pg_upgrade Said "ok" 37 Times. 98 Indexes Were Broken Anyway.

A 79 GB cluster. PostgreSQL 10.4 to 18.6 - eight major versions in one jump. pg_upgrade printed 37 ok lines and one notice, exited 0, and the whole run finished in 3 minutes 33 seconds.

Then I pointed amcheck at the 1,990 collation-sensitive B-tree indexes, and 98 of them failed its invariant checks, across 16 databases.

Cause first, because it matters: everything points to pg_upgrade not having created that damage. It carries the old cluster’s data files forward - that is the whole mechanism behind --copy, --clone and --link - so it moved those indexes across exactly as it found them. Both servers used the same macOS C library and locale, and every failure was an ordering failure on a text key. I didn’t run amcheck on the old cluster, so that is a strong inference, not a measurement. Most likely, the upgrade was simply the first time anyone looked.

About this case study

This is a write-up of a PostgreSQL major-version migration, from the audit beforehand through to deleting the old cluster. The cluster ran on my own Mac: a long-lived local development cluster holding dozens of application databases, with no production databases on it. By every signal the tooling produces, it succeeded: the pre-flight checks passed, the migration exited cleanly, and the applications connected to the new server and worked.

Independent validation then found a class of problem pg_upgrade was never designed to detect, because it falls outside what the tool claims to do. Tracing why it had gone unnoticed for years led somewhere I didn’t expect: two blind spots stacked. PostgreSQL 10 never recorded a version for the database’s default collation on any platform, and on macOS even PostgreSQL 18 records nothing for libc collations - so the past drift went unreported, and future drift will too.

So the interesting engineering wasn’t the migration command. It was separating what the tooling guaranteed from what still had to be proved - and finding out which of my own assumptions survived contact with evidence. Several didn’t.

Why upgrade

Two concrete reasons.

PostgreSQL 10 reached end of support on 10 November 2022 (see the PostgreSQL versioning policy), and this cluster was running a minor release from 10 May 2018 (the 10.4 release notes). The binaries were also x86_64 running under translation on an Apple Silicon Mac, while a native arm64 build of a current version existed.

I ran no benchmarks, so I’m claiming no performance improvement. Plausible isn’t measured.

The audit, and the question most guides skip

Before touching anything I spent time only looking: server version and build architecture; the data directory as the server reports it, not as I remembered it; databases and sizes; extensions per database (they’re per-database objects, so a cluster-level glance misleads); roles and globals; prepared transactions (which block pg_upgrade) and replication slots (which it won’t carry over from a pre-17 cluster); free disk space; the native client drivers that would have to connect; and the collation provider and locale - the line that turned out to matter most.

Plus one question that shaped every later decision: what actually starts this server?

Here, a GUI application bundle managed it - binaries inside the app, data directory under the user’s Library folder, a login item holding the auto-start flag. That’s ordinary for a developer machine, and it means “where are the binaries”, “who owns the postmaster” and “what starts it at boot” all have installation-specific answers.

Every upgrade guide tells you how to install PostgreSQL. The procedure you need depends on how yours is operated - package manager, Homebrew, GUI bundle, container, managed service. Those four questions have four different sets of answers, and pg_upgrade needs three of them as arguments.

A backup you haven’t restored is a hypothesis

Two kinds, to an external drive:

# Physical: byte-level copy of the cluster
pg_basebackup -h localhost -U postgres -D /path/to/backup/basebackup -X stream -P

# Logical: per-database dumps plus cluster-wide objects
pg_dumpall -h localhost -U postgres --globals-only -f /path/to/backup/logical/globals.sql
pg_dump    -h localhost -U postgres -Fc -d "$db" -f "/path/to/backup/logical/$db.dump"

Both, deliberately: the physical copy restores the cluster as it was; the logical dumps are version-portable and can be loaded into a newer server. Then a manifest, so “intact” becomes testable:

find . -type f -print0 | xargs -0 shasum -a 256 > manifests/SHA256SUMS
shasum -a 256 -c manifests/SHA256SUMS

