database: upgrade from PostgreSQL 9.6

[GraemeWatt]

Email today from CERN DB On Demand team:

Official PostgreSQL 9.6 EOL release (9.6.24) will be published by mid Nov 2021.

DBOD will cease PostgreSQL 9.6 support by the 31st January 2022.

A forced upgrade to the latest PostgreSQL 12.x of any PostgreSQL 9.6 databases remaining thereafter, will be executed - regardless of the instances status - within the first two weeks of February 2022 with a scheduled intervention announced as usual via OTG/SSB.

Further reminders will be sent when the deadline is approaching but we strongly suggest that you take the initiative to upgrade your databases running PostgreSQL major version 9.6, at your earliest convenience, to prevent any issue.

This communciation is intended to provide an advance notice to plan for the upgrades of your database cluster(s) in a timely manner. Some might find an opportunity to work on this between now and the end of the year 2021.

You can request an upgrade of your database instance to a later major version of PostgreSQL at any time with a SNOW ticket.

The upgrade procedure will shut down the instance, perform the upgrade, and restart the instance. Multiple restarts might be required. The duration of the upgrade should depend mainly on the number of objects to process.

We recommend that you request a clone and test the upgrade to check for any blocking issues, any work required due to extensions, and to have an idea of the downtime required.

We also strongly suggest to extensively test on the upgraded clone the impact on the functionalities and performance of your application(s).

To learn more about upgrading PostgreSQL 9.6 and the DBOD versioning policy and milestones, please review the dedicated page in the DBOD user guide.

Please do not hesitate to come in touch at any time by opening a SNOW ticket, should you have any question, concern or exceptional needs to discuss.

• [x] We should first test locally whether there are any issues with running pg_upgrade to upgrade a database from v9.6 to the latest v12.x.
• [x] Then the current prod database and CephFS data directory contents should be copied to qa and a request made to upgrade the hepdata_qa database from v9.6 to v12.x.
• [x] If this is successful, the same procedure can be repeated for the hepdata (prod slave) and hepdata02 (prod master) instances. We might be able to upgrade to v13.x instead of v12.x depending on when v13.x is made available by the CERN DBOD team.

FYI: @benjamin-bergia

[alisonrclarke]

Upgrading my local db from v9.6 to v12 worked OK (once I'd updated the Collate and Ctype settings of my v12 cluster to match the existing one).

[benjamin-bergia]

Just for info: on the inspire side I am working on moving the database inside the cluster. I tested pg_upgrade from 9.6 to 14 on inspire prod dataset and it worked

[GraemeWatt]

Update from CERN DB On Demand team (12th January 2022):

Official PostgreSQL 9.6 EOL release (9.6.24) has been published mid Nov 2021.

DBOD will cease PostgreSQL 9.6 support by the 31st January 2022.

A forced upgrade to the latest PostgreSQL 12.x of any PostgreSQL 9.6 databases remaining thereafter, will be executed - regardless of the instances status - within the first two weeks of February 2022 with a scheduled intervention announced as usual via OTG/SSB.

An upgrade checker job has been running periodically across all PostgreSQL 9.6 instances enabling the triggering of the upgrade process from the DBOD web interface as described in the DBOD user guide [1].

The upgrade procedure will shut down the instance, perform the upgrade, and restart the instance. Multiple restarts might be required. The duration of the upgrade should depend mainly on the number of objects to process.

Before triggering the upgrade, we recommend that you request a clone and test the upgrade to check for any blocking issues, any work required due to extensions, and to have an idea of the downtime required.

We also strongly suggest to extensively test on the upgraded clone the impact on the functionalities and performance of your application(s).

Please consult the attached file to identify your instance among all those affected.

To learn more about upgrading PostgreSQL 9.6 and the DBOD versioning policy and milestones, please review the dedicated page in the DBOD user guide [1].

Please do not hesitate to come in touch at any time by opening a SNOW ticket [2], should you have any question, concern or exceptional needs to discuss.

[1] https://dbod-user-guide.web.cern.ch/instance_management/upgrading/postgres_9_6/ [2] https://cern.service-now.com/service-portal/?id=sc_cat_item&name=request&fe=db-on-demand

Upgrading the DB on my laptop worked fine after updating the datcollate and datctype fields mentioned by Alison above, otherwise pg_upgrade gives an error message. In case it's useful in future, here were the main steps:

brew install postgresql@12
brew services stop postgresql@9.6
brew services start postgresql@12
postgres=# update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8';
brew services stop postgresql@12
pg_upgrade -d /usr/local/var/postgresql@9.6 -D /usr/local/var/postgresql@12 -b /usr/local/opt/postgresql@9.6/bin -B /usr/local/opt/postgresql@12/bin
brew services start postgresql@12

I then restored the latest prod DB backup to the hepdata_qa DBOD instance and clicked "upgrade to version 12.5" on the DBOD web interface. The upgrade was successful and took about 7 minutes. Next week I can similarly upgrade the prod DB via the DBOD web interface. The static backup site should be deployed for the duration of the DB upgrade, and the downtime should be announced on Twitter.

[GraemeWatt]

We successfully upgraded the production database from PostgreSQL version 9.6.20 to 12.5.