skip to content

Major-Version Migration

A PostgreSQL major upgrade uses a dump and restore, pg_upgrade or a logical-replication cutover, trading downtime for effort. Interviewers probe pg_upgrade's modes and what it leaves behind.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

Why can't PostgreSQL 18 binaries start on a data directory initialized by PostgreSQL 17, and what are the three routes to a new major version?

level: juniorimportance: must knowfreq 62%

answer

  1. catalog layout, not page format
  2. PG_VERSION checked at startup
  3. logical dump with the newer binaries
  4. rebuild catalogs, reuse data files
  5. replicate across majors, then switch

basics

~20 s

A major release may change the system-catalog layout, so the server refuses a data directory from another major. The routes are dump and restore, pg_upgrade, which rebuilds the catalogs and reuses the data files, and a logical-replication cutover.

solid answer

~50 s

Minor releases never change the storage format, so they are a binary swap and restart. A **major** release may change the system catalogs, and at startup the server reads the data directory's `PG_VERSION` file and refuses with `database files are incompatible with server` if another major initialized it. Three routes move the data. **Dump and restore** uses the *newer* version's `pg_dumpall`/`pg_dump` into a fresh `initdb` cluster: simple, but downtime grows with data volume and index builds. **`pg_upgrade`** recreates the catalogs from a schema-only dump and reuses the user data files, so in its link-style modes the outage is minutes. A **logical-replication cutover** subscribes a new-major cluster to the old one and switches over in seconds, at the cost of the most preparation. Whatever the route, read the *Migration* section of every intervening major's release notes.

code

bash · 8 lines
bash
# 18 client binaries reading the old 14 server (old-db) and loading the new 18 server (new-db)
BIN=/usr/lib/postgresql/18/bin
$BIN/pg_dumpall -h old-db --globals-only > globals.sql
$BIN/pg_dump -h old-db -Fd -j 8 -f /backup/elearning.dir elearning
psql -h new-db -d postgres -f globals.sql
$BIN/createdb -h new-db elearning
$BIN/pg_restore -h new-db -d elearning -j 8 /backup/elearning.dir
$BIN/vacuumdb -h new-db --all --analyze-in-stages

go deeper

for a junior

Know that a minor update is a binary swap but a major one needs a new cluster, and name the three routes: dump and restore, pg_upgrade, and a logical-replication cutover.

for a middle

Explain why the server refuses an old data directory, why pg_upgrade is fast (catalogs rebuilt, data files reused) and why the dump should come from the newer pg_dump.

for a senior

Pick a route from the downtime budget, data size and team effort, and know what each leaves to do afterwards: statistics, globals, sequences or schema drift.

for a principal

Frame the route choice as a cost trade: hours of outage against operational complexity, and a standing old cluster as fallback against disk and coordination cost.