89 GB total; 37,242 files in the base backup; 39,747 in the manifest. And then I restored both - the dump into a scratch database, the physical backup as a separate instance on a spare port - because the alternative is a rollback plan made of file sizes.

One correction I’ll own. I first ran pg_basebackup with --no-slot, reasoning it was tidier not to leave a replication slot on a live cluster. The pg_basebackup documentation disagrees: with -X stream the slot created by default is temporary and cleans itself up, and “using a replication slot is almost always preferred, because it prevents needed WAL from being removed by the server during the backup.” On an idle workstation it’s academic; on a busy server --no-slot trades a transient slot for a chance the backup fails partway. Don’t copy that flag from me.

--copy, --clone, --link

Mode	Speed	Extra space	Old cluster still usable
--copy (default)	Slowest	~one full extra copy	Yes
--clone	Near-instant where supported	Very little initially	Yes
--link	Near-instant	Very little	No

Per the pg_upgrade documentation, --link is the one that forfeits rollback - “you will not be able to access your old cluster once you start the new cluster” - while --clone is supported “on Linux (kernel 4.5 or later) with Btrfs and XFS… and on macOS with APFS” and gives “the speed advantages of --link while leaving the old cluster untouched.”

So --clone was available and would not have endangered rollback. I chose --copy for conservative operational simplicity: the default mode, the fewest filesystem-specific behaviours in play, and space accounting I could predict to the gigabyte. To be explicit, since this is easy to misread: that was not a durability argument against --clone. Reflinked files are logically independent, and the documentation is clear that the old cluster stays usable.

The unavoidable consequence: 79 GB became ~158 GB during the upgrade. One related trap, since it cost me real time: on APFS (and other copy-on-write filesystems), deleting files frees nothing while snapshots still reference those blocks, and reclamation after you clear them is asynchronous. Pre-upgrade cleanup appeared to free zero bytes; so did deleting the 79 GB old cluster at the end. If your “I just deleted 80 GB” doesn’t show up, check snapshots before hunting for a culprit.

Pre-flight: two blockers, one test-rig problem, one silent decision

pg_upgrade --check is free and non-destructive. Run it until it’s clean.

The install user. database user "…" is not the install user. The cluster had been created by the role postgres; my shell user was the account name. The tool requires you to connect as the role that owns the cluster, and that the new cluster contain only that user. Fix: initdb -U postgres, then pg_upgrade -U postgres.

If you are upgrading to PostgreSQL 18, read this one

18 changed initdb to enable data checksums by default, and “pg_upgrade requires matching cluster checksum settings” - see the PostgreSQL 18 release notes and initdb.

An older cluster without checksums plus a default-initialised 18 cluster is a mismatch, and the upgrade stops: “old cluster does not use data checksums but the new one does.” So:

/path/to/pg18/bin/initdb -D /path/to/new-cluster -U postgres \
  --no-data-checksums --encoding=UTF8 --locale=en_US.UTF-8

Nothing to do with your data, and it will stop a lot of upgrades from pre-18 clusters.

A socket path too long for the OS. A test instance failed because its Unix socket path exceeded sockaddr_un.sun_path - 104 bytes on this platform per the system header, 108 on Linux. A data directory under a long Library path exhausts that fast. The fix is to point test instances somewhere short (-c unix_socket_directories=/tmp/pgtest), not to move your data directory.

And char signedness, which is a decision that produces no error either way. PostgreSQL 18 added --set-char-signedness because pre-18 clusters don’t record it: upgrading from 17 or earlier, pg_upgrade “adopts the char signedness of the platform on which it was built”, and the docs say to set it explicitly when “you have already migrated the cluster to a platform with different char signedness (for example, from an x86-based system to an ARM-based system).”

I assumed x86_64 → ARM meant signed → unsigned, and passed signed accordingly. Then I checked:

printf '#include <limits.h>\n#include <stdio.h>\nint main(){printf("%d\n",CHAR_MIN);}\n' > /tmp/char_check.c \
  && cc -o /tmp/char_check /tmp/char_check.c && /tmp/char_check

