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?
answer
- catalog layout, not page format
- PG_VERSION checked at startup
- logical dump with the newer binaries
- rebuild catalogs, reuse data files
- replicate across majors, then switch
basics
~20 sA 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 sMinor 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# 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-stagesgo deeper
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.
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.
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.
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.