PostgreSQL major upgrade (pg_upgrade / logical replication)

Major version upgrade (e.g. 16 → 17): choice of method (pg_upgrade --link / --copy / --clone, or a near-zero-downtime migration with logical replication), pg_upgrade --check, extension compatibility, carrying over the configuration, regathering statistics, rebuilding standbys with rsync or pg_basebackup, removing the old cluster and a rollback runbook. Single server, streaming replication (primary + standbys) and Patroni cluster (Patroni's official major upgrade procedure: Patroni is stopped, pg_upgrade runs on the leader, the DCS record is removed, the replicas are re-cloned by Patroni). RHEL family and Debian/Ubuntu (including pg_upgradecluster).

For an update within the same major version (e.g. 17.4 → 17.6) use pg-minor-update instead of this guide. For continuous table-level replication or a partial migration use pg-logical-repl; to move a small database to another server with a dump use pg-dump-restore.

Topology: In place on the same server (pg_upgrade; single server, primary + standbys or Patroni cluster) or to a new server (logical)

Scenarios

• Major upgrade — Major version upgrade (e.g. 16 → 17): pre-checks, backup, pg_upgrade or logical replication, verification and cleanup.
• Rollback (upgrade failed / abandoned) — The upgrade stopped halfway or the new version has problems: return to the old version. With --link, once the new cluster has been started, the only way back is the backup.

Before you start

• The source cluster is PostgreSQL …, installed from the PGDG repository (pg-install). The target version … is higher than the source.
• The application and drivers have been tested with PostgreSQL …; the same upgrade has been rehearsed in a test environment. The "Migration to Version" sections of the release notes of every major version up to … have been read (removed settings, behavior changes).
• A … build is available for every installed extension (PostGIS, pg_repack, pgaudit, TimescaleDB…). If an extension is not available for …, the upgrade cannot be done.
• …There is free space equal to the current data size for the new data directory.…The old and new data directories are on the same file system (required for hard links); a few GB of free space for WAL and the new catalog.…The file system supports reflink (XFS reflink=1 or Btrfs) and both directories are on the same file system.…
• Logical replication slots / subscriptions, if any, have been listed: …with a PostgreSQL 17+ source, pg_upgrade migrates logical slots, but all changes must have been consumed…with a PostgreSQL … source, pg_upgrade does not migrate logical slots; they have to be recreated after the upgrade….
• Passwordless SSH as the postgres user from the primary to every standby, and rsync installed on the standbys. The data directory layout is …/<version>/data (rsync uses the common parent directory).
• The standbys are streaming; the primary's pg_hba.conf allows the replication connection and the … password is in ~postgres/.pgpass on the standbys.
• The Patroni cluster is healthy: … is the current leader, all replicas are streaming with no lag, no Pending restart. Cluster name …, configuration …, REST API passwords in /etc/patroni/patroni.env (pg-patroni layout). A snapshot backup of the DCS (etcd) has been taken. The cluster will be completely down during the upgrade: connections through HAProxy/VIP are refused.
• The Patroni version supports PostgreSQL … (new major version support in the Patroni release notes). An older Patroni may not recognize the new version's parameters and control file; if necessary, upgrade Patroni first in a rolling fashion.
• PostgreSQL … is installed on the target server with pg-install, empty and running; the … builds of the source's extensions are installed. The target can reach port … on the source.
• The tables to migrate have a primary key or replica identity (otherwise UPDATE/DELETE fail on the source). Add a PK to the listed tables or apply ALTER TABLE … REPLICA IDENTITY FULL.
• There is a current backup with a tested restore… (pgBackRest)…; if possible a server/disk snapshot will also be taken.… With the --link method this is the only way back once the new cluster has been started.…

Phases of the guide

• Pre-checks — Check the Patroni cluster status · Record the cluster properties · List the extension RPMs/debs · Check prepared transactions and long-running transactions
• Preparing the new cluster — Create the new data directory · Create the new cluster with the same properties (initdb) · Carry the configuration over to the new cluster · Check for settings that are invalid in the new version · Check the Patroni parameters against the new version
• Backup and fallback point — Take a backup (pgBackRest full) · Take a backup (pg_dumpall + snapshot)
• Compatibility check (pg_upgrade --check) — pg_upgrade --check
• Downtime: stop the old cluster — Stop the applications · Final backup at the start of the downtime (pgBackRest incr) · Final logical backup at the start of the downtime · Stop the old primary · Stop Patroni on the replicas · Stop Patroni on the leader …
• Upgrade — Run pg_upgrade · Upgrade with pg_upgradecluster · Move the conf.d files to the new cluster · Switch the pgBackRest configuration to the new cluster · Switch bin_dir and data_dir in patroni.yml to the new version · Upgrade the pgBackRest stanza to the new version …
• Starting the new cluster — Start the new cluster · Start Patroni on the leader · Start the standbys immediately
• Post-upgrade tasks — Update the extensions · Gather statistics · pgBackRest check and a new full backup
• Verification — Version, settings and databases · Verify the Patroni cluster · Check the logs · Application tests
• Removing the old cluster — Delete the old data directory · Delete the old data directory on the …replicas…standbys… · Remove the old version's software
• Preparing the source server — wal_level = logical · Restart the source · Create the replication user · Add the target to pg_hba.conf · Reload pg_hba · Create the publication
• Schema and subscription on the target — Write the source passwords to .pgpass · Copy the roles (globals) · Copy the schema · Create the subscription · Wait for the initial copy to finish · Check the lag
• Final cutover — Stop writes on the source · Verify that the last changes have been transferred · Transfer the sequence values · Drop the subscription · Gather statistics · Redirect the applications to the target …
• Rollback — Stop the new cluster · Stop Patroni on the replicas · Stop Patroni on the leader · Restore pg_control (--link, new cluster not started) · …Roll back from backup (--link, new cluster started)…Roll back from the logical backup (--link, new cluster started)… · Complete recovery without Patroni …