-128 is signed, 0 is unsigned. On this Apple Silicon machine with the system compiler: -128. Plain char is signed on macOS arm64 exactly as on macOS x86_64, so my flag confirmed what pg_upgrade would have chosen anyway. It fixed nothing.

I’m not going to tell you what your platforms do - I couldn’t establish a general ARM rule from primary documentation, and guessing is how folklore starts. The transferable part is the hazard’s shape: a signedness mismatch raises no error. pg_upgrade records a value and moves on; wrong values are misread later, silently. Test both platforms with those four lines and pass the flag knowingly. And note the docs’ other branch: if you plan to change platforms after upgrading, don’t use the option - upgrade first on the original platform.

The migration, which was the easy part

/path/to/pg10/bin/pg_ctl -D /path/to/old-cluster -m fast stop

/path/to/pg18/bin/pg_upgrade \
  -b /path/to/pg10/bin  -B /path/to/pg18/bin \
  -d /path/to/old-cluster -D /path/to/new-cluster \
  -U postgres --set-char-signedness=signed --copy

Exit 0, 3 minutes 33 seconds, 37 ok lines, one notice (extensions want updating - more on that below).

Then the most useful thing I did all day, and it’s a refusal: I did not give the new server port 5432.

/path/to/pg18/bin/pg_ctl -D /path/to/new-cluster \
  -o "-p 50433 -c listen_addresses='' -c unix_socket_directories=/short/dir \
      -c default_transaction_read_only=on -c autovacuum=off" \
  -l first_start.log start

A private port, no TCP listener at all (only a Unix socket in a short private directory), read-only by default and autovacuum off: nothing connects by accident, no application picks it up because its config says localhost:5432, and nothing writes to a cluster I haven’t validated. pg_upgrade finishing is not a reason to cut over; it’s a reason to start testing.

Validation: what amcheck found

A B-tree index is sorted, and the order was fixed when the index was built, using whatever comparison rules applied at that moment. For text, with the libc provider, those rules come from the operating system’s C library - and the amcheck documentation names the consequence when they move: “Structural inconsistencies caused by incorrect operator class implementations. This includes issues caused by the comparison rules of operating system collations changing… which implies that operating system collation rules must never change.”

An index sorted the old way, read by an engine comparing the new way, is internally inconsistent with the rules in force - and typically nothing errors. Sometimes you do get a visible failure: a unique index whose ordering is wrong can reject a legitimate insert, or refuse to rebuild because it has accumulated duplicates. But the documented and dangerous common case is silence. What PostgreSQL documents as the consequence is wrong answers rather than error messages: a lookup missing a row that exists, a range scan skipping entries, an index-only scan and a sequential scan disagreeing about the same data, a unique index no longer reliably preventing duplicates.

What I actually observed was none of that. I observed failed invariant checks. I did not construct a query that returned a demonstrably wrong result on this data, and I’m not going to imply otherwise.

I also did not reconstruct the specific historical event that damaged these particular indexes. What I established: the failures were in files pg_upgrade copied forward unchanged, and the mechanism above is documented and consistent with a cluster built in 2018 on an operating system that has moved several major versions since. I didn’t baseline the old cluster before migrating, which is precisely the mistake I’d fix next time.

Two passes, increasing in strength. amcheck is a contrib extension; I installed it temporarily in the 43 databases under validation and dropped it afterwards.

CREATE EXTENSION amcheck;

-- Pass 1: structure; unique indexes also checked for duplicates.
SELECT bt_index_check(index => c.oid, checkunique => <is unique>) FROM ...;

-- Pass 2: survivors, checked harder (parent/child relationships).
SELECT bt_index_parent_check(index => c.oid,
                             rootdescend => false,
                             checkunique => <is unique>) FROM ...;

Two passes because the locks differ, and that’s an operational decision, not a detail. bt_index_check takes an AccessShareLock - what a plain SELECT takes, safe alongside live traffic. bt_index_parent_check takes a ShareLock, blocking INSERT, UPDATE and DELETE on the table while it runs. On a workstation, irrelevant. On a shared system, that’s the difference between a background sweep and an incident. Run the cheap check broadly; spend the expensive one deliberately.

