Simulating Async Extension Upgrades with pg_upgrade

Run pg_upgrade --check against a throwaway clone of a production cluster to prove that every installed extension will survive a major-version jump — before any binary swap touches real data.

Up: Async Upgrade Simulation — the deterministic validation gate that decouples extension checks from production traffic; this page is the concrete pg_upgrade mechanics that gate depends on, and it feeds the wider Extension Upgrade Planning & Compatibility Validation process.

Context & When This Applies

Reach for this technique whenever a major PostgreSQL transition — 13→15, 15→17, or any jump that crosses a PG_VERSION_NUM boundary — must be validated against a fleet that carries compiled extensions (PostGIS, timescaledb, pgvector, pg_partman, pgcrypto). A minor-version patch keeps the on-disk catalog format stable and rarely needs this; a major upgrade rewrites the catalog and reloads every shared library, so an extension whose .so is missing, whose ABI drifted, or whose upgrade script is absent from the target share/extension/ directory will abort the real pg_upgrade mid-flight.

Running the check asynchronously — in an ephemeral CI runner rather than during the maintenance window — turns that risk into a pre-flight signal. It applies to PostgreSQL 12 and newer, where pg_upgrade --check reliably reports extension and catalog misalignment without copying or linking any data. Resolve the extension requires graph first with Dependency Tree Analysis; an unmapped prerequisite surfaces here as a confusing shared-object error rather than a clear dependency message.

Async pg_upgrade --check simulation data flow Two independent inputs feed a read-only check. On the left, a cleanly stopped source cluster is rsync-cloned into /tmp/pg_sim_src, a consistent copy with pg_wal excluded. On the right, initdb from the new binaries builds a version-matched skeleton at /tmp/pg_sim_tgt. Both directories are passed as --old-datadir and --new-datadir into a central pg_upgrade --check box that copies nothing, links nothing, and never touches pg_wal. Inside, three probes compare the clusters: pg_extension for the installed set of the old cluster, pg_available_extensions for the update-path SQL on the target, and the target $libdir for a shared object matching the new server's PG_MODULE_MAGIC. The exit code branches the outcome: exit 0 promotes the target package as provably clean, while a non-zero exit blocks promotion and routes the log to triage. Source cluster stopped cleanly / filesystem snapshot initdb — new binaries match encoding · locale · checksums rsync -a --checksum creates skeleton /tmp/pg_sim_src consistent clone · no pg_wal /tmp/pg_sim_tgt version-matched skeleton --old-datadir --new-datadir pg_upgrade --check read-only · copies nothing · links nothing · never touches pg_wal pg_extension installed set old cluster pg_available_extensions update-path .sql on target target $libdir .so matches PG_MODULE_MAGIC exit code exit 0 — promote target package provably clean exit ≠ 0 — block parse log · route to triage

Concept: What --check Actually Validates

pg_upgrade is synchronous by design, but its --check mode is read-only and side-effect-free, which is exactly what makes it safe to run in parallel with CI/CD artifact staging. During a check pass it never touches pg_wal, never links or copies relation files, and never mutates system catalogs. It instead compares the old and new clusters along three axes that matter for extensions:

  • Catalog presence. It reads pg_extension from the old cluster to enumerate what is installed, then confirms each name resolves in the target’s pg_available_extensions view — the target must ship the control file and the correct default_version.
  • Update-path availability. For every extension whose version differs, it checks that a <ext>--<old>--<new>.sql migration script exists in the target share/extension/ directory, the same catalog surface described in understanding pg_available_extensions vs installed extensions.
  • Shared-object resolution. It confirms the target $libdir holds a loadable .so compiled against the new server’s PG_MODULE_MAGIC, so a library built for PostgreSQL 15 is not silently loaded into a 17 backend.

A non-zero exit is a refusal, not a failure of your setup — it is the tool telling you precisely which of those three axes is unsatisfied so you can fix the target image before the window opens. That precision is why the check belongs in front of any live ALTER EXTENSION UPDATE orchestrated through ALTER EXTENSION automation.

Runnable Implementation

The simulation clones the stopped source data directory, initializes a target-version skeleton, and runs the dry-run check. Every step is copy-pasteable; adjust the version paths for your distribution’s layout.

#!/usr/bin/env bash
set -euo pipefail

OLD_BIN=/usr/lib/postgresql/15/bin
NEW_BIN=/usr/lib/postgresql/17/bin
SRC_LIVE=/var/lib/postgresql/15/main
SIM_SRC=/tmp/pg_sim_src
SIM_TGT=/tmp/pg_sim_tgt

cleanup() { rm -rf "$SIM_SRC" "$SIM_TGT"; }
trap cleanup EXIT

