pg_upgrade Fails on Extensions, Not Core: the Pre-Flight Check Teams Skip

The Postgres major upgrade goes beautifully. pg_upgrade runs clean, the checks pass, the new cluster is initialised. You cut over, and the new server refuses to start, or starts and then throws errors the moment an application query touches a spatial column. You upgraded Postgres perfectly. You just forgot that pg_upgrade does not upgrade your extensions.

This is the failure that catches careful teams, because the core upgrade is not where it breaks. PostGIS, its dependents, and any other C-based extension are versioned and compiled separately, and a clean core migration says nothing about whether they are compatible with the new major version. There is no single pre-flight command that flags this for you, so it gets discovered mid-cutover, which is the worst possible time.

What the failure looks like

There are two common shapes. During the upgrade itself, pg_upgrade may abort because a library the old cluster references is not present for the new one:

could not load library "$libdir/postgis-3":
ERROR:  could not access file "$libdir/postgis-3": No such file or directory

Or the upgrade completes and the new cluster falls over on first real use, because the extension's SQL objects exist but the loadable module version behind them is wrong for the new Postgres. Either way the message points at the extension, not at Postgres, which is the clue people miss when they assumed a green core upgrade meant they were done.

Why pg_upgrade leaves extensions behind

An extension is two things bolted together: a set of SQL objects (functions, types, operators) recorded in the catalog with a version number, and one or more compiled shared libraries on disk that those SQL objects call into. pg_upgrade works by copying the old cluster's catalog and data files into the new cluster's format. It faithfully carries over the SQL side of every extension, including the recorded version like postgis 3.1.0.

What it cannot do is compile or move the binary side. Those .so files were built against a specific Postgres major version's ABI and live in that version's $libdir. When the new cluster tries to load postgis-3.1, it looks in the new Postgres's library directory, and if you only installed the new PostgreSQL server package without the matching new PostGIS package, that file is not there. The catalog says the extension exists; the disk says it does not. That mismatch is the whole bug.

PostGIS is the usual culprit for two reasons. It is a large C extension with a tight coupling to the Postgres version, and it drags dependents along: postgis_topology, postgis_raster, postgis_tiger_geocoder, address_standardizer. Each of those is its own extension with its own version, and each has to line up.

Step 1: Inventory every extension and its version

On the old cluster, in every database (extensions are per-database, so check them all):

SELECT e.extname, e.extversion
FROM pg_extension e
ORDER BY 1;
-- or from psql: \dx

Write down the exact versions. This list is your compatibility checklist for the new server.

Step 2: Update extensions on the OLD cluster first

Move each extension to the newest version available for your current Postgres before upgrading. This reduces the version gap you are jumping and avoids upgrading Postgres and the extension in the same leap.

ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_raster UPDATE;

Check what the new default becomes:

SELECT postgis_full_version();

Step 3: Install the matching extension binaries for the NEW major version

Before you run pg_upgrade, install the extension packages built for the target Postgres. On Debian/Ubuntu with the PGDG repository:

# target Postgres 17 with PostGIS 3
sudo apt install postgresql-17 postgresql-17-postgis-3 \
     postgresql-17-postgis-3-scripts

The version of PostGIS you install for the new cluster must be equal to or newer than the version recorded in the old cluster's catalog. Installing an older PostGIS on the new side leaves you unable to load the version the catalog expects. Confirm the library is present:

ls /usr/lib/postgresql/17/lib/ | grep postgis

Step 4: Run pg_upgrade with the check flag first

/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check

The --check run does not modify anything and will surface missing loadable libraries before you commit. Only drop --check once it passes cleanly. See the official pg_upgrade documentation for the full flag reference.

Verification: prove the extensions load on the new cluster

Start the new cluster, then in each database:

-- align the SQL version with the newly installed binary
ALTER EXTENSION postgis UPDATE;

SELECT postgis_full_version();
SELECT extname, extversion FROM pg_extension ORDER BY 1;

A clean postgis_full_version() that names the new Postgres and the expected PostGIS release, with no could not access file error, is your proof. Run one real spatial query, for example SELECT ST_AsText(ST_MakePoint(1,2));, to confirm the binary side actually answers.

What people get wrong

Trusting a green core upgrade. pg_upgrade succeeding tells you the data and catalog converted. It says nothing about whether the loadable libraries exist. Teams read the success message and schedule the cutover, then discover the gap when the app hits a spatial function. The check is not automatic; you have to inventory and match versions yourself.

Installing the new PostgreSQL server package alone. apt install postgresql-17 gives you the core. It does not pull in PostGIS or any other extension. Every extension is a separate package you install deliberately for the new major version.

Upgrading Postgres and PostGIS in a single jump. Update the extension on the old cluster first, then move Postgres. Doing both at once multiplies the ways it can fail and makes the failure harder to attribute.

When it is still broken

1. The new cluster will not start and the log names a library. Read could not access file "$libdir/..." literally; install the exact extension package for the new version that provides it, then start again.
2. A dependent extension is the blocker. postgis_tiger_geocoder and address_standardizer are frequently forgotten because nobody remembers installing them. Your \dx inventory from Step 1 is the authority; install a package for every row.
3. Rehearse against a clone, not production. Restore a recent backup or spin up a replica, run the entire upgrade there, and time it. This is the only way to catch an extension gap safely and to get realistic downtime numbers. If your upgrade is being driven by running low on transaction IDs rather than a routine bump, read my Postgres transaction ID wraparound runbook first, because that changes the urgency and the sequence.
4. Keep the old cluster until you have verified. Do not delete /var/lib/postgresql/14 or run the generated delete_old_cluster.sh until every extension loads and real queries pass on the new server. It is your rollback.

Extension drift is one of a family of upgrade surprises that only show up under real load. If you run Postgres in containers, the storage and connection quirks in could not resize shared memory segment are worth a look before you cut over there too.