I wanted rootdescend in the second pass, and PostgreSQL refused: “Only B-Tree version 4 indexes support rootdescend verification.” Indexes built before PostgreSQL 12 keep their old on-disk format through pg_upgrade, so rootdescend only became available after the REINDEX, and the re-check used it. I also did not run heapallindexed, which adds a full heap scan per index and detects a different class of damage (index entries missing for rows that exist).

The result: of 1,990 collation-sensitive B-tree indexes, 98 failed amcheck’s invariant checks, across 16 databases, 29 of them unique indexes. Plus 2 GiST indexes, in a seventeenth database, that amcheck cannot verify at all.

All 98 were ordering failures: 88 “item order invariant violated” and 10 “high key invariant violated”. Every one had at least one key column under the database’s libc default collation - 94 varchar, 3 text, 1 citext. The sweep only covered indexes with a collatable key, so that last fact confirms the pattern rather than proving it on its own; together with the ordering-only failure signature, it is what points at collation drift rather than something else.

Coverage has edges worth knowing: as of PostgreSQL 18 there is also gin_index_check(), so GIN indexes can be verified. GiST, SP-GiST and BRIN cannot. For collation-sensitive indexes of those types there’s no verification - only a decision about whether to rebuild them.

The discovery: nothing was ever going to warn

PostgreSQL has a mechanism for exactly this failure mode. It records a collation’s version when the collation or database is created, checks it on use, and warns on divergence - see the Notes in ALTER COLLATION:

WARNING:  collation "xx-x-icu" has version mismatch
DETAIL:  The collation in the database was created using version 1.2.3.4,
         but the operating system provides version 2.3.4.5.
HINT:    Rebuild all objects affected by this collation and run
         ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION...

datcollversion exists to carry it: “recorded when the database is created and then checked when it is used, to detect changes in the collation definition that could lead to data corruption” (pg_database catalogue).

On this cluster, two separate gaps meant that mechanism never had anything to compare.

The first is history. datcollversion only arrived in PostgreSQL 15. Every collatable column here used the database’s default collation, and a cluster created on version 10 never recorded a version for that default collation, on any platform. Whatever drifted in the years before the upgrade could not have produced a warning anywhere.

The second is the platform, and it outlasts the upgrade. It’s in this sentence:

“When using collations provided by libc, version information is recorded on systems using the GNU C library (most Linux systems), FreeBSD and Windows.”

macOS is not on that list. So even on PostgreSQL 18, nothing is recorded for libc collations here.

I checked the live cluster rather than trusting the inference:

SELECT datname, datlocprovider, datcollate, datcollversion FROM pg_database;

SELECT collname, collprovider, collversion, pg_collation_actual_version(oid)
  FROM pg_collation;

datcollversion is NULL for every database. pg_collation_actual_version() returns NULL for the default, C and en_US.UTF-8 libc collations - while the builtin ucs_basic collation does report a version, which proves the machinery works and simply has nothing to work with here.

What follows from that:

• On this configuration there will never be a mismatch warning. Not for the drift that already happened, and not for drift from here on.
• ALTER DATABASE ... REFRESH COLLATION VERSION did not apply. There’s no recorded version to refresh. Not running it was correct, not an oversight. On glibc, FreeBSD, Windows - or with ICU anywhere - you would run it after reindexing, while noting the docs’ caveat that it “does not actually check whether all affected objects have been rebuilt correctly.” It silences a warning; it doesn’t verify your work.
• So on this configuration, amcheck was the practical detection mechanism available to me. Not the only conceivable one - you could compare sort orders across versions, or diff query results against a known-good restore - but the one that is purpose-built, documented for this failure mode, and cheap enough to actually run.

My recommendation for a new long-lived cluster - my opinion, not a PostgreSQL requirement - is to decide the provider deliberately rather than inheriting the default.

Use ICU if you need linguistic sorting. Its version is tracked on every platform, so you get the warning this configuration can’t give you. Be clear-eyed about the costs, though: your server build must include ICU support; ICU locale names differ from libc’s and may end up in your DDL and application code; and an ICU library upgrade is itself a rebuild-and-refresh event - the pg_upgrade documentation names upgrading to binaries linked against a newer ICU as one of the legitimate ways this happens. You’re trading an invisible drift problem for a visible, versioned one.