# 1. Clone the source data directory. Stop the source cluster cleanly (or
#    snapshot the volume) FIRST — copying a live datadir yields an inconsistent
#    pg_control and pg_upgrade --check will refuse it. Exclude WAL and lock files.
rsync -a --checksum \
  --exclude=pg_wal --exclude=postmaster.pid \
  "$SRC_LIVE/" "$SIM_SRC/"

# 2. Initialize a fresh target-version skeleton. Match the source's encoding,
#    locale and checksum setting or pg_upgrade will reject the pairing.
"$NEW_BIN/initdb" -D "$SIM_TGT" \
  --encoding=UTF8 --locale=C --data-checksums

# 3. Dry-run: validate catalog and shared-object alignment, then halt.
#    --check copies/links nothing, so --link is intentionally omitted.
"$NEW_BIN/pg_upgrade" \
  --old-datadir "$SIM_SRC" \
  --new-datadir "$SIM_TGT" \
  --old-bindir "$OLD_BIN" \
  --new-bindir "$NEW_BIN" \
  --check

echo "SIMULATION CLEAN: extension and catalog alignment verified."

Because --check never writes to either directory, you can fan the simulation across an extension matrix by pointing each combination at its own /tmp/pg_sim_tgt_<n> skeleton and running the checks concurrently. (--jobs parallelizes the real upgrade’s dump/restore phase, not the check pass, so matrix parallelism comes from isolated target directories rather than that flag.) Provision those disposable targets to mirror production the way Test Environment Routing prescribes, and keep a volume snapshot on hand per snapshot & point-in-time recovery so the clone step never has to touch a running primary.

Expected Output & Verification

A clean run exits 0 and prints a checklist of passing probes. The extension-relevant lines look like this:

Performing Consistency Checks
-----------------------------
Checking cluster versions                          ok
Checking for extension updates                     ok
Checking for presence of required libraries        ok
Checking database connection settings              ok

*Clusters are compatible*

Every diagnostic — including the exact failure signature on a non-zero exit — is also written to pg_upgrade_output.d/pg_upgrade.log, which automated triage can parse deterministically. Confirm the check truly passed rather than trusting the console alone:

# Exit status is the gate; the log carries the detail for archival + triage.
echo "exit=$?"
grep -E 'FATAL|ERROR|could not load|no update path' \
  pg_upgrade_output.d/*/pg_upgrade.log || echo "no blocking signatures"

Feed those signatures into error categorization for automated triage so a could not load library line routes to a different remediation queue than a no update path line, and only promote the target package when the exit code is 0 across every matrix combination.

Edge Cases & Gotchas

A missing shared library blocks the check. When the target $libdir lacks the exact .so an installed extension needs, the check reports:

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.
    could not load library "$libdir/postgis-3": ... No such file or directory

Pre-install the extension binaries into the target image and confirm pg_config --pkglibdir on the new cluster points at the directory that actually holds them. Cross-check the resolved paths against the source’s shared_preload_libraries before re-running.

An absent upgrade script fails the update-path probe. If the target ships the extension but not the transition SQL, you get:

extension "pg_stat_statements" has no update path from version 1.9 to 1.10

Deploy the exact pg_stat_statements--1.9--1.10.sql file into the target share/extension/ directory, then verify the path is visible with SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';. Reconciling these version-to-script gaps across a fleet is the job of Compatibility Matrix Synchronization.

An ABI drift surfaces as an undefined symbol. A library compiled against a different minor release loads but cannot resolve a symbol:

could not load library "$libdir/pgcrypto":
  undefined symbol: PQencryptPasswordConn

Rebuild the extension against the target server’s headers (pg_config --includedir-server) and confirm the linker is satisfied before re-simulating:

ldd "$NEW_BIN/../lib/pgcrypto.so" | grep 'not found' \
  && echo 'rebuild against target toolchain' || echo 'linkage ok'

A remaining not found means the extension was linked against an incompatible libpq or OpenSSL — rebuild with the target distribution’s toolchain.

An inconsistent clone is rejected before any extension is examined. Copying a running data directory, or one snapshotted mid-write, produces a dirty pg_control and the check aborts early:

The source cluster was not shut down cleanly.

Stop the source cleanly or take a filesystem-consistent snapshot, re-run rsync with --checksum, and validate the clone with pg_controldata "$SIM_SRC" before retrying. Treat any change to the target extension set as a reviewable artifact through Version Control Branching so the image that passed the simulation is provably the one you promote.

The official PostgreSQL pg_upgrade documentation details additional flags such as --old-options and --new-options for cases where custom postgresql.conf parameters interfere with the dry-run parser.