How to perform a major upgrade of PostgreSQL in a Patroni cluster?

Major PostgreSQL upgrades in a Patroni cluster require careful handling, as directly upgrading the version without stopping the cluster is not possible. The process typically involves the following steps:

1. Preparation:

   • Make a full backup of the data.
   • Ensure you have access to the new binary files of the required PostgreSQL version.

2. Updating replicas:

   • Stop each replica one by one.
   • Perform the PostgreSQL upgrade on each replica (install the new version).
   • Start the replica with the new version, using pg_upgrade or restoring from backup if necessary.
   • Reconnect the replica to the Patroni cluster.

3. Updating the master:

   • Failover to one of the updated replicas to make it the new master.
   • Stop the old master.
   • Upgrade PostgreSQL on the old master.
   • Start it as a replica of the new version.

4. Verification:

   • Ensure all nodes are functioning correctly and are synchronized.

Important: Patroni does not support automatic major upgrades, so the process requires manual intervention and thorough testing.

Example command for upgrading using pg_upgrade on one node:

pg_ctl stop -D /var/lib/postgresql/data
pg_upgrade -b /usr/lib/postgresql/old_version/bin -B /usr/lib/postgresql/new_version/bin -d /var/lib/postgresql/data_old -D /var/lib/postgresql/data_new
pg_ctl start -D /var/lib/postgresql/data_new

After upgrading the node, update the Patroni configuration if paths or versions have changed.