Upgrading PostgreSQL across major versions: pg_upgrade, dump and restore, or a logical-replication switchover

Goal

Move a database to a new major version with a known downtime, a rehearsed rollback and no surprises afterwards in planner statistics, extensions or collations.

Prerequisites

The "Migration" section of the release notes for every major version being crossed (the upgrading chapter says to read all intervening notes). A tested backup. The new binaries installed next to the old ones, with the extension shared libraries built for the new version. An application test suite that can run against the new server.

Steps

1. Choose the method. Dump and restore with pg_dumpall from the newer binaries is the traditional path, can stream into a parallel server on another port, and is slow for large databases. pg_upgrade migrates in place; the documentation says upgrades can be performed in minutes, particularly with --link. A logical-replication standby on the new version is the documented third path: several seconds of switchover, at the cost of setup effort and logical replication's restrictions.
2. For pg_upgrade, run pg_upgrade --check with the intended mode flag (--link, --clone or --swap) while the old server is still running; it reports incompatibilities and the manual steps to expect.
3. Rehearse on a copy. The documentation suggests a schema-only copy with dummy data for deployment testing, or a copied cluster upgraded in link mode.
4. Fix the rollback before starting. In copy or clone mode the old cluster stays unmodified. With --link, the old cluster is unsafe to use once the new one has been started; with --swap, it is destructively modified during file transfer. In those two cases rollback means restoring from backup.
5. Run the upgrade in the maintenance window; carry pg_hba.conf and postgresql.conf changes over to the new cluster.
6. Regenerate what was not transferred. From PostgreSQL 18 on, pg_upgrade retains most optimizer statistics but not extended statistics; the documentation instructs running vacuumdb --all --analyze-in-stages --missing-stats-only and then vacuumdb --all --analyze-only. Older versions transfer no statistics, so plans are poor until ANALYZE has run everywhere.
7. Run the post-upgrade script files that pg_upgrade generates (extension updates among them), reindex whatever the release notes or collation-version warnings demand, run the test suite, and delete the old cluster only when satisfied.

Expected result

A cluster on the new version with fresh statistics and current extensions, a deliberately chosen rollback path, and a note of how long each step took, for the next upgrade.

Limits and test basis

Steps follow the cited documentation; durations depend on size, mode and hardware and are not claimed. Standbys need their own procedure (the pg_upgrade page describes an rsync-based one), and the replication path needs a primary key or replica identity on every table that receives updates or deletes.

Reverting a link-mode upgrade

In link mode the point of no return is the first start of the new cluster, not pg_upgrade itself. If the run aborts before linking begins, the old cluster is untouched. If linking has begun but the new server has not been started, the old cluster is intact apart from global/pg_control, which pg_upgrade renamed to pg_control.old; removing the suffix restores it. Once the new cluster has started it has written to the shared files, and the old cluster must not be used again; from then on, rollback means restoring from backup. Rehearse the interval between the two: run every check that does not need a running server before that first start. In swap mode the old cluster is modified during the transfer, so only the backup path applies.