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.

Introduction & Quick Summary
Symptom. pg_upgrade stops before touching a single table:
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 settingsOr 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.
# 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/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 \
--checkCore 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:
psql -Atc "show data_checksums"
pg_controldata /var/lib/postgresql/17/main | grep -i checksumpg_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:
/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
| Path | Command | Downtime | Checksums after upgrade |
|---|---|---|---|
| Accept old setting | initdb --no-data-checksums | copy or link time only | off, revisit later |
| Enable on old cluster | pg_checksums --enable then upgrade | hours on large data | on |
| New cluster, restore by dump | pg_dumpall then psql | full restore | on |
| New cluster, logical replication | publish and subscribe | near zero | on |
| Postgres 18 change | What it affects | Action |
|---|---|---|
initdb enables checksums | pg_upgrade consistency check | match the old setting |
VACUUM and ANALYZE include inheritance children | partitioned tables | use ONLY for old behaviour |
| MD5 password auth deprecated | CREATE ROLE, ALTER ROLE | move to SCRAM |
RESTRICT FK violation is 23001 | error handling in applications | update code that catches 23503 |
| Generated columns are virtual by default | hand-written DDL | write STORED explicitly |
| PK/FK need deterministic collations | schema restore | fix collation before upgrading |
| Optimizer statistics are preserved | planning after upgrade | run 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:
tail -50 pg_upgrade_server.log
grep -i checksum pg_upgrade_server.logIf 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:
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:
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:
# 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))6. Picking between --swap, --link and --copy
--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.
# dry run first, always
pg_upgrade ... --swap --checkCommon Compilation/Runtime Mistakes & Version Gotchas
- Running
initdbwith 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 --enableon 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_upgradefrom; run it from an empty, dedicated directory. - Forcing
--jobshigh 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_upgradeprints scripts to run for extension updates; the tables those scripts touch are unsafe to use until they finish. - Skipping
--checkbecause 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 documentationThe SQL documentation that defines the behaviour this guide explains.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSupabase: new row violates row-level security policy (42501)
RLS is default-deny per command. Add the insert policy, default the owner column to auth.uid(), and give the server client a real session.
Postgres: operator does not exist: uuid = text
Postgres matches operators by argument types. Cast the parameter, not the column, and keep the index on the identifier you compare.
Postgres: relation "users" does not exist (42P01)
Name resolution walks search_path in the current database only. Find the real schema, then qualify or fix the path instead of guessing.
Postgres: could not serialize access due to concurrent update
At REPEATABLE READ and SERIALIZABLE a conflicting write aborts with SQLSTATE 40001. Retry the whole transaction with backoff, then remove the conflict instead of retrying it.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.