PostgreSQL Pre-Upgrade Checklist

How to use this checklist

Days before the window, not on the morning of it. Most of these items find problems that take longer to solve than the window allows.

Run the check until it passes

The mismatch you will hit going to 18

Choose the mode by its rollback

Link and swap buy speed with rollback. On a large cluster with a short window one of them may be the only option — and if it is, the backup taken immediately before the window is your only way back, which is why this checklist asks you to have verified it.

Capture what the verification will compare against

-- row counts on the significant tables
SELECT count(*) FROM orders;

-- object counts
SELECT relkind, count(*) FROM pg_class
WHERE relkind IN ('r','i','S','v','m') GROUP BY 1;

-- extensions and versions, in EVERY database
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- plans for a handful of significant queries
EXPLAIN (COSTS ON) SELECT ...;

None of these can be reconstructed after the upgrade. Capturing them takes ten minutes and it is the difference between “the upgrade worked” and “the upgrade worked, and here is why we know”.

The statistics claim changed between versions

For a minor upgrade: standbys first

A standby running a newer minor version than its primary is supported. The reverse is not guaranteed.

Upgrade each standby one at a time, confirming it reconnects and catches up before starting the next — and remove any synchronous standby from synchronous_standby_names before restarting it, or every commit on the primary hangs for the duration.

The backup you will need afterwards

Schedule a new physical backup immediately after the upgrade. The existing ones are for the previous major version and cannot restore into the new cluster.

Until a new base backup exists, the estate’s recovery path is a restore of the old version followed by another upgrade — which is a recovery time objective nobody agreed to.

Where the numbers come from

The blocking conditions come from pg_upgrade --check itself, run days in advance and repeated until every line reads ok. The checksum settings come from pg_controldata on both clusters, not from an assumption about defaults — 17 and earlier default to off, 18 defaults to on.

The window size comes from a timed rehearsal on production-scale data, recorded per phase. Extensions come from pg_extension in every database, enumerated rather than sampled.

Access this needs

Read access to the release notes for every version between the current one and the target, to the extension inventory in every database, and to the rehearsal environment where --check has been run.

A role holding pg_monitor and pg_read_all_settings on the source cluster, and the operating-system account that will run the upgrade — because pg_upgrade --check must be run as that user, on that host, before the window.

Read access to the most recent backup’s record and to the date it was last proven by restore.

What the review produces

A dated record naming the reviewer, both versions, the chosen transfer mode with the rollback it implies, the rehearsal timings per phase, and the disposition of every item — completed before the window opens.

Attach the clean --check output and the captured pre-upgrade state: row counts, object counts by relkind, extension versions per database, and plans for the significant queries. None of it can be reconstructed afterwards.

The abort criteria and the name of whoever decides go into the record before the window, not into a conversation during it.

Sign-off

• Reviewer: ________________ Date: ___________
• Change owner: _____________ Date: ___________
• Service owner: ____________ Date: ___________

Every critical item must pass before the window opens. A failing critical item postpones the window; it is not a risk to carry into it. Record the date, the reviewer, and the name of whoever authorised proceeding with any item unmet.