Home / Databases / PostgreSQL

DatabasesRunbookAdvanced

PostgreSQL major version upgrade with pg_upgrade: a safe runbook

Prepare, check, run and validate a PostgreSQL major version upgrade with pg_upgrade, including extensions, replicas, statistics and a rollback plan.

Deepak Bhardwaj2 min read

Applies toPostgreSQL 12 → 17 on Linux (self-managed); managed services have their own upgrade path

Before the change window

  1. Read the release notes for every major version you skip. Look for removed features and changed defaults.
  2. List extensions and confirm each one exists for the new version:
SELECT extname, extversion FROM pg_extension;
  1. Install the new version's binaries next to the old ones (for example postgresql17-server and postgresql17-contrib), plus the same extension packages built for 17.
  2. Take a full backup you have tested restoring.
  3. Run the upgrade on a copy first and time it.

1. Initialise the new cluster

Use the same locale, encoding and data checksum setting as the old one:

sudo -u postgres /usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/data \
  --encoding=UTF8 --locale=en_US.UTF-8 --data-checksums

(Check the old cluster with pg_controldata /var/lib/pgsql/14/data | grep checksum.)

2. Check compatibility (no downtime yet)

sudo -u postgres /usr/pgsql-17/bin/pg_upgrade \
  -b /usr/pgsql-14/bin -B /usr/pgsql-17/bin \
  -d /var/lib/pgsql/14/data -D /var/lib/pgsql/17/data \
  --check

Fix everything it reports (incompatible data types, missing extension libraries, reg* columns, prepared transactions).

3. Upgrade

sudo systemctl stop postgresql-14
sudo -u postgres /usr/pgsql-17/bin/pg_upgrade \
  -b /usr/pgsql-14/bin -B /usr/pgsql-17/bin \
  -d /var/lib/pgsql/14/data -D /var/lib/pgsql/17/data \
  --link --jobs 4
Mode Speed Rollback
default (copy) Slow, needs double disk space Old cluster untouched; just start it again
--link Minutes, even for TBs Once the new cluster has started, the old one can't be used
--clone (Linux, supported filesystems) Fast Like copy

Copy postgresql.conf and pg_hba.conf settings across (review them; don't blindly copy). Then:

sudo systemctl start postgresql-17

4. After the upgrade

# statistics are NOT carried over before PG 18: queries are slow until you run this
sudo -u postgres /usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages

ALTER EXTENSION pg_stat_statements UPDATE;   -- for each extension

pg_upgrade also writes scripts (for example update_extensions.sql) to the current directory when needed. Run them.

5. Replicas

Streaming standbys can't follow across a major version. Either rebuild them with pg_basebackup after the upgrade, or use the documented rsync procedure with --link (advanced).

Rollback plan

  • Copy mode: stop 17, start 14.
  • Link mode: restore from backup, or fail over to a standby that you kept on the old version until validation finished.

Tip

For near-zero downtime, use logical replication from the old version to a new-version cluster instead, then switch the application over. It takes more setup but the cutover is seconds.

Related fixes