How to Map PostgreSQL Extension Dependencies Across Major Versions

This page shows exactly how to build a truthful dependency map for every installed extension before a PostgreSQL major-version jump, so pg_upgrade never aborts on an ABI break, a missing upgrade script, or a shared_preload_libraries mismatch you did not see coming.

Context & When This Applies

Reach for this technique whenever you are moving a database cluster across a major version boundary — PostgreSQL 13→14, 14→16, or any jump that swaps the .so binaries and the share directory underneath a live catalog. It applies to every supported release from 12 onward (the pg_depend, pg_shdepend, and pg_extension_update_paths() surfaces used here are stable across that range) and to both self-hosted clusters and managed platforms where you can run catalog queries. The mapping becomes load-bearing in three concrete situations: a pg_upgrade --check that must pass on the first attempt inside a fixed maintenance window; a fleet audit that has to prove every extension has a binary and an upgrade path on the target major version before anyone touches production; and a rebuild-from-source decision where you need to know precisely which extensions carry ABI-sensitive C code.

This is the cross-version extension of ordinary extension registry mapping: instead of reconciling one server against its own disk, you are reconciling the source cluster’s runtime catalog against the target major version’s share and library directories. If you only need to compare filesystem candidates against installed rows on a single server, start with pg_available_extensions vs installed extensions first, then return here once you cross a binary boundary.

Concept: How PostgreSQL Records Cross-Version Dependencies

PostgreSQL does not track extension dependencies in an application-level manifest. It records them in the catalog, at the object level, in two places. pg_depend holds object-to-object references inside a single database; pg_shdepend holds shared, cluster-wide dependencies for objects like roles and shared extensions (pg_stat_statements, pgaudit) that live outside any one database. A missing upgrade script or an orphaned dependent object in either catalog is what causes pg_upgrade to halt during its --check phase, long before any data is copied.

Every pg_depend row carries a deptype code that dictates upgrade ordering:

  • deptype = 'e' — an extension-to-extension dependency. These must be upgraded in topological order; a child cannot be updated before the parent it requires.
  • deptype = 'i' (internal) — points at core system functions. If those reference a removed or renamed C API on the target version, the extension fails to load after promotion.
  • deptype = 'p' (pin) — objects that cannot be dropped, usually tightly coupled core components.

Layered on top of the catalog are two filesystem facts that a major-version jump changes underneath you: the .control metadata in pg_config --sharedir/extension/ and the compiled .so binaries in pg_config --pkglibdir. When the target release ships new binaries, an ABI break — a changed fmgr interface, Datum alignment, or MemoryContext API — turns a previously loadable extension into a hard could not load library failure. Mapping dependencies across versions therefore means checking three surfaces at once: the catalog dependency graph, the presence of a target-version binary, and the existence of a valid upgrade path between the installed version and the one the target ships.

Mapping extension dependencies across a major-version boundary On the left, the source PostgreSQL 14 cluster holds its runtime catalog: a pg_extension pill of installed rows and exact versions above a pg_depend / pg_shdepend box whose three rows are classified by deptype — 'e' extension dependencies that must be upgraded in requires-first topological order, 'i' internal dependencies on ABI-sensitive core C APIs, and 'p' pin dependencies on non-droppable core coupling. Three arrows carry these into a central band of validation gates: dependency order, binary present, and upgrade path exists. Each passing gate reconciles the catalog against the target PostgreSQL 16 on-disk state on the right: a share directory holding .control files and extension--from--to.sql upgrade scripts, and a library directory holding the .so binaries whose fmgr and Datum ABI must match the target major version. A footer states that when all three gates are green, pg_upgrade --check passes on the first attempt. Source cluster · PG 14 runtime catalog Cross-version validation three gates must pass Target major version · PG 16 share + lib on disk pg_extension installed rows · exact versions pg_depend · pg_shdepend deptype 'e' — extension upgrade in requires-first order deptype 'i' — internal core C API · ABI-sensitive deptype 'p' — pin non-droppable core coupling dependency order topological · requires-first binary present .so in target pkglibdir upgrade path exists chainable update scripts share dir pg_config --sharedir .control + default_version extension--from--to.sql library dir pg_config --pkglibdir .so binaries · fmgr / Datum ABI must match target major All three gates green → pg_upgrade --check passes on the first attempt
The source catalog crosses a binary boundary only when the dependency order, target binary, and upgrade path all reconcile against the target's on-disk state.

Runnable Implementation: Extract the Cross-Version Dependency Map

