Upgrade PostgreSQL a Major Version with a Rehearsed Cutover

Move a production PostgreSQL cluster to the next major version with a rehearsed pg_upgrade run, a verified backup and a cutover that can fall back to the old cluster.

advanced~45 mindatabaseoperationsreliability

Objective

Upgrade a production PostgreSQL cluster across a major version with measured downtime, validated extensions and a tested path back to the previous release.

Environment

Source PostgreSQL 16 cluster and target PostgreSQL 17 binaries installed on the same host
A staging host with a restored copy of production and the target binaries
A verified base backup plus WAL archive for the rollback path
Application owners available for a defined write-freeze window
Extension inventory checked against the packages available for the target version

Steps

1. Read release notes and inventory extensions

List installed extensions with pg_extension, confirm each has a build for the target major version, and note removed or renamed configuration parameters.

Expected state: Every extension has a compatible package and no application feature depends on a removed setting.

2. Rehearse on a restored clone

Restore the latest backup to the staging host, run pg_upgrade --check, then run the full upgrade with --copy and record durations and errors.

Expected state: The rehearsal completes, the new cluster starts, and row counts match the source for every critical table.

3. Prepare the maintenance window

Take a fresh base backup, verify it restores on the staging host, confirm free disk space and announce the write freeze to application owners.

Expected state: A restorable backup exists and every consumer has acknowledged the freeze window.

4. Run the production upgrade

Stop application writes, stop the old cluster, run pg_upgrade with --copy, start the new cluster and run vacuumdb --all --analyze-in-stages.

Expected state: The new cluster accepts connections, replication slots are recreated and no upgrade errors remain in the log.

5. Validate and switch traffic

Run smoke queries, compare critical row counts and query plans, then switch the connection string and resume writes with the new port.

Expected state: Application health checks pass against the new cluster and error rates stay flat for one hour.

6. Retain the old cluster for rollback

Keep the old data directory and old binaries untouched for fourteen days, then remove them only after a written sign-off.

Expected state: The old cluster remains startable throughout the retention window while the new cluster serves production.

Decision points

Should pg_upgrade use --copy or --link?

--copy to preserve the old cluster for rollback
--link for the fastest cutover

Use --copy whenever rollback matters; --link hard-links files and makes the old cluster unusable, which is an irreversible step.

How should downtime be minimized?

Stop writes and run pg_upgrade in one window
Replicate to a new cluster with logical replication

Use pg_upgrade --copy with a short write freeze for most workloads; reserve logical replication for clusters where the freeze must be under a minute.

Expected outcome

Production runs on the new PostgreSQL major version with all extensions working, validated data and a retained old cluster for rollback.

Verification

Check pg_lsclusters shows the new version online, compare row counts and key query plans, run the application smoke suite, and confirm pg_upgrade reported no failures.

Cleanup

Remove the staging clone, archive the upgrade logs and timings, and delete the old cluster only after the fourteen-day retention and sign-off.

Choose the upgrade path

pg_upgrade --copy copies every relation into the new data directory, so the old cluster stays intact and can be started again if validation fails. --link hard-links the files instead, which is much faster but leaves the old cluster unusable: treat that flag as irreversible and use it only when a restore from backup is an acceptable rollback. For very tight maintenance windows, logical replication into a new cluster is an alternative, but it adds schema and sequence synchronization work that a rehearsed pg_upgrade avoids.

/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --copy --check

Remove --check for the real run, then start the target cluster with pg_ctlcluster 17 main start and refresh statistics with vacuumdb --all --analyze-in-stages.

Safety checks before the window

Rehearse on a restored clone, not on a logical dump, so ownership, extensions and large objects are exercised. Confirm free space for a full copy of the database plus WAL, keep the old binaries installed, and check that connection pools, backups and monitoring point to the new port only after validation. Freeze DDL as well as writes during the upgrade; a concurrent ALTER TABLE can invalidate comparison queries.

Validate and switch

Compare critical row counts, run EXPLAIN (ANALYZE, BUFFERS) on the five most important queries, and run the application smoke suite before resuming writes. Keep the write freeze until the new cluster passes checks, because anything written after cutover exists only in the new cluster.

Rollback

With --copy, stop the new cluster, start the old one, and restore the previous connection string; the old data is complete but writes accepted after cutover are lost, so freeze writes until validation passes. With --link, there is no direct rollback: restore the verified base backup and replay WAL, which makes the hard-link choice the irreversible step in this workflow.