Runbook: Perform a Major Version Upgrade With pg_upgrade

1 · Prerequisites

Confirm every item is in place before any state change.

• Both major versions installed on the same host, with their binary directories known
• A verified backup taken immediately before the window, and a tested restore procedure
• A rehearsal of this exact upgrade on a copy of production data, with its duration measured
• A change window sized from the rehearsal rather than from an estimate, plus margin for the verification
• Confirmation that every extension in use exists for the target version, with a compatible version available
• Agreement on the upgrade mode, since copy, link and swap have very different rollback properties

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · **Run pg_upgrade --check days before the window, and keep running it until it passes.** It is read-only and it finds every blocking condition. A check first run inside the window is a window spent reading error messages.
• · Compare the checksum settings of both clusters. pg_controldata on each, Data page checksum version. PostgreSQL 17 and earlier default to 0 (off); PostgreSQL 18 defaults to 1 (on). A mismatch fails the check with old cluster does not use data checksums but the new one does.
• · Enumerate every extension and its version. SELECT extname, extversion FROM pg_extension; in every database. An extension without a build for the target version blocks the upgrade, and finding out during the window is expensive.
• · Check for logical replication slots and subscriptions. pg_upgrade has specific checks for both, and a slot in an invalid state blocks it.
• · Confirm free space for the mode you have chosen. Copy mode needs room for a second full copy of the cluster. Link and swap do not, and pay for that with a much harder rollback.
• · Confirm both clusters are stopped before the upgrade proper. pg_upgrade will refuse otherwise, and that refusal is protecting you.
• · Confirm the rehearsal duration. The measured upgrade in one estate took 1587 ms on a small database; a large one takes far longer in copy mode and roughly the same in link mode. The rehearsal is the only source for this number.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Resolve the checksum mismatch before anything else. The better option is enabling checksums on the source: stop the old cluster and run pg_checksums --enable -D <old-datadir>. Measured at 159 ms on a 43 MB database; it scales with cluster size. The worse option is initdb-ing the new cluster with --no-data-checksums, which gives up a real protection permanently.
2. 2**Re-run pg_upgrade --check until it reports no failures.** Every line should read ok.
3. 3Take the backup, and verify it. This is the last moment at which the old cluster is intact and the only rollback that works for every mode.
4. 4Stop both clusters cleanly. pg_ctl -D <datadir> -m fast stop for each.
5. 5Choose the mode deliberately. Default (copy) duplicates every file — slowest, and the old cluster remains fully usable. --link hard-links them — fast, and the old cluster becomes unusable once the new one starts. --swap moves the directories — fastest, and equally destructive to the old cluster.
6. 6Run the upgrade. pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d /var/lib/postgresql/17/main -D /var/lib/postgresql/18/main. Add the mode flag if you chose one.
7. 7Read the completion output rather than the exit status. It ends with Upgrade Complete and then tells you exactly what to do next, including which statistics commands to run.
8. 8Read what it says about statistics carefully, because the wording changed. PostgreSQL 17's pg_upgrade said Optimizer statistics are not transferred. PostgreSQL 18's says Some statistics are not transferred. Those are different claims and the runbook you inherited may carry the older one.
9. 9Start the new cluster and confirm the version. SELECT version();
10. 10Check what statistics actually arrived before running anything. SELECT count(*) FROM pg_stats WHERE tablename = '<table>'; and SELECT reltuples, relpages FROM pg_class WHERE relname = '<table>'; On 18, full column statistics — n_distinct, MCV lists and 101-bucket histograms — transfer.
11. 11**Run the recommended vacuumdb regardless, and compare.** vacuumdb --all --analyze-in-stages --missing-stats-only then vacuumdb --all --analyze-only. Capture a plan before and after; if the estimates are identical, the statistics had already transferred, and knowing that changes how long the post-upgrade window needs to be next time.
12. 12Verify the data. Row counts on the significant tables, object counts by relkind, and the extensions present with their versions.
13. 13Reconnect the application and watch it, then remove the old cluster only after the new one has been serving for an agreed period.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓SELECT version(); reports the target major version.
• ✓Row counts on the significant tables match the pre-upgrade values, recorded before the window.
• ✓SELECT extname, extversion FROM pg_extension; in every database shows every extension present, at a version compatible with the new server.
• ✓Plans for the significant queries are equivalent to the pre-upgrade plans. Capture EXPLAIN output for a handful of queries before the window and compare.
• ✓SELECT count(*) FROM pg_stats WHERE tablename = :table; is non-zero before any ANALYZE, if you are on a version that transfers statistics — and the vacuumdb --missing-stats-only run reports nothing to do.
• ✓Application error rates and latency are normal, from the application's own monitoring.
• ✓A physical backup of the new cluster has been taken, because the old backups are for a different major version and cannot restore into this one.
• ✓The measured duration of every phase is recorded for the next upgrade.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶In copy mode, rollback is to stop the new cluster and start the old one. The old data directory is untouched and fully usable, provided nothing has written to the new cluster that must be preserved.
• ↶In link or swap mode there is no rollback to the old cluster. --link shares files between the two, and starting the new cluster modifies them; --swap has moved the directories. The rollback is a restore from the backup taken before the window.
• ↶That difference is the whole reason to choose the mode deliberately. Link and swap buy speed with rollback.
• ↶If the application wrote to the new cluster before a problem was found, those writes exist only there. Decide explicitly whether to extract them before rolling back, and extract them with pg_dump of the affected tables.
• ↶If pg_upgrade fails partway, read its log directory — it names the failure precisely. Do not start either cluster until you understand what happened; in copy mode the old cluster is still safe and in link mode it may not be.
• ↶If checksums were enabled on the source with pg_checksums --enable, that change persists and does not need reverting. It is an improvement and the old cluster remains usable with it.
• ↶Keep the old cluster or its backup until the new one has served for an agreed period, and remove it deliberately with a note rather than reclaiming the space by reflex.

6 · Escalation

When the runbook isn't enough, contact:

• · pg_upgrade --check reports an extension without a target-version build: escalate to whoever owns that extension. The upgrade cannot proceed and the answer is a package, not a flag.
• · The checksum mismatch cannot be resolved by enabling checksums on the source within the window: escalate rather than initdb-ing the new cluster without checksums. Giving up checksums is a permanent reduction in protection and it should be a recorded decision, not a workaround.
• · pg_upgrade fails with an error not covered by --check: escalate and preserve the log directory. Starting either cluster before understanding the failure can make a recoverable situation worse.
• · The upgrade duration exceeds the window and the mode is copy: escalate to the change owner with the remaining work. Aborting cleanly in copy mode is possible; aborting halfway through link mode is not.
• · Plans have changed materially after the upgrade and performance has regressed: escalate to the application owner with the before and after plans. A major version can change planner behaviour, and that is a tuning exercise rather than an upgrade failure.
• · Logical replication slots or subscriptions exist and their handling is unclear: escalate before proceeding. Slots do not survive an upgrade in the way people expect, and a downstream consumer may need to be resynchronised.

References

1. PostgreSQL 18 documentation, pg_upgrade
2. PostgreSQL 18 documentation, Upgrading a PostgreSQL Cluster
3. PostgreSQL 18 documentation, pg_checksums
4. PostgreSQL 18 documentation, vacuumdb