Start inside the source cluster. The query below joins pg_extension to the available-versions catalog and pg_depend, classifies each dependency by type, and surfaces every installed extension whose version differs from a candidate the server can offer. Run it against each database in turn — pg_extension is database-scoped, so a complete cluster-wide picture requires iterating every database.

SELECT
    e.extname,                                  -- installed extension name
    e.extversion,                               -- exact version recorded at CREATE/ALTER time
    ev.version           AS available_version,  -- candidate the server could install/update to
    d.deptype,                                  -- raw dependency code from pg_depend
    CASE d.deptype
        WHEN 'n' THEN 'normal'
        WHEN 'a' THEN 'auto'
        WHEN 'i' THEN 'internal'   -- references core C APIs; ABI-sensitive across majors
        WHEN 'e' THEN 'extension'  -- upgrade in topological (requires-first) order
        WHEN 'p' THEN 'pin'        -- non-droppable core coupling
    END                  AS dependency_type,
    obj_description(d.objid) AS dependent_object
FROM pg_extension e
JOIN pg_available_extension_versions ev ON e.extname = ev.name
LEFT JOIN pg_depend d ON e.oid = d.refobjid   -- LEFT JOIN keeps extensions with no tracked dependents
WHERE ev.version != e.extversion               -- only rows where a version transition is pending
ORDER BY e.extname, ev.version, d.deptype;

For shared extensions that bypass schema-level tracking, cross-reference pg_shdepend in the same pass; those objects reside in the shared catalog space and are the ones most prone to version skew during a binary upgrade. The official pg_depend catalog documentation covers the edge-case dependency semantics for the rarer deptype combinations.

With the graph in hand, validate that the target major version actually ships a binary and a control file for every installed extension. This shell pre-flight walks the live extension list and checks both the library directory and the share directory of the target release:

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

NEW_SHAREDIR="/usr/share/postgresql/16"   # target major version share dir
NEW_LIBDIR="/usr/lib/postgresql/16/lib"   # target major version library dir

psql -t -A -c "SELECT extname FROM pg_extension;" | while read -r ext; do
    # Some extensions ship a versioned .so (e.g. postgis-3.so); accept either form.
    if [ ! -f "${NEW_LIBDIR}/${ext}.so" ] && [ ! -f "${NEW_LIBDIR}/${ext}.so.0" ]; then
        echo "CRITICAL: ${ext} shared library missing in target libdir ${NEW_LIBDIR}"
        exit 1
    fi

    # Control file lives in the share dir, not the lib dir.
    CONTROL_FILE="${NEW_SHAREDIR}/extension/${ext}.control"
    if [ -f "$CONTROL_FILE" ]; then
        MODULE_PATH=$(grep -E "^module_pathname" "$CONTROL_FILE" | cut -d= -f2 | tr -d "'\" ")
        if [[ "$MODULE_PATH" == *'$libdir'* ]]; then
            echo "INFO: ${ext} uses dynamic \$libdir resolution"
        fi
    else
        echo "WARNING: ${ext}.control missing in target sharedir ${NEW_SHAREDIR}/extension/"
    fi
done

Finally, confirm that a chainable upgrade path exists between each installed version and the one the target ships. pg_extension_update_paths() returns a non-NULL path only when PostgreSQL can chain the required extension--from--to.sql scripts:

-- Flag any extension with no resolvable upgrade path to the target version.
SELECT e.extname AS name,
       e.extversion AS from_ver,
       'TARGET_VERSION' AS to_ver          -- substitute the version the target release ships
FROM pg_extension e
WHERE NOT EXISTS (
    SELECT 1 FROM pg_extension_update_paths(e.extname) up
    WHERE up.source = e.extversion
      AND up.target = 'TARGET_VERSION'
      AND up.path IS NOT NULL
);

When a direct path is absent, chain incremental updates rather than skipping intermediate releases — each ALTER EXTENSION ... UPDATE TO runs only the scripts it can resolve:

ALTER EXTENSION postgis UPDATE TO '3.2.0';
ALTER EXTENSION postgis UPDATE TO '3.3.0';

Never skip an intermediate version unless the maintainer documents a jump-compatible script. For a version-by-version compatibility view across paired extensions such as PostGIS and pgvector, feed these results into a dynamic compatibility matrix so the mapping stays decoupled from the deployment run. Wrap the actual ALTER EXTENSION calls in explicit transactions using the patterns in ALTER EXTENSION automation so a failed jump never leaves the catalog half-migrated.

Expected Output & Verification