Use the builtin C.UTF-8 if code-point ordering is acceptable. Its sort order is stable across releases (case mapping still follows the Unicode version), so there’s nothing to track for indexes.

One caveat that saves disappointment: the provider is fixed when a database is created. initdb defaults to libc in 18, and no flag converts an existing database - it means creating new databases and reloading data. For most of us that’s a decision about the next cluster.

Remediation, and the validator that lied

Repair was targeted, not wholesale: a generated plan of exactly 100 REINDEX INDEX statements - the 98 that failed plus the 2 unverifiable GiST indexes, rebuilt on principle since nothing could clear them - run fail-fast:

psql -d "$db" -v ON_ERROR_STOP=on -f "reindex_${db}.sql"

100 of 100 succeeded in 72 seconds. Re-checking afterwards with bt_index_parent_check, now with rootdescend and checkunique (still without heapallindexed): all 98 passed. Note that REINDEX INDEX blocks writes to the table and, because the planner locks every index of a table, nearly all queries on it too. REINDEX INDEX CONCURRENTLY avoids that, but it can’t run inside a transaction block, leaves an invalid index behind if it fails partway, and needs more care for indexes backing constraints - read the REINDEX documentation for your version before using it on anything live. With no users connected, I took the simple form.

I’d recorded each index’s size in the plan, so there’s a before-and-after: those 100 indexes totalled 1,162.9 MiB beforehand and 822.4 MiB when I re-measured - about 29% smaller. Two caveats, because I’d rather hand you a caveated number than a tidy fiction: the second measurement came weeks later with normal write activity in between, and I did not separate the causes. A fresh rebuild removes accumulated bloat; it also writes the current on-disk B-tree format, which has improved substantially since version 10. Both are in that figure. I’m not attributing it to either.

And now the part I’m least proud of and find most useful. My first tally of the amcheck results reported zero errors.

It was wrong - 98 were failing. I caught it by reconciling counts: the number of indexes reported as checked didn’t match the number I knew were in scope, and the gap was the failures. The cause was mundane. When psql runs a script and a statement raises an error, the message is prefixed with the script path and line number; my script grepped for the error text at the start of the line. It matched nothing, every time, with total confidence.

Validation needs validating. If your checker can only report success, it isn’t a checker. Count what you attempted, count what passed, count what failed, and assert that the three reconcile. “No errors found” and “no errors detected by code incapable of detecting errors” produce identical logs.

What the upgrade left for me to finish

The extension script runs itself only if you run it. pg_upgrade’s notice generates update_extensions.sql; mine held 45 ALTER EXTENSION ... UPDATE statements across 24 databases covering eight extensions, and took about a second with ON_ERROR_STOP.

An updated extension can still be inert. pg_stat_statements was current in every database and entirely non-functional, because shared_preload_libraries was empty, so the module never loaded. Its SQL objects exist; the thing that populates them doesn’t run. “Version current” and “working” are separate claims, and the fix needs a config change and a restart.

Statistics: check your reflexes, because the guidance differs in 18. The older rule of thumb was “pg_upgrade discards statistics, so ANALYZE everything.” Per the pg_upgrade documentation, “pg_upgrade will transfer most optimizer statistics”, excluding “those created explicitly with CREATE STATISTICS, custom statistics added by an extension, or statistics collected by the cumulative statistics system.” The documented sequence is two commands:

vacuumdb --all --analyze-in-stages --missing-stats-only   # fill gaps quickly
vacuumdb --all --analyze-only                             # refresh cumulative stats

I ran only vacuumdb --all --analyze-in-stages. That recomputes optimizer statistics progressively, in passes of increasing accuracy, ending with a full ANALYZE of every database - so it covered both concerns, but it redid work pg_upgrade had already preserved, and --missing-stats-only would have filled just the gaps. No harm done, just slower than needed; the documented pair is what I’d run next time.

