Major Version Upgrades

Postgres ships a major version every autumn and supports each one for five years, so an upgrade is a date in a calendar rather than an incident, right up until the version falls out of support and becomes both at once. Two methods matter and their downtime differs by orders of magnitude. Almost everything that decides how the night goes happens before either of them runs.

Cartwheel is moving from 17 to 18 in the spring. The cluster on pg-primary is roughly 700 GB, operations will sign off a two-hour window at 02:00 on a Tuesday, four extensions are installed, and there are two replicas that pg_upgrade will not touch. Those four facts choose the method between them, and how they do it is the interesting part of this topic rather than which command gets typed.

Minor Releases Are a Restart

A minor upgrade, 18.5 to 18.6 for instance, never changes the internal storage format and is compatible in both directions inside the same major. Stop the server, replace the binaries, start it again; the data directory is untouched, there is no dump, no rebuild and nothing to validate afterwards. Cartwheel does this on a rolling basis with the replicas first, so the primary's restart is the last thing that happens and the shortest.

Teams skip these for years on the grounds that upgrades are risky, and the result is a cluster carrying every security fix and every data-corruption fix released since the install. The risk profile is inverted: applying a minor release is one restart with a known rollback, while not applying it accumulates exactly the defects that eventually force an emergency window. Cartwheel runs them on a monthly cadence, replicas first.

pg_upgrade and Its Transfer Modes

A major upgrade changes the on-disk catalogue format, so the new server cannot simply open the old data directory. pg_upgrade builds the new cluster's catalogue from the old one and then moves the data files across, so it finishes in minutes on a cluster that a dump and reload would take most of a day to rebuild. The choice of how those files move is the entire downtime decision.

pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --clone --check

The five transfer modes differ in what they destroy. --copy is the default and the slow one: hours on 700 GB, and the old cluster survives intact. --link hard-links the files instead, which takes minutes and needs both data directories on one filesystem, and once the new cluster has started the old one must never be started again. --clone uses filesystem reflinks where they exist, giving link-mode speed while leaving the old cluster untouched. --copy-file-range is the Linux and FreeBSD middle ground. --swap, new in 18, moves the directories rather than the files and is potentially the fastest of all on a cluster with many relations, with the sharpest edge attached: once the transfer step begins the old cluster is destructively modified and is no longer safe to start.

Run --check first in every case and read what it says. It runs against the live old cluster before anything is moved, and it catches the failures that are expensive to discover halfway through: an extension with no matching library on the new version, a mismatch in the checksum setting between the two clusters, incompatible data types left behind by an old feature. For Cartwheel the answer is --clone, because the filesystem supports reflinks and because keeping the 17 cluster startable is the cheapest rollback in the whole plan.

Statistics, and What 18 Changed

Before 18, a freshly upgraded cluster had no optimizer statistics at all. Every table looked like a blank slate to the planner, every estimate was a default, and the first hour after the window was a wall of sequential scans that a generation of engineers remembered as "the new version is slower". The fix was always the same and always ran too late: a full ANALYZE across the cluster while production traffic was already arriving. Since 18, pg_upgrade carries most optimizer statistics across with the data.

Three things are still not carried, and the third is the one that surprises people. Extended statistics created with CREATE STATISTICS do not transfer, statistics added by an extension do not transfer, and nothing from the cumulative statistics system transfers, so the counters autovacuum reads to decide a table is due all start at zero and a table that was 90% of the way to its next automatic vacuum arrives having made no progress. The upgrade prints the commands to fix it.

vacuumdb --all --analyze-in-stages --missing-stats-only   -- minimal, fast
vacuumdb --all --analyze-only                             -- the full pass

The first pass generates a rough statistic for anything that has none, in stages, so the planner has something usable within minutes rather than after a complete scan of orders and delivery_events. The second brings every relation up to date and refills the cumulative counters that drive vacuum and analyze scheduling. For Cartwheel this matters twice over, because the Saturday slowdown Chapter 9 diagnosed was a stale row estimate on orders, and the first Saturday after an upgrade arrives with an extended statistics object that has to be re-created by hand.

The Path With Seconds of Downtime

The alternative is logical replication into a cluster that is already running the new version. Chapter 13 built that machinery; this topic only chooses it. A new 18 cluster subscribes to the 17 primary, catches up over days while both run, and then the cutover is a matter of seconds: stop writes, confirm the subscriber has applied everything, advance the sequences, repoint pgbouncer-01. pg_createsubscriber, available since 17, converts a physical standby into a logical subscriber, which removes the initial copy of 700 GB from the critical path entirely.

The cost is everything that comes with running two clusters for a week. DDL is coordinated by hand because it does not replicate, every published table needs a usable replica identity, and the sequences must be advanced as an explicit cutover step, since a forgotten sequence means the new primary starts issuing orders.id values that already exist and the first unique violation arrives an hour later. The publisher also carries a slot that retains WAL if the subscriber stalls. Below a few hundred gigabytes with a window like Cartwheel's, pg_upgrade wins on simplicity; the logical path earns its complexity when the acceptable downtime is measured in seconds and there is nowhere to put a maintenance window.

The Pre-Flight Checklist

What breaks is rarely the upgrade command; it is the surroundings, and every item below belongs on a written list that gets read out on the night.

SELECT extname, extversion FROM pg_extension ORDER BY 1;

SELECT datname, datcollate, datctype FROM pg_database;

Extensions first: every one installed has to exist, packaged for the new major, on the day of the window, and on the provider's supported list too if the application ever moves to one. Collations second: a major upgrade usually rides along with an operating-system upgrade, the system collation library changes underneath it, and every index on a text column is then sorted by rules the server no longer uses, which Chapter 2 covers as a reindex rather than a surprise. Then the replicas, because pg_upgrade does not touch them: pg-replica-a and pg-replica-b are rebuilt in a documented order, and until they are, Cartwheel has no failover target. Finally the removed parameters, the changed defaults, the client drivers and the pooler binary.

Rehearsing, and the Rollback That Is a Restore

Restore last night's backup into a scratch cluster, upgrade that one, run the application's test suite against it, and time every phase with a clock. The restore rehearsal from Chapter 12 is a prerequisite for this rather than a parallel activity: if the restore does not work, the upgrade rehearsal cannot start and the rollback plan does not exist either. What the rehearsal produces is the number you promise operations, plus whatever the scratch cluster refused to do the first time.

Rollback depends entirely on the transfer mode. With --copy or --clone the 17 cluster is still startable, so falling back is stopping 18 and starting 17, losing every write that landed on the new cluster in between, which is why writes stay off until the verification passes. With --link or --swap there is no old cluster to go back to and the rollback plan is a restore from backup, on 700 GB, inside a window that has already half gone. Both plans belong on paper with the time each one takes, written before anybody touches the binaries.