This Fixes: pg_upgrade ‘could not load library’ Extension Mismatch

pg_upgrade stops dead with could not load library, names a .so file, and refuses to go further. The cause is almost never corruption. It is that an extension living in your old cluster has no matching binary in the new one — and the fix is to install the missing packages for the target major version, not to touch the old data directory.

This is one of the most common ways a PostgreSQL major upgrade fails on the first attempt, and it is entirely benign once you understand what pg_upgrade is telling you. I have hit it upgrading a PostGIS-heavy cluster from 16 to 17 on Ubuntu, and the resolution is the same every time: read the check output, install the matching contrib and third-party packages for the new version, re-run --check until it is clean, then run the real upgrade. What follows is that sequence, with the specific packages named.

What the error actually means

When you run pg_upgrade (or pg_upgrade --check), one of its early tests is “Checking for presence of required libraries”. For every extension and loadable module registered in the old cluster, it works out which shared library the new server would have to load, and verifies that library exists under the new installation. If any are missing it aborts and writes the full list to a file — loadable_libraries.txt — in the directory you ran the command from.

The message looks like this:

Checking for presence of required libraries                 fatal

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.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Open that file. Each line names a library the new cluster cannot find, such as postgis-3 or pg_stat_statements. That is your shopping list. pg_upgrade is not saying your data is broken; it is saying the new PostgreSQL binaries do not yet ship the code behind extensions your databases depend on. It refuses to proceed because migrating those objects into a cluster that cannot load them would leave you with a broken catalogue.

Inventory the extensions before you touch anything

The loadable_libraries.txt file tells you which libraries are missing, but it is worth building the full picture from the old cluster first, so you install everything in one pass rather than discovering the next gap on the next run. Connect to the old cluster and list every extension in every database, because pg_upgrade checks all of them, not just the ones in the database you happen to be looking at.

-- Run against the OLD cluster. Lists installed extensions per database.
-- psql -p 5432 -d postgres -At -f this.sql   (adjust the old port)
SELECT current_database() AS db,
       extname,
       extversion
FROM   pg_extension
ORDER  BY extname;

Repeat for each database, or loop over them. To catch libraries loaded outside the extension system — anything pinned in shared_preload_libraries, for instance — also check the server configuration:

-- Anything preloaded must exist in the new cluster too.
SHOW shared_preload_libraries;   -- e.g. pg_stat_statements, auto_explain

Between the loadable_libraries.txt list and these two queries you now know exactly what the new cluster is missing. The two that catch people most often are PostGIS, because it is a separately packaged third-party extension, and pg_stat_statements, because it is a contrib module that is easy to assume ships by default.

Install the matching packages for the target version

On Debian and Ubuntu using the PostgreSQL community (PGDG) apt repository, packages are versioned per major release. The binaries the new cluster needs live in packages named for PostgreSQL 17, and they are distinct from whatever satisfied the old cluster. The contrib modules — which include pg_stat_statements, auto_explain, hstore, pg_trgm and friends — ship inside the main server package postgresql-17. PostGIS is separate.

# Refresh, then install the target-version server (which carries contrib)
sudo apt-get update
sudo apt-get install -y postgresql-17

# PostGIS for PostgreSQL 17 is a separate, version-matched package
sudo apt-get install -y postgresql-17-postgis-3

# Sanity check: which packages are actually installed for v17
dpkg -l | grep -E 'postgresql-17|postgis'

A few things worth being precise about. The postgresql-contrib package on PGDG is a transitional metapackage; the modules themselves are already in postgresql-17, so installing the versioned server package is what actually puts pg_stat_statements.so on disk for the new cluster. The PostGIS package name encodes both the server major version and the PostGIS major line — postgresql-17-postgis-3 gives you the PostGIS 3.x series built against PostgreSQL 17. Match the PostGIS major version to what the old cluster ran; if you were on PostGIS 3.4, do not silently jump to a different major line as part of the same upgrade. For any other third-party extension in your inventory — pgvector, timescaledb, and so on — find and install its PostgreSQL 17 build the same way before you go on.

Re-run the check until it is clean

Never run the real upgrade to find out whether you have fixed it. Run --check again. It is read-only, it touches neither cluster’s data, and it will either pass or hand you the next missing library.

# Run as the postgres user, from a writable working directory.
# Adjust bin and data paths to your layout.
/usr/lib/postgresql/17/bin/pg_upgrade 
  --old-bindir=/usr/lib/postgresql/16/bin 
  --new-bindir=/usr/lib/postgresql/17/bin 
  --old-datadir=/var/lib/postgresql/16/main 
  --new-datadir=/var/lib/postgresql/17/main 
  --check

When the library check reports ok, and the rest of the pre-flight is clean, drop the --check flag and run it for real. Treat a failed upgrade as a change failure to be measured and learned from, the same way you would any deployment against your DORA metrics — the point of the check loop is to move the failure into a rehearsal, not into production. Rehearse the whole sequence against a copy first; the same discipline that puts a gate in front of the commit belongs in front of a major-version migration.

Why you never edit the old data directory to force it

There is a tempting shortcut when the check keeps naming an extension you no longer care about: reach into the old cluster and DROP EXTENSION, or worse, hand-edit catalogue entries so the library stops being referenced. Dropping an unused extension cleanly through SQL, on the old cluster, is legitimate — that is the “remove the functions using them” path the error message itself offers. Editing the data directory by hand is not. pg_upgrade relies on the old cluster being a faithful, consistent source; poke at its catalogue files and you can corrupt the migration in ways that surface days later as unreadable tables. The old data directory is your rollback. If the upgrade goes wrong, an untouched old cluster is what you start again from. Install the missing library instead — it is a two-minute apt-get, and it keeps the one artefact you cannot easily rebuild intact.

The whole failure reduces to a version-matching problem: every loadable library the old cluster uses must exist, built for the new major version, before pg_upgrade will proceed. Fix it by adding binaries to the new side, never by mutilating the old one — the same instinct that fixes the state rather than fighting the symptom. Read the file, install the packages, re-check, upgrade.