Your admin queries are part of the compatibility surface. My own inspection scripts failed on the new server with column "daticulocale" does not exist and operator is not unique: unknown || "char". Catalogue columns get renamed and function resolution gets stricter across eight majors. The application can be perfectly portable while your diagnostics are not.

Native drivers care about architecture - but not uniformly. Of five Rails applications, one failed to load its PostgreSQL driver; its installed native extension had been built for x86_64, the previous architecture, which is the likely cause. Reinstalling dependencies fixed it with no lockfile change. The other four needed nothing PostgreSQL-related. My initial assumption that all of them would need rebuilding was wrong, which is the lesson: don’t preemptively rebuild everything, don’t assume you’re fine, and test one app per architecture-sensitive driver before the migration rather than during cutover.

Cutover, briefly, because the ordering is the only interesting part. Disable the old server’s auto-start first, while the new one is still on its private port - free up 5432 before that and you’ve created a race between two servers for the port, and whichever wins, writes land somewhere you didn’t choose. Then move the new server to 5432, re-run the application tests, and give exactly one supervisor responsibility for keeping it alive (here, a user-level launchd agent running the postgres binary directly, with the GUI manager kept closed so it couldn’t adopt a data directory the service manager already owned). Then reboot the machine and verify with a script that checks version, port ownership, parent process, database count and free space. Anything short of a reboot tests your intentions, not your configuration.

Guarantees, facts, and recommendations

Worth separating explicitly, because conflating them is how upgrades go wrong:

What PostgreSQL guarantees. pg_upgrade checks cluster settings and known-incompatible types, relies on on-disk format compatibility, and transfers the files. It does not inspect index contents, does not rebuild indexes, and makes no claim about whether your data sorts the way your indexes think it does. It generates an extension-update script; it doesn’t run it.

What this migration actually did. Verified physical and logical backups, restore-tested both. Iterated --check until clean. --copy in 3 m 33 s. Validated on a private port. Found 98 B-tree indexes that failed amcheck’s invariant checks across 16 databases, plus 2 GiST indexes it couldn’t verify; rebuilt those 100 indexes across 17 databases in 72 seconds; re-verified the 98. Ran 45 extension updates. Rebuilt statistics (more slowly than needed). Tested five applications, rebuilt one driver. Cut over, supervised, rebooted, re-verified the backup, then deleted the old cluster.

What I recommend, labelled as opinion: baseline your indexes before you migrate; decide the collation provider deliberately for new long-lived clusters; run the documented statistics pair; set a rollback expiry date in advance.

Rollback has an expiry date

--copy left the old cluster bootable and I disabled its auto-start rather than deleting it. The plan was written down: unload the new service, stop the new server, re-enable the old auto-start, start the old server.

It was genuinely available - and worth less every hour. Within three days the new cluster’s cumulative statistics counters showed hundreds of thousands of row changes. That counter includes some migration-time activity, so treat it as an order of magnitude rather than a precise count of application writes; the conclusion holds either way, because rolling back would have meant discarding real work. A retained old cluster is a time-limited safety net, and the clock starts when applications connect. Decide the expiry in advance rather than during an incident.

Two further things about that old cluster:

What you kept still has the problem. It holds the same 98 indexes that failed those checks. Any physical restore from pre-upgrade state needs the same validate-and-reindex pass applied to it.

A physical backup is not self-contained. It’s a byte-level copy of a version-10 cluster, and starting it requires compatible PostgreSQL binaries for that major version and architecture. Those binaries were later removed from the machine during unrelated housekeeping - and in that moment opening an 89 GB verified physical backup became a job that starts with reinstalling PostgreSQL 10. The logical dumps stayed directly usable, since they load into a newer server. Keep the matching binaries for as long as you rely on a physical backup, or treat your logical dumps as the real one.

What I’d do differently

Baseline the indexes before migrating. The big one. I found 98 failing indexes afterwards and the evidence says they predate the migration - but “the evidence says” is weaker than “I measured it beforehand.” A pre-migration amcheck run would have made the baseline a fact, separated pre-existing damage from anything the migration touched, and let me reindex on the old cluster at leisure instead of inside a cutover window.

