Major version upgrade options compared · PostgreSQL

Major versions change the on-disk format, so the data must be moved. There are four ways, and the right one is decided almost entirely by downtime tolerance and size.

The four routes

Route	Downtime	Cluster size	Rollback
`pg_dump` / `pg_restore`	Hours to days	Small only	Old cluster untouched
`pg_upgrade --copy` (default)	Minutes to hours	Medium	Old cluster untouched
`pg_upgrade --link` / `--swap`	Minutes	Any	None. See below
Logical replication	Seconds	Any	Switch back

Dump and restore

Simple, thorough, and slow. Everything is read through the SQL layer and every index rebuilt, which lesson XIII-02 covered.

It has one property nothing else offers: it produces a cluster with no inherited physical baggage — no bloat, no fragmented indexes. That is occasionally worth the downtime on a small database and never worth it on a large one.

`pg_upgrade` in copy mode

The default. Copies the data files, converts the catalogue.

Read-only / Safea real 17.11 to 18.6 upgrade, copy mode— EXECUTION VALIDATED. A 78 MB table, 400,000 rows, two indexes.

$ pg_upgrade --old-datadir=... --new-datadir=... --old-bindir=... --new-bindir=...

Upgrade Complete
----------------
ELAPSED: 2 seconds

`pg_upgrade --link` and `--swap`

--link hard-links the data files into the new cluster instead of copying them, so the time is independent of cluster size — minutes on any size.

Read-only / Safethe transfer modes, compared between the two binaries— EXECUTION VALIDATED. --swap is present in 18 and absent in 17, confirmed from the binaries themselves rather than from release notes.

$ /usr/lib/postgresql/17/bin/pg_upgrade --help | grep -E 'link|clone|copy|swap'
/usr/lib/postgresql/18/bin/pg_upgrade --help | grep -E 'link|clone|copy|swap'

--- 17 ---
-k, --link                    link instead of copying files to new cluster
    --clone                   clone instead of copying files to new cluster
    --copy                    copy files to new cluster (default)
    --copy-file-range         copy files to new cluster with copy_file_range

--- 18 ---
(the same four, plus)
    --swap                    move data directories to new cluster

--link and --swap have no rollback

With --link, the two clusters share the same files on disk. Once the new cluster has started and written anything, the old cluster is not a fallback — starting it would operate on files the new one has already modified.

--swap moves the directories outright, which is faster still and even more final.

The rollback for both is restore from backup, which is a completely different operation with a completely different duration from “start the old cluster”.

That is not an argument against them. It is an argument for:

A verified backup taken immediately before, per Part XIII.
A rehearsal on a copy, per lesson XVII-08.
A decision, made in advance, about what you do if the new cluster is wrong — because “go back” is not on the menu.

--clone is a middle ground where the filesystem supports reflinks (Btrfs, XFS with reflink=1): fast like --link, with copy-on-write so the old files are not shared destructively.

Logical replication

Replicate from the old major version to a new one, let it catch up, then switch the application over. Downtime is the switchover — seconds.

It is the only route that offers near-zero downtime on a large cluster, and it costs the most in preparation:

Every table needs a replica identity — a primary key, or an explicitly configured one. Tables without one cannot replicate updates or deletes.
Sequences are not replicated. They must be advanced on the target before the switchover, and forgetting produces duplicate key errors immediately afterwards.
DDL is not replicated. The schema must be kept in step manually.
Large objects are not replicated.
The initial sync of a large table is itself a long operation.

It is a project rather than a procedure, and on a large cluster with a strict downtime budget it is the right one.

Choosing

Situation	Route
Small database, downtime available	Dump and restore
Medium, hours available	`pg_upgrade --copy`
Large, minutes available, backup verified	`pg_upgrade --link` / `--swap`
Large, seconds available	Logical replication
Reflink-capable filesystem	`pg_upgrade --clone`

What none of them carry across for free

Configuration. postgresql.conf is not migrated. Settings are removed and added between major versions, and a copied file can contain settings the new version rejects at startup.

Extensions. They must be installed for the new major version before the upgrade. pg_upgrade --check reports missing ones, which is a large part of why the check is worth running days ahead.

Cumulative statistics. Lesson XVII-07 measured last_analyze NULL and n_live_tup zero after an upgrade, even in 18 where planner statistics survive.

Anything outside the cluster. Cron jobs, backup scripts, monitoring queries referencing views that changed — lesson XVI-03’s pg_stat_bgwriter split is exactly this.

Why the on-disk format changes at all, and the versions where it did not

A major version bumps the catalogue version in pg_control, and PostgreSQL refuses to start on a data directory whose catalogue version does not match. That check is what makes major upgrades a deliberate operation rather than an accident.

The format changes because the catalogue changes: new columns in system tables, new system tables, changed representations. Sometimes the heap page format changes too, and sometimes it does not — which is the subtlety behind pg_upgrade being fast at all.

pg_upgrade works precisely because user data pages are usually compatible across versions. It converts the catalogue, which is small, and moves the user data files without reading them. If every page needed rewriting, --link could not exist and every upgrade would cost a full rewrite.

That has a consequence worth knowing: when a release does change the user page format, pg_upgrade cannot avoid the work, and the release notes say so prominently. It has been rare, and it is the reason pg_upgrade --check exists rather than the tool simply assuming.

Two related details.

Indexes are not always carried across unchanged. A release that changes an index type’s on-disk representation requires those indexes to be rebuilt, and pg_upgrade reports which. The upgrade completes and the affected indexes are invalid until rebuilt, which is a post-upgrade task that must be planned rather than discovered.

Collation changes are the sharper version of this. An operating system upgrade that changes the C library’s collation data invalidates text indexes just as thoroughly as a format change, and PostgreSQL will not necessarily tell you. PostgreSQL 18 records collation version information and warns when it differs, which converts a silent correctness problem into a visible one — lesson XIII-08 listed it among the failures only a restore test finds, and a major upgrade that also moves operating system is the most common way to meet it.

What to take from this

Four routes, chosen by downtime tolerance and size, not by elegance.
Measured: copy mode on 78 MB took 2 seconds. That establishes the mechanism and nothing about a terabyte.
--link and --swap have no rollback. The fallback is a restore.
--swap is new in 18, verified by comparing the 17 and 18 binaries.
Logical replication gives seconds of downtime and needs replica identities, manual sequence advancement, and schema discipline.
None of them carry configuration, extensions, cumulative statistics or anything outside the cluster.

Cross-course references

Linux for Production Sysadmins — Part LXXIII (Change management) covers choosing an approach by its rollback cost rather than by its runtime, which is the decision this lesson turns on.
Git, CI/CD & GitOps — Part LX (Forward fix versus rollback) covers the same choice in delivery terms, including the case where rollback stops being available partway through.