Contents

• Upgrade steps
  • Get ready
  • Start maintenance
  • Do a full backup of all the databases
  • Upgrade PostgreSQL server
  • Post-upgrade steps
  • Get your Flow Production Tracking site out of maintenance using SEC
  • Test your site
  • Install new version of the PostgreSQL tools on all the Flow Production Tracking app servers
• Troubleshooting
  • pg_upgrade fails to connect to the old database
  • Rollback

First, contact support to get the latest recommended PostgreSQL major version for Flow Production Tracking.

Upgrading PostgreSQL is a major operation that requires careful planning. This operation is disruptive and will result in significant downtime at your Studio.

Upgrade steps

Get ready

1. Read the PostgreSQL release notes.
2. Make sure that you have a proper rollback plan in place in case of failure.
3. Upgrade your staging cluster first.
4. Plan for a few weeks of testing between the staging and production upgrade.
5. Still not feeling comfortable? Test your rollback procedure and do it again!

Start maintenance

1. Put your Flow Production Tracking site in maintenance using SEC.
2. Put all your other Application and API servers, if any, in maintenance using SEC.
3. Make sure any scripts accessing directly the database, if any, are stopped.
4. Validate that there is no connections to the database.

Do a full backup of all the databases

Depending on the method you use to upgrade PostgreSQL, it may be better to proceed with a backup of the the database that you will be able to rollback to in case of failure.

Upgrade PostgreSQL server

Upgrading PostgreSQL is not an operation that is Flow Production Tracking specific. A lot of great resources are available on the web for how to proceed. Here are some useful references:

• https://www.postgresql.org/docs/15/pgupgrade.html
• https://www.postgresql.org/docs/15/upgrading.html

There are many ways to upgrade a PostgreSQL cluster. The best strategy depends on your specific situation. Flow Production Tracking recommends:

1. Upgrading using pg_upgrade without the --link argument if the downtime is acceptable. This is the safest and easiest way as it allows to rollback without a prior backup.
   1. This technique is going to double the disk usage. Make sure you have enough free space before proceeding.
2. Upgrading using pg_upgrade --link if your database is huge and downtime using the without --link approach takes too much time.
   1. If you use this method, make sure to take a backup of your database first (see https://www.postgresql.org/docs/15/pgupgrade.html for details).

Post-upgrade steps

As underlined in the PostgreSQL documentation, some steps may be required after the upgrade. If this is the case, it will be clearly indicated by warnings issued in the upgrade process.

1. Make sure to follow these post-upgrade steps properly.
2. Update the optimizer statistics, as instructed by PostgreSQL after the upgrade.

Get your Flow Production Tracking site out of maintenance using SEC

Remove maintenance on all the Application/API servers.

Test your site

Make a generic regression pass, testing your important pipeline steps, scripts, Web App, etc. Be on the lookout for performance regressions.

Right after the upgrade, Flow Production Tracking may feel a bit slower because everything may not be cached yet by PostgreSQL. This is normal, but should dissipate with usage.

Install new version of the PostgreSQL tools on all the Flow Production Tracking app servers

If you are running a dbops container, change the Docker Compose file for this container to match the version of PostgreSQL you just installed.

 dbops:  
  image: postgres:15

Troubleshooting

pg_upgrade fails to connect to the old database

sudo vi /var/lib/postgresql/data/pg_hba.conf

# Edit with the following:
# For postgres versions >=9.1
local all all  peer
# For old postgres versions  
local all all ident 

Rollback

Roll back instructions are detailed in the Postgres Wiki.