Reconcile counts in validation scripts from line one, not after a result looks suspiciously clean.

Test one native client driver per architecture early. Five minutes that removes a surprise from the worst moment.

Keep disposable clusters somewhere reboot-safe. Mine lived in the system temp directory, and a reboot deleted them.

Run the documented statistics sequence, rather than the version I remembered from older releases.

Write the rollback expiry date down before starting.

Consider --clone where the filesystem supports it, knowing it preserves a usable old cluster. --copy cost 3 m 33 s and 79 GB, which was affordable, but affordable isn’t the same as the best choice.

The checklist

Before you touch anything

• Record version and build architecture, data directory (as the server reports it), databases and sizes, extensions per database, roles, prepared transactions, replication slots, free disk space
• Record the collation provider. If libc on a platform without collation-version tracking (macOS), or if the cluster predates PostgreSQL 15, assume you will get no warning about drift and plan to validate explicitly
• Identify what starts the server, and what will start it afterwards
• Physical backup and logical dumps and globals, with a checksum manifest
• Restore both. An unrestored backup is an assumption
• Confirm compatible binaries exist (and will keep existing) for any physical backup you intend to rely on
• Budget disk: ~2× cluster size for --copy; on CoW filesystems, check snapshots before trusting free-space numbers
• Test one native client driver per architecture-sensitive dependency
• Baseline amcheck on the old cluster - bt_index_check at minimum - and record which invariant failed for anything that fails
• Write the rollback procedure and its expiry date

Migration

• pg_upgrade --check, iterated until clean: install user, checksum settings (18 enables them by default), char signedness decided deliberately for your platforms
• Choose copy mode knowingly: --copy, --clone where supported, --link only if you accept losing the old cluster
• Stop the old server with a defined shutdown mode (-m fast)
• Run the upgrade; keep the log
• Start the new server on a private port: Unix socket only, read-only, autovacuum off
• Run the generated update_extensions.sql with ON_ERROR_STOP
• Check whether any updated extension needs shared_preload_libraries (and a restart) to function
• vacuumdb --all --analyze-in-stages --missing-stats-only, then vacuumdb --all --analyze-only

Validation

• bt_index_check(checkunique => …) broadly - AccessShareLock, safe alongside traffic
• bt_index_parent_check(checkunique => …) on survivors - ShareLock, blocks writes. rootdescend only works on version 4 indexes (built on PostgreSQL 12+); heapallindexed adds a full heap scan per index
• gin_index_check() on GIN (18+); decide explicitly about GiST, SP-GiST, BRIN - they can’t be verified
• Targeted REINDEX INDEX, fail-fast, then re-verify what you rebuilt
• Reconcile counts: attempted = passed + failed
• Application smoke tests, read-only first: PGOPTIONS="-c default_transaction_read_only=on"
• Cutover: disable old auto-start first, then take 5432
• One supervisor owns the postmaster; confirm its parent process
• Reboot, then verify with a script
• Re-verify backup checksums
• Where collation versions are tracked, ALTER DATABASE ... REFRESH COLLATION VERSION after reindexing

Cleanup

• Delete the old cluster only after the backup re-verifies and the rollback window has expired
• Keep old-version binaries as long as you rely on a physical backup
• Remove temporary tooling (amcheck) and confirm nothing depended on it

What this was about

Eight major versions, 79 GB, three and a half minutes, exit code 0. If that were the job, there’d be nothing to write about.

The job was the rest: restoring the backup instead of assuming it, noticing that a default changed between versions, reading a documentation sentence carefully enough to realise the platform could not warn me about a failure mode PostgreSQL otherwise detects - and distrusting my own script when it delivered good news.

pg_upgrade guarantees that your files are compatible with the new binaries. It doesn’t claim the contents are correct, and it has no mechanism to tell you otherwise. Those 98 indexes were almost certainly failing their invariant checks before I started, and they would have gone on failing them, in a state PostgreSQL documents as producing wrong answers rather than errors, if the upgrade hadn’t given me a reason to look.

Moving the bytes is the part with a progress indicator. Establishing what they still mean is the work that no tool will prompt you to do.