← blog · November 13, 2026

PostgreSQL Major Version Upgrades: Choosing Between pg_upgrade, Dump and Restore, and Logical Replication

A major version upgrade is a data migration in disguise. How the three methods compare on downtime and rollback, the real cost of pg_upgrade's link mode, post-upgrade statistics and extensions, sequences that are not replicated, and indexes silently broken by OS collation changes.

A minor PostgreSQL upgrade (17.4 to 17.5) is a binary swap and a restart. A major upgrade (16 to 17) is not: the on-disk format of the data directory changes between major versions, and the new server refuses to open the old one. Every major upgrade is therefore a data migration in disguise, and the method you pick decides your downtime, your rollback options and how much can go wrong.

There are three ways to do it: dump and restore, upgrade in place with pg_upgrade, or move to a new cluster through logical replication. For most teams pg_upgrade is the right default. The other two win under specific conditions.

Dump and restore

Take a dump from the old server with the new version's pg_dump and load it into the new cluster with pg_restore. Always use the newer client tools; an old pg_dump is not guaranteed to produce output the new server handles correctly.

pg_dumpall --globals-only -h old-server > globals.sql
pg_dump -h old-server -Fd -j 4 -f app.dump app
psql -h new-server -f globals.sql
createdb -h new-server app
pg_restore -h new-server -d app -j 4 app.dump

--globals-only carries roles and tablespaces. A per-database dump does not include them, and forgetting them fills the restore with ownership errors. The upside of this method is that everything is rebuilt from scratch: fresh indexes, no bloat, and no collation surprises from an operating system change. The downside is time. Downtime equals dump plus restore, and rebuilding large indexes takes hours. Below a few tens of gigabytes, with a system that can tolerate an outage, it is the least surprising path.

In-place upgrade with pg_upgrade

pg_upgrade leaves user data files alone. It rebuilds the system catalog for the new version and adopts the existing data as is. Both versions' binaries must be installed on the same machine.

pg_upgrade \
  -b /old/version/bin -B /new/version/bin \
  -d /old/data -D /new/data \
  --link --check

--check changes nothing and reports incompatibilities: extensions missing on the new side, unsupported data types, mismatched settings. Run it first, and run the real command without --check only once the report is clean.

The transfer mode sets the duration. The default mode copies files; it is safe, but takes time proportional to data size and needs twice the disk. --link adopts files through hard links, and even a multi-terabyte cluster usually finishes in minutes. The price: once the new cluster has been started, the old one is no longer safe to use, because both share the same files. --clone, where the filesystem supports it, gets close to link speed without damaging the old cluster.

My recommendation is to use --link, but base the rollback plan on a backup taken just before the upgrade and whose restore you have actually tested, not on the old cluster. Believing you are safe because the old directory is still there is exactly wrong in this mode.

Two jobs remain afterwards. First, planner statistics. Before version 18, pg_upgrade does not carry them over, and the new cluster runs its first minutes on bad plans. From 18 on, most statistics are preserved, but extended statistics defined with CREATE STATISTICS still arrive empty. Either way, analyze before opening traffic:

vacuumdb --all --analyze-in-stages

--analyze-in-stages collects coarse statistics first and detailed ones later, so you can open traffic early and let refinement finish in the background. Second, extensions. Even with the new extension version installed, its definition inside each database stays at the old version until you run ALTER EXTENSION name UPDATE;.

Moving to a new cluster with logical replication

When the downtime budget is measured in minutes and the data is large, build a separate cluster on the new version and feed it from the old one through logical replication. The source needs wal_level = logical, which requires a restart, so do that well before upgrade day.

-- on the old cluster
CREATE PUBLICATION upgrade FOR ALL TABLES;

-- on the new cluster, after the schema is loaded
CREATE SUBSCRIPTION upgrade
  CONNECTION 'host=old-server dbname=app user=replicator'
  PUBLICATION upgrade;

Logical replication does not carry schema. Load it first with pg_dump --schema-only; the subscription then performs the initial copy and starts streaming changes. At cutover you stop writes, wait for lag to reach zero, synchronize sequences and point connections at the new cluster.

The real advantage is rollback: set up a subscription in the reverse direction after cutover and the old cluster stays current, ready to take traffic back. The cost is just as real: far more setup and monitoring than the other two methods, and every trap below concentrates here.

Pitfalls

Tables without a primary key. Logical replication ships UPDATE and DELETE by identifying the row. On a table with no primary key and no REPLICA IDENTITY setting, those statements fail on the source once the table is published, which means upgrade preparation breaks the live application. Find these tables first:

SELECT c.oid::regclass
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  );

Sequences are not replicated. Table rows flow, but sequence values on the new cluster stay at their starting point, so the first INSERT after cutover collides with an existing id. Once writes are stopped, read the values on the old cluster and apply them to the new one:

SELECT format('SELECT setval(%L, %s, true);',
              format('%I.%I', schemaname, sequencename), last_value)
FROM pg_sequences
WHERE last_value IS NOT NULL;

Schema changes during the migration. DDL is not replicated. If a migration lands on the old cluster while replication runs, the new side does not know the new column and the subscription stops. Freeze schema migrations from preparation until cutover.

A forgotten replication slot. The subscription creates a slot on the source. A trial subscription that gets abandoned keeps the source from recycling WAL and eventually fills the disk. After a rehearsal, drop the subscription and verify that the slot is gone too.

Collation rules that change with the operating system. In most installations PostgreSQL takes text ordering from the operating system's C library. If you combine the major upgrade with a new OS image and move the data files physically (pg_upgrade or a physical replica), text indexes remain built with the old ordering. Queries do not error; they return wrong results, and unique constraints let duplicates in. glibc 2.28 is the best known example of this change. Either change the OS as a separate step, or rebuild affected indexes with REINDEX after the upgrade. Version 15 and later warn about a database-level collation version mismatch; when you see it, rebuild the indexes and then run ALTER DATABASE name REFRESH COLLATION VERSION;. Dump and restore and logical replication rebuild indexes, so they are immune.

Physical replicas. pg_upgrade upgrades only the primary. Streaming replicas do not follow. The documentation describes an rsync procedure for upgrading standbys alongside, but it is unforgiving; in most cases rebuilding replicas with pg_basebackup after the upgrade is safer. Write the resulting window without redundancy into the outage plan.

Upgrading without a rehearsal. You do not estimate upgrade time, you measure it. Rehearse on a copy of production data with the exact commands, and check the --check output, the analyze duration and the new version's plans for your heaviest queries. The planner changes between releases, and a plan can get worse as easily as better.

When not to pick each

Do not choose logical replication just because it sounds zero-downtime. If a one hour maintenance window is acceptable, pg_upgrade --link gets you the same result with far fewer moving parts. Logical replication pays for its complexity only when downtime is truly limited to minutes or a live rollback path is a business requirement.

Do not choose dump and restore for large databases; restore time grows faster than data size once indexes are counted. And do not run pg_upgrade on the same day as an OS change without checking collations.

Whichever path you take, the rule is the same: run the upgrade on a copy first, prove rollback with a backup, and do not open traffic before statistics are collected.