Upgrade rehearsal and post-upgrade validation · PostgreSQL

An upgrade you have not rehearsed is a procedure you are testing on production, with an audience.

Rehearsing

On a restored copy of production. Not a subset, not a schema-only copy, not staging with a thousandth of the rows. The duration you need depends on the data volume and on the number of objects, and neither transfers from a smaller copy.

This is also, usefully, a restore test — lesson XIII-08’s requirement, satisfied by an exercise you were doing anyway.

With the real procedure, from the written runbook. If the runbook is wrong, the rehearsal should discover that.

Measure four things:

Number	Why
Wall-clock, stop to accepting connections	Your window
Wall-clock of the `ANALYZE` afterwards	Often longer than the upgrade
What `--check` reported	The problems, before the window
What broke afterwards	The list you would otherwise find live

Validating

“It started” is not validation. Four levels, and the last two are the ones that get skipped.

1. It is running the version you expect

SELECT version();

2. The data is there

-- row counts per table, compared against the same query before
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;

That view is empty immediately after an upgrade — lesson XVII-07 measured n_live_tup at zero — so run ANALYZE first, or count directly:

SELECT count(*) FROM orders;

Take these counts before the upgrade too. A comparison needs both sides.

3. The application works

Its own test suite, against the upgraded cluster. Not a sample of queries somebody wrote for the occasion.

4. The plans have not changed catastrophically

This is the one that is skipped and the one that causes the “the upgrade made everything slow” incident.

-- capture before, on the old cluster
EXPLAIN (FORMAT JSON) <your top 20 queries by total_exec_time>;
-- capture after, on the new one, and diff

Take the top 20 from pg_stat_statements on the old cluster, capture their plans on both, and compare. A planner change between major versions is a feature; discovering it in production is not.

PostgreSQL 18 makes this less urgent, and not unnecessary

Before 18, an upgraded cluster had no planner statistics at all, so every query was planned from defaults until ANALYZE completed. The first hour after an upgrade was reliably terrible, and teams learned to schedule around it.

Lesson XVII-07 measured 18 carrying planner statistics across identically. That removes most of the effect.

It does not remove all of it, which is what the completion message’s “Some statistics are not transferred” means, and it does not remove plan changes caused by the planner itself changing between versions.

So: still run vacuumdb --all --analyze-in-stages --missing-stats-only, and still compare plans. The reason has shifted from “there are no statistics” to “the planner is a different planner”, and the second reason does not go away.

The rollback

The plan depends entirely on the transfer mode, and must be decided before the window:

Mode	Rollback
Dump/restore	Start the old cluster. Minutes
`pg_upgrade --copy`	Start the old cluster. Minutes
`pg_upgrade --link` / `--swap`	Restore from backup. Hours
Logical replication	Switch the application back. Seconds

Do not run delete_old_cluster.sh on the day

In copy mode the old cluster is your rollback, and the script deletes it:

#!/bin/sh

rm -rf '/var/lib/postgresql/17data'

Keep it for days. The disk is cheaper than the alternative, and the pressure to reclaim space is never as urgent as it feels.

Go/no-go, decided in advance

Write these down before the window opens, because deciding them inside it is how a bad upgrade gets accepted:

Abort if --check reports anything unresolved.
Abort if the verified backup is not verified.
Abort if the rehearsal’s duration exceeded the window.
Roll back if row counts do not match.
Roll back if the application test suite fails.
Investigate, do not roll back, if a few plans changed — that is expected, and rolling back for it means never upgrading.

The last one matters as much as the others. A team that rolls back on any regression will defer major upgrades indefinitely, and end up doing four versions at once on an unsupported release, which is strictly worse.

Where the upgrade window actually goes

People plan for the pg_upgrade run and are surprised by the rest. On a large cluster the run is frequently the smallest term.

Stopping the old cluster cleanly. A fast shutdown waits for transactions to end and performs a shutdown checkpoint, which on a cluster with a large dirty buffer pool is real time. Lesson XII-07 measured a fast shutdown at 119 ms on a small cluster and a smart shutdown failing after 10 seconds while sessions remained — which is why the shutdown mode is part of the plan, not a detail.

The schema restore. Scales with object count, not data size. A database with tens of thousands of tables, partitions and indexes spends most of the window here, and it is the term a small rehearsal most badly under-represents.

ANALYZE afterwards. On pre-18 this often exceeded the upgrade itself. --analyze-in-stages exists to make the cluster usable early: it runs three passes with increasing statistics targets, so rough statistics arrive quickly and are refined afterwards.

Rebuilding standbys. Each one is a full base backup, which on a large cluster is hours — and until they exist, the cluster has no failover target. This is the term most likely to be left out of the plan entirely, because it happens after everyone has declared success.

Taking a new base backup. Also mandatory, also hours, and until it exists the recovery position is “restore the old cluster and upgrade again”.

A realistic plan on a large cluster therefore has an inner window, during which the database is down, and an outer window during which it is up but unprotected — no standby, no current backup. The outer window is usually much longer, it is rarely written down, and it is exactly the period in which a second incident is least survivable.

What to take from this

Rehearse on a restored copy of production, with the real procedure. It doubles as a restore test.
The rehearsal produces your duration. This course’s 2 seconds is a mechanism, not a projection.
Validate on four levels: version, data, application, plans.
PostgreSQL 18 carries planner statistics across, which removes most of the post-upgrade slowness — not the plan changes.
The rollback depends on transfer mode. --link and --swap mean restore from backup.
Do not run delete_old_cluster.sh on the day.
Decide go/no-go criteria in advance, and do not roll back for a few changed plans.
Plan the outer window too: rebuilding standbys and taking a new backup, during which the cluster is unprotected.

Cross-course references

Linux for Production Sysadmins — Part LXIV (Rolling maintenance) covers rehearsing on production-scale data so the window is sized from a measurement, and Part LXXXIII (Operational documentation) covers recording the timings for the next one.
Git, CI/CD & GitOps — Part LXIV (Auditability) covers keeping the before-and-after evidence that makes “the upgrade worked” a claim somebody else can check.