Major version upgrade with pg_upgrade --link (end-to-end runbook)

Command

# As the postgres user, in a writable directory; example 16 -> 17
export OLD_BIN=/usr/pgsql-16/bin NEW_BIN=/usr/pgsql-17/bin
export OLD_DATA=/var/lib/pgsql/16/data NEW_DATA=/var/lib/pgsql/17/data
# 0) initdb the new cluster with the same encoding/locale and checksum setting as the old one (-k if the old cluster has checksums)
# 1) Compatibility check (can run while the old cluster is up, changes no data):
$NEW_BIN/pg_upgrade --old-bindir=$OLD_BIN --new-bindir=$NEW_BIN --old-datadir=$OLD_DATA --new-datadir=$NEW_DATA --check
# 2) Stop the old cluster and upgrade with hard links:
$OLD_BIN/pg_ctl -D $OLD_DATA stop -m fast
$NEW_BIN/pg_upgrade --old-bindir=$OLD_BIN --new-bindir=$NEW_BIN --old-datadir=$OLD_DATA --new-datadir=$NEW_DATA --link --jobs=4
# 3) Carry postgresql.conf/pg_hba.conf customizations over to the new cluster, start it:
$NEW_BIN/pg_ctl -D $NEW_DATA -l /tmp/pg17_start.log start
# 4) Gather statistics, run generated scripts if any:
$NEW_BIN/vacuumdb --all --analyze-in-stages
# psql -X -f update_extensions.sql   (if pg_upgrade generated it)
# 5) After validation and a fresh backup, delete the old cluster:
./delete_old_cluster.sh

What it does

First validates compatibility with --check, then performs the major upgrade in minutes regardless of data size by hard-linking the old cluster's data files to the new version instead of copying them; afterwards gathers statistics in stages and removes the old cluster.

When to use it

To move a large cluster in place to a new major version with an acceptably short outage (minutes).

Caution

With --link, once the new cluster has been started the old cluster can no longer be used safely; the only way back is a backup taken before the upgrade. The old and new data directories must be on the same file system. Plans may be poor on the new cluster until analyze finishes. If you use pgBackRest, run stanza-upgrade and take a new full backup immediately; backups of the old version cannot be restored into the new cluster.