The dependency extraction query returns one row per pending version transition, with the dependency_type column already decoded:

 extname |  extversion | available_version | deptype | dependency_type | dependent_object
---------+-------------+-------------------+---------+-----------------+------------------
 postgis | 3.2.0       | 3.3.0             | e       | extension       | (null)
 postgis | 3.2.0       | 3.4.0             | e       | extension       | (null)
 pgvector| 0.5.1       | 0.7.0             | n       | normal          | (null)

Rows with dependency_type = 'extension' tell you the topological order to apply updates in; a parent must be updated before any child that requires it. A clean upgrade-path query returns zero rows — every installed extension can chain to the target. Any row it returns is an extension you must fix (install the matching package or rebuild from source) before promotion.

The authoritative go/no-go signal is a dry-run pg_upgrade --check. Run it after the catalog and binary checks pass, and gate your pipeline on its exit code:

#!/usr/bin/env bash
set -euo pipefail
PG_OLD_BIN="/usr/lib/postgresql/14/bin"
PG_NEW_BIN="/usr/lib/postgresql/16/bin"
DATA_DIR="/var/lib/postgresql/14/main"
NEW_DATA_DIR="/var/lib/postgresql/16/main"

"$PG_NEW_BIN/pg_upgrade" \
  --old-bindir="$PG_OLD_BIN" \
  --new-bindir="$PG_NEW_BIN" \
  --old-datadir="$DATA_DIR" \
  --new-datadir="$NEW_DATA_DIR" \
  --check \
  --jobs="$(nproc)" 2>&1 | tee upgrade_output.log
rc=${PIPESTATUS[0]}

if [ "$rc" -ne 0 ]; then
    echo "FAIL: pg_upgrade --check detected incompatibilities"
    grep -E "extension|library|upgrade" upgrade_output.log | sort -u
    exit 1
fi
echo "PASS: dependency graph validated. Safe to proceed with binary upgrade."

A PASS here means the catalog dependency graph, target binaries, and upgrade paths all reconcile. For extra safety on containerized fleets, mount the target libdir into a throwaway PostgreSQL instance and run CREATE EXTENSION ... WITH VERSION 'target' in a sandbox database — this catches control-file syntax errors without touching production data.

Edge Cases & Gotchas

1. Missing upgrade path between the installed and target versions

If the intermediate migration script is not shipped in the target release’s share directory, the update aborts:

ERROR:  extension "postgis" has no update path from version "3.2.0" to version "3.4.0"

Resolution: chain incremental ALTER EXTENSION ... UPDATE TO calls through each intervening version, or install the extension package built for the target major version so the missing scripts are present. If no chain exists at all, fall back to pg_dump/pg_restore with --extension filtering, or rebuild the extension from source against the target PostgreSQL headers.

2. ABI mismatch — a stale .so compiled against the old headers

An extension binary carried over unchanged, or built against the previous major’s headers, fails to load on the new server:

ERROR:  could not load library "/usr/lib/postgresql/16/lib/pg_similarity.so": undefined symbol: FunctionCallInvoke

Resolution: recompile the extension against the target PostgreSQL source, or install pre-built binaries published for the target major version. This is exactly why the shell pre-flight above checks the target libdir before promotion rather than trusting that a carried-over .so will load.

3. shared_preload_libraries divergence between source and target

Preload mismatches do not surface at upgrade time — they cause a startup panic after promotion, which is far harder to unwind. Diff the two configurations before you promote:

diff <(psql -h old_host -t -A -c "SHOW shared_preload_libraries;") \
     <(psql -h new_host -t -A -c "SHOW shared_preload_libraries;")

Resolution: synchronize shared_preload_libraries in the target postgresql.conf before the first start of the new cluster. An extension that pg_upgrade reports as “not installed in the new cluster” is frequently one that was dropped from the preload list rather than one with a missing binary.

4. A pinned or internal dependency references a removed core API

A deptype = 'i' or deptype = 'p' object that points at a core function removed or renamed in the target release blocks the load with a hardcoded version guard:

FATAL:  extension "timescaledb" requires PostgreSQL version 15 or later

Resolution: update the extension to a build that supports the target release before upgrading; a hardcoded check in the .control file or C code will not yield to any amount of catalog surgery. Where the operation needs elevated rights to swap or reinstall, enforce the model in security boundaries and permissions so the SUPERUSER step is scoped and auditable. If a promotion still fails mid-flight, restore the pre-upgrade state using the snapshot and point-in-time recovery path rather than attempting an in-place repair.