Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

pg_upgrade to Postgres 18: old and new clusters differ on checksums

Postgres 18 enables data checksums in initdb by default. Match the old cluster, or enable checksums first and plan a longer window.

~5 min readSQL
#PostgreSQL#pg_upgrade#Migration#Release Notes
Two database cylinders with mismatched checksum plates and an amber mismatch marker, headline CHECKSUM MISMATCH

Introduction & Quick Summary

Symptom. pg_upgrade stops before touching a single table:

code
Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking for hash indexes ok *failure* Consult the last few lines of "pg_upgrade_server.log" for the probable cause of the failure. could not connect to source postmaster started with the command: ... -c data_checksums=off old and new clusters have different checksum settings

Or the check passes and the restore fails later with primary and foreign key constraints must use deterministic collations.

Cause. PostgreSQL 18 changed initdb: data checksums are on by default, and pg_upgrade requires both clusters to agree on the setting. Almost every cluster created before 18 has checksums off, so the 18 cluster you just initialised does not match the one you are leaving. A second, unrelated failure comes from the new collation rule for primary and foreign keys, which breaks pg_upgrade during the schema restore.

Fix. Create the new cluster with the old cluster's checksum setting, or enable checksums on the old cluster first and accept a longer window.

bash
# the common case: old cluster has checksums off initdb -D /var/lib/postgresql/18/main --no-data-checksums # the better long-term case: checksums on in the old cluster, then upgrade pg_ctl -D /var/lib/postgresql/17/main stop pg_checksums --enable -D /var/lib/postgresql/17/main pg_ctl -D /var/lib/postgresql/17/main start
bash
/usr/lib/postgresql/18/bin/pg_upgrade \ --old-bindir=/usr/lib/postgresql/17/bin \ --new-bindir=/usr/lib/postgresql/18/bin \ --old-datadir=/var/lib/postgresql/17/main \ --new-datadir=/var/lib/postgresql/18/main \ --check

Core Mechanics, Signatures & Reproducible Setup

pg_upgrade links or copies relation files between two clusters; it does not rewrite pages. Checksums live in each page header, so a cluster with checksums and one without cannot share files: either every page has a checksum or none does. That is why the setting must match exactly, and why the error appears at the consistency-check stage before any data moves.

Find out what the old cluster has before you plan anything:

bash
psql -Atc "show data_checksums" pg_controldata /var/lib/postgresql/17/main | grep -i checksum

pg_checksums --enable rewrites every block in place, so it takes time proportional to database size and needs a clean shutdown. On a 500 GB cluster that is hours, not minutes. Deciding which path to take during the maintenance window is how windows get blown.

The full sequence that works, with the check run in the same mode as the real upgrade:

bash
/usr/lib/postgresql/18/bin/pg_upgrade \ --old-bindir=/usr/lib/postgresql/17/bin \ --new-bindir=/usr/lib/postgresql/18/bin \ --old-datadir=/var/lib/postgresql/17/main \ --new-datadir=/var/lib/postgresql/18/main \ --old-port=5432 \ --link --jobs=4 --check

--check is safe against a running old server as long as you pass its real port; the default port for both sides is 50432 and two clusters cannot share one port.

Key Comparison Table

PathCommandDowntimeChecksums after upgrade
Accept old settinginitdb --no-data-checksumscopy or link time onlyoff, revisit later
Enable on old clusterpg_checksums --enable then upgradehours on large dataon
New cluster, restore by dumppg_dumpall then psqlfull restoreon
New cluster, logical replicationpublish and subscribenear zeroon
Postgres 18 changeWhat it affectsAction
initdb enables checksumspg_upgrade consistency checkmatch the old setting
VACUUM and ANALYZE include inheritance childrenpartitioned tablesuse ONLY for old behaviour
MD5 password auth deprecatedCREATE ROLE, ALTER ROLEmove to SCRAM
RESTRICT FK violation is 23001error handling in applicationsupdate code that catches 23503
Generated columns are virtual by defaulthand-written DDLwrite STORED explicitly
PK/FK need deterministic collationsschema restorefix collation before upgrading
Optimizer statistics are preservedplanning after upgraderun vacuumdb --analyze-only after

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. Reading the failure from the right log

pg_upgrade prints one screen and writes the detail to four log files in the current directory: pg_upgrade_server.log, pg_upgrade_dump_*.log, pg_upgrade_output.d, and the load logs. Checsum mismatches show up in pg_upgrade_server.log as a startup failure, because the new cluster is started with the old cluster's data_checksums value and refuses the mismatch:

