We booked a forty-minute maintenance window to take our primary from PostgreSQL 13 to 16. The database was about 1.1 TB, and the plan was the standard one: stop the world, run pg_upgrade with --link, start the new cluster, done. The pg_upgrade run itself took six minutes and eleven seconds. I remember the number because it was the last thing that went to plan that morning. What followed was four hours of hangover: query plans collapsed because nobody told me pg_upgrade does not carry planner statistics, one extension turned out to exist in the old cluster but not the new one's package set, and the rsync command I used to bring the standby across had synced an empty directory with great confidence. Nothing was lost and nothing was corrupt, which is the best kind of painful upgrade — but the window ran three hours over, and the slow-query alerts ran for two days after.
We have done four major upgrades since, across versions 14 through 17, and each one has been calmer than the last. This is the routine that made them calm: what pg_upgrade actually moves, what it deliberately leaves behind, and the rehearsal that turns cutover morning into a formality.
What does pg_upgrade actually carry across, and what does it leave behind?
pg_upgrade works by building a new cluster's catalog entries pointing at the old cluster's data files. With --link it hard-links those files instead of copying them, which is why a terabyte upgrades in minutes: no data blocks move, only metadata is rewritten. The constraints fall out of the mechanism. The old and new clusters must live on the same filesystem, because hard links do not cross filesystems. And once the link-mode upgrade finishes, the old cluster must never be started again — both clusters now share physical files, and a postmaster writing through the old catalog will corrupt data the new cluster owns. I rename the old data directory the same morning, before anyone can fat-finger a pg_ctl start against it.
The list of what does not cross is the part that bites. Planner statistics — the contents of pg_statistic that the query planner uses to choose plans — are not copied. Your new cluster starts with a planner that knows nothing about your data distribution. Logical replication slots and subscriptions do not survive on versions before 17, which matters if anything downstream consumes changes from this cluster; on 17 pg_upgrade learned to migrate slots and subscriptions, and the release notes are worth reading line by line before you count on it. Configuration files are not moved: postgresql.conf, pg_hba.conf, pg_ident.conf are yours to port, and the new version will have renamed or retired a few parameters — recovery_conf is long gone, wal_keep_segments became wal_keep_size, and your old config will fail the new cluster's startup check if you copy it blindly. And anything stored outside the catalog, like extension-managed files under the data directory, is your problem to inventory.
Why were our queries slow after a technically flawless upgrade?
Because a planner with no statistics falls back to default selectivity guesses, and those guesses are wrong in the expensive direction. Our order-lookup endpoint went from an index scan to a sequential scan over 400 million rows because the planner assumed a match-anything predicate returned half the table. The query did not change, the data did not change, and the plan fell apart anyway — that is the fingerprint of missing statistics, and it is why the first hour after every major upgrade is now an analyze hour, not a celebration hour.
pg_upgrade knows about this and writes a helper script, analyze_new_cluster.sh, which runs vacuumdb with the analyze-in-stages flag. The staged approach matters: instead of one full-strength analyze pass over every table, it does a fast, low-statistics-target pass first so the planner has something, then progressively better passes. We run the first stage before reopening traffic and let the rest finish under light load. During that window I watch which tables are still waiting:
SELECT schemaname, relname, n_live_tup,
last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE last_analyze IS NULL
AND last_autoanalyze IS NULL
AND n_live_tup > 0
ORDER BY n_live_tup DESC
LIMIT 30;Any large table still on that list an hour after cutover is a plan regression waiting to be reported by a user. On our biggest tables I do not wait for vacuumdb to reach them at all — I ANALYZE them by name immediately, largest first, and let the staged pass handle the long tail. The other number I watch is the sequential-scan share on hot tables: a spike in seq_scan on tables that were index-served yesterday is the same statistics gap showing up in counters, which beats discovering it in the slow-query log. If estimates are a topic you want to go deeper on, the planner statistics field notes cover how the planner actually uses what analyze collects.
How do you rehearse a major upgrade instead of improvising one?
The same way you rehearse a restore: on a copy, on a schedule, with a timer running. Once a month in the quarter before the cutover, we restore the latest production base backup to a scratch host, initdb a cluster of the new version with identical locale, encoding, and checksum settings — locale mismatches are a silent plan-corruption risk of their own, and the collation corruption notes are the reason I check collversion on every upgrade now — install the new version's packages and every extension the old cluster loads, then run pg_upgrade --check followed by the real thing. The check pass is the whole point. It compares the two clusters without touching anything and lists what will fail: extensions present in the old cluster and missing from the new one, databases whose encodings disagree, tables using data types that changed meaning between versions, and any lingering prepared transactions, which block an upgrade outright.
Three rehearsal rules came out of our painful first attempt. First, always run the pg_upgrade binary from the new version — the old version's binary does not know the new catalog layout, and the error messages when you get this wrong are not obviously about that. Second, time every rehearsal and write the number down; our six-minute figure came from the third rehearsal, not the cutover, and it is what justified the forty-minute window to management. Third, rehearse the fallback, not just the upgrade. In link mode the fallback is "the old cluster still works if the new one never started," which is only true until the new cluster accepts its first write — after that, fallback means restore from backup, and you want to have said that out loud in the plan meeting rather than during the incident.
What breaks around the cluster: extensions, standbys, and slots?
Extensions break first because they live in two places. The catalog rows come across with the upgrade, but the shared libraries and SQL scripts live in the new version's installation directories, and pg_upgrade will refuse to proceed until every extension the old cluster references is installed there. Version drift inside that requirement is the subtle failure: an extension can be present but at a version whose ABI changed, and the fix is a plain ALTER EXTENSION ... UPDATE after cutover — schedule that into the window, not into next week.
Standbys break second, because physical replication is version-locked: a 16 standby cannot replay WAL from a 17 primary. pg_upgrade documents an rsync procedure that hard-links the standby's data files against the upgraded primary's, and it genuinely works — our failure was my path mistake, not the procedure's — but the relative-path arithmetic in that recipe is exactly the kind of thing to get wrong at 05:40. Since then we rebuild standbys with pg_basebackup from the new primary. It costs a couple of hours of network for a terabyte and removes an entire category of "did the rsync actually..." from the morning. Logical slots break third, on versions before 17: any CDC consumer — Debezium, a homegrown logical decoding pipeline like the one covered in the replication monitoring guide — loses its slot and needs a fresh snapshot. The pre-cutover checklist now has a line that says "page the consumers," because the one time we forgot, the downstream team found out from their own alarms.
When is logical replication the better upgrade path?
When the business cannot give you the window. A logical subscriber can run a newer major version than its publisher, so the zero-downtime shape is: build a new-version cluster, subscribe it to the old primary, let it catch up, cut over the application, done. It is a genuinely good answer that we have used exactly once, for the cluster whose maintenance window was measured in seconds. The costs are real: you need double the storage during the migration, DDL does not replicate so every schema change during the catch-up window needs a coordinated apply on both sides, sequences are not replicated and must be advanced by hand as part of the cutover runbook, and the replication slot on the old primary pins WAL until you drop it. For anything that can tolerate an hour of downtime, pg_upgrade --link with a rehearsed fallback is simpler, faster, and has fewer moving parts to explain to the on-call rota. Choose logical when downtime is the constraint, not because it feels more modern.
Watching the post-upgrade settling with MonPG
The hours after a major upgrade are a monitoring problem before they are anything else: per-statement mean times against the pre-upgrade baseline tell you whether the staged analyze is keeping up, the sequential-scan share on hot tables tells you which plans the planner is guessing on, and analyze progress per table tells you how much runway is left. MonPG graphs exactly these series — pg_stat_statements latency history, scan mix, and autovacuum and analyze activity — as part of its PostgreSQL monitoring, so the regression our first upgrade hid for two days now shows up as a line crossing its own baseline within the hour. The upgrade is six minutes. The settling is the project — instrument the settling.