How to upgrade PostgreSQL streams

Resolution

The solution is inspired from the official RHEL8 documentation 7.4.7.3. Fast upgrade using the pg_upgrade utility.

In the document, we assume we are on a RHEL8 system currently having a database based on PostgreSQL 13 stream, as per below output:

# dnf module list postgresql
[...]
Name                   Stream             Profiles                      Summary                                       
postgresql             9.6                client, server [d]            PostgreSQL server and client module           
postgresql             10 [d]             client, server [d]            PostgreSQL server and client module           
postgresql             12                 client, server [d]            PostgreSQL server and client module           
postgresql             13 [e]             client, server [d]            PostgreSQL server and client module           
postgresql             15                 client, server [d]            PostgreSQL server and client module           
postgresql             16                 client, server [d]            PostgreSQL server and client module           

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

We also assume that the database is hosted in the standard directory /var/lib/pgsql/data.

The goal is to upgrade to PostgreSQL 16 stream.

With RHEL and PostgreSQL in general, it's not possible to upgrade from distant major releases, it's only possible to upgrade to the next release.

IMPORTANT: Do not skip releases! If, for instance, you have PostgreSQL 12 installed, move to 13 and then run postgresql-upgrade. Once you've confirmed that the upgrade was successful, only then can you safely move to the next major version and run postgresql-upgrade again. Repeat this sequence until you're at your desired version. Note that you can only run postgresl-upgrade from an offered version one step newer than what you have installed or it will refuse to proceed. You can go from 9.6 to 10, from 10 to 12, from 12 to 13, from 13 to 15, and from 15 to 16 only.

WARNING: even though postgresql-upgrade is robust and won't corrupt the database if the upgrade fails, it's recommended to backup the database after stopping the service anyway.

1. Install postgresql-upgrade package

   # dnf -y install postgresql-upgrade
   [...]
   

2. Stop the database

   # systemctl stop postgresql.service
   

3. If you have pgaudit configured, disable it

   # grep -w pgaudit /var/lib/pgsql/data/postgresql.conf
   shared_preload_libraries = 'pgaudit,pg_stat_statements,passwordcheck'
   

   Here above the database is configured with pgaudit, you need to remove it from the line, or comment the entire line out.

4. If you have ssl_*_file properties referring to absolute paths, fix those referring to the database directory

   For example, if you have a ssl_cert_file property configured and pointing to /var/lib/pgsql/data/<some.cert>, then remove /var/lib/pgsql/data/ prefix or else the upgrade will fail, because the upgrade will temporarily rename /var/lib/pgsql/data into /var/lib/pgsql/data-old, causing the database upgrade runtime to not find the certificate anymore and upgrade to fail.
   This has to be done for all ssl_*_file properties referring to absolute paths starting with /var/lib/pgsql/data (normally there should be none).

5. Reset the DNF module, enable Stream 15 and update the packages

   # dnf -y module reset postgresql
   [...]
   # dnf -y module enable postgresql:15
   [...]
   # dnf -y update postgresql
   [...]
   

   At this point, you should have Stream 15 packages installed, but the database still at Stream 13 level.

6. Upgrade the database

   # postgresql-setup --upgrade
   [...]
   

   At this point, you should have the older database at Stream 13 level in data-old directory and newer database at Stream 15 level in data directory.

   In case the command failed:

   1. Restore the old database and pg_control file

      # mv /var/lib/pgsql/data-old /var/lib/pgsql/data
      # mv /var/lib/pgsql/data/global/pg_control.old /var/lib/pgsql/data/global/pg_control
      

   2. Re-do the upgrade under strace

      # yum -y install strace
      # strace -fttTvyy -s 8192 -o /tmp/pgupgrade.strace -- postgresql-setup --upgrade
      

   3. Open a ticket on the Customer Portal, reference this solution and upload the strace file /tmp/pgupgrade.strace

   4. Ask support for further instructions

7. Make sure to include the configuration changes in the upgraded database

   Compare the configuration files from data-old directory with the ones from data directory and port the customization you made compared to defaults.
   Typically differences will be found in postgresql.conf and pg_hba.conf files.
   The files may largely differ, make sure to only port the customization that were made.
   In particular, if you had pgaudit prior to the upgrade (step 3), restore the setting in postgresql.conf.

8. Finish the upgrade

   $ exit
   # systemctl start postgresql
   
   # sudo -u postgres -i
   $ /usr/bin/vacuumdb -U postgres --all --analyze-in-stages
   $ ./delete_old_cluster.sh
   

   The commands are coming from upgrade_postgresql.log log file which got created during the upgrade.
   Once the last command executed, data-old directory will be removed.

9. Upgrade to Stream 16

   The procedure is identical to the one starting at step 2. Stop the database, but replacing Stream 13 with Stream 15:

   # systemctl stop postgresql.service
   
   # dnf -y module reset postgresql
   # dnf -y module enable postgresql:16
   # dnf -y update postgresql
   
   # postgresql-setup --upgrade
   
    ... Port the customization made to the configuration files from data-old/ to data/ ...
   
   $ exit
   # systemctl start postgresql
   
   # sudo -u postgres -i
   $ /usr/bin/vacuumdb -U postgres --all --analyze-in-stages
   $ ./delete_old_cluster.sh