bash
tail -50 pg_upgrade_server.log grep -i checksum pg_upgrade_server.log

If the message is about a library instead, the extension's build for 18 is missing; install the package rather than trying to create the extension in the new cluster.

2. Collation failures during the schema restore

PostgreSQL 18 requires primary and foreign keys to use deterministic collations, or the same nondeterministic collation on both sides. A cluster that defaults to ICU with a nondeterministic collation on an email column now fails the restore:

-- find keys built on a nondeterministic collation SELECT c.conname, t.relname, a.attname, col.collname, col.collisdeterministic FROM pg_constraint c JOIN pg_class t ON t.oid = c.conrelid JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY (c.conkey) JOIN pg_collation col ON col.oid = a.attcollation WHERE c.contype IN ('p', 'f') AND col.collisdeterministic = false;

Fix the schema on the old cluster and rerun the check. This is a schema change you want to make deliberately, with the application tested against it, not discovered at 02:00.

3. Generated columns that changed type silently

Before 18, GENERATED ALWAYS AS (expr) without STORED was a syntax error. In 18 it is accepted and creates a virtual column, which cannot be indexed the way a stored one can and which some ORMs cannot represent. Search migrations for the pattern:

bash
grep -rn "GENERATED ALWAYS AS" --include=*.sql --include=*.py .

Add STORED where the column is used in an index or read by an ORM mapping that expects a real column. A virtual column is a good default for a computed value that is only selected; it is the wrong default for anything you filter on.

4. Statistics, plans and the week after

pg_upgrade now carries optimizer statistics across, which removes most of the "everything is slow on Monday" effect. Extended statistics are not carried, so rebuild them and fill anything missing:

bash
vacuumdb --all --analyze-in-stages --missing-stats-only --jobs=4 vacuumdb --all --analyze-only --jobs=4

--missing-stats-only is new in 18, requires a superuser, and only works with the analyze options. If the cluster defaults to ICU or the builtin provider, reindex full-text search and pg_trgm indexes after the upgrade, because 18 reads configuration files with the cluster's default collation provider instead of always using libc.

5. Application changes that follow the server

Two server-side changes leak into application code. First, RESTRICT foreign key violations now report SQLSTATE 23001 instead of 23503, so handlers that catch 23503 and print "referenced row missing" will fall through. Second, COPY FROM no longer treats \. as end-of-file in CSV mode, which breaks scripts that end a CSV stream with a backslash-dot line:

python
# before: relied on the \. terminator cursor.copy_expert("COPY t FROM STDIN WITH CSV", buf_with_terminator) # after: pass a real EOF, the delimiter is plain data now cursor.copy_expert("COPY t FROM STDIN WITH CSV", io.StringIO(csv_text))

--link is fast and leaves the old cluster unusable once the new one has written. --copy is safe and slow. --swap is new in 18: it swaps directories instead of copying or linking files, which is the fastest option and the least reversible, since a rollback now means swapping back before the new cluster has accepted writes. On a managed service you usually do not get the choice, but on your own VM it decides whether your rollback plan is realistic.

bash
# dry run first, always pg_upgrade ... --swap --check

Common Compilation/Runtime Mistakes & Version Gotchas

  • Running initdb with defaults and then retrying. Every retry fails the same check; delete the new data directory before re-initialising, or you will debug a half-written cluster.
  • Enabling checksums during the upgrade window. Measure pg_checksums --enable on a copy beforehand; it is the longest step and it does not shrink.
  • Deleting log files before reading them. They live in the directory you ran pg_upgrade from; run it from an empty, dedicated directory.
  • Forcing --jobs high on a small disk. Parallel checks and dumps multiply I/O; a low-memory container swaps and looks like a hang.
  • Assuming extensions were created. pg_upgrade prints scripts to run for extension updates; the tables those scripts touch are unsafe to use until they finish.
  • Skipping --check because the first run looked fine. The check is the only stage that tests the real mode and the real ports.
  • Treating a collation failure as a Postgres bug. 18 enforces a rule the schema was already violating; the fix is in the schema.

Inspect the row counts you expect after the upgrade with the SQL formatter, and compare a plan before and after in the diff tool when a query changes shape later.

Reference: PostgreSQL 18 release notes and pg_upgrade documentation.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.