## Why a new major will not start on an old data directory PostgreSQL version numbers have two parts since version 10: in `18.6`, **18** is the major and **6** the minor. The manual draws a hard line between them: - **Minor releases** never change the internal storage format. You stop the server, replace the executables and start it again on the same data directory. - **Major releases** regularly change the layout of the **system catalogs** (the internal tables that describe tables, columns, types and functions), even though the format of the user data pages rarely changes. Every data directory carries a `PG_VERSION` file written by `initdb`. When a server starts, it compares that file with its own major version and, if they differ, stops with `database files are incompatible with server` and a detail line naming the version that initialized the directory. Nothing is damaged by trying; the server simply refuses. So a major upgrade always means producing a **new cluster** whose catalogs match the new binaries, and getting the user data into it. ## Route 1: dump and restore The traditional route is a **logical dump**: SQL (or an archive) describing every object and row, replayed into a new cluster. 1. Install the new binaries next to the old ones and `initdb` a new cluster. 2. Dump with the **newer** version's tools. The manual recommends this because they carry fixes and improvements, and the current dump programs can read any server back to 9.2; `pg_dump` cannot dump a server newer than itself. 3. Dump cluster-wide objects (roles, tablespaces) with `pg_dumpall --globals-only`, and each database with `pg_dump`; parallel dump and restore need the directory format (the formats themselves belong to the dump topic). 4. Restore, then gather planner statistics. In PostgreSQL 18, `pg_dump` does **not** include statistics unless you pass `--statistics`, so a restored database starts with none; `vacuumdb --all --analyze-in-stages` builds usable ones quickly. The strength of this route is that everything is rewritten: you can change `initdb` choices such as encoding or data checksums, and bloat disappears. The weakness is downtime: writes made after the dump starts are not in it, so the application must stay read-only or offline for the whole dump, restore and index build, which is hours for a multi-terabyte database. ## Route 2: pg_upgrade `pg_upgrade` is the fast in-place route. It starts the old cluster briefly, runs the new version's `pg_dump --binary-upgrade` to capture the **schema only**, restores that schema into the new cluster so the catalogs are created in the new layout, and then **transfers the existing data files** instead of reloading rows. The transfer mode decides how: copy (the default), hard links, file cloning, `copy_file_range`, or, since 18, swapping directories. With link-style modes the outage is minutes regardless of data size; with plain copy it grows with the data. It supports old clusters from 9.2 onward and needs the new cluster initialized with compatible options. ## Route 3: logical-replication cutover Logical replication works **between different major versions**. You build the new-version cluster, load the schema, subscribe it to a publication on the old primary, let it copy and catch up, then stop writes, confirm it has applied everything and repoint the applications. The manual describes the switch-over as costing only several seconds of downtime. The price is preparation: schema changes, sequence values and large objects are not replicated and must be handled by hand. ## Comparing the routes | Route | Typical downtime | Extra disk | Effort | Old cluster afterwards | |---|---|---|---|---| | Dump and restore | Hours for large data | Dump file plus a full new cluster | Low | Untouched | | `pg_upgrade --copy` | Grows with data size | A full second copy | Medium | Untouched | | `pg_upgrade --link` / `--swap` | Minutes | Little | Medium | Unusable once used | | Logical cutover | Seconds | A full second cluster | High | Untouched, still running | ## Before any of them - Read the **Migration** section of the release notes of every major you cross. Skipping majors is allowed, but going from 14 to 18 means reading the 15, 16, 17 and 18 notes. - Test the applications against the new major on a copy of production; administrative views and system catalogs change between majors even when SQL behaviour does not. - Take a backup first, whichever route you choose.

  • Why should the dump be taken with the newer version's pg_dump rather than the old server's own copy?
    The manual recommends the newer `pg_dump` and `pg_dumpall` because they carry bug fixes and improvements, and current releases read any server back to 9.2. The reverse does not work: `pg_dump` cannot dump a server newer than itself. Installing the new binaries first also lets you prepare the new cluster in parallel, which shortens the outage.
  • Can you go from PostgreSQL 14 straight to 18, or must you step through each major?
    You can go straight there: `pg_upgrade` accepts any old cluster from 9.2 onward, and dump and restore has no stepping requirement either. What you cannot skip is reading: the incompatibilities of 15, 16, 17 and 18 all apply, so read each major's Migration section and test the application against 18.

  • A major upgrade is just installing new binaries and restarting, like a minor update.
  • pg_upgrade rewrites every table into the new on-disk format, so it takes as long as a reload.
  • The dump must be taken with the old server's own pg_dump version to stay compatible.
  • You must step through every intermediate major, 14 to 15 to 16 to 17 to 18.
  • In PostgreSQL 18 a plain pg_dump carries planner statistics, so no ANALYZE is needed after restore.
open as a page

In PostgreSQL 18's pg_upgrade, how do the copy, link, clone, copy-file-range and swap modes differ in speed, disk use and the way back?

level: middleimportance: must knowfreq 55%

basics

~20 s

Copy, the default, duplicates files and keeps the old cluster intact; link hard-links them, fast, but the old cluster dies once the new starts; clone and copy-file-range share blocks where supported; 18's swap moves directories, destroying the old cluster.

open as a page

What does PostgreSQL's pg_upgrade --check verify, and why run it days before the window against the still-running old server?

level: middleimportance: should knowfreq 48%

basics

~20 s

pg_upgrade --check runs the upgrade's compatibility checks without changing any data: control-data settings, blocked data types, prepared transactions, missing extension libraries and more. It works against the live old server, so blockers surface days ahead instead of mid-outage.

open as a page

Upgrading a PostgreSQL 14 cluster to 18, pg_upgrade --check stops with 'old cluster does not use data checksums but the new one does'. Why, and what are your options?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Since PostgreSQL 18, initdb enables data checksums by default, and pg_upgrade requires both clusters to match. Re-run initdb for 18 with --no-data-checksums, or enable checksums on the old cluster offline with pg_checksums --enable before upgrading.

open as a page

Moving a 3 TB PostgreSQL 14 e-learning database to 18 through logical replication with seconds of downtime, what is the cutover sequence and what does replication not carry?

level: seniorimportance: should knowfreq 40%

basics

~20 s

Load roles and schema into 18, subscribe it to 14 and freeze DDL; at cutover stop writes, wait until the final LSN is confirmed, setval the sequences and repoint clients. DDL, sequence values, large objects and materialized views are not replicated.

open as a page

After pg_upgrade from PostgreSQL 14 to 18, which planner statistics arrive with the data, which do not, and what do you run before reopening traffic?

level: seniorimportance: should knowfreq 42%

basics

~10 s

PostgreSQL 18's pg_upgrade carries most per-column and per-table planner statistics, but not extended statistics, extension-made statistics or cumulative activity counters. Run vacuumdb --all --analyze-in-stages --missing-stats-only, then vacuumdb --all --analyze-only.

open as a page