How to Map PostgreSQL Extension Dependencies Across Major Versions #

Mapping extension dependencies across PostgreSQL major versions requires direct interrogation of system catalogs, control file parsing, and shared library validation. Relying on pg_upgrade or pg_dump without pre-validating dependency graphs causes silent failures during cluster promotion, particularly when ABI boundaries shift or shared_preload_libraries configurations diverge. This guide provides exact diagnostics, catalog queries, and CI/CD-safe validation patterns for platform engineers and SREs managing PostgreSQL Extension Architecture & Lifecycle Fundamentals in production environments.

System Catalog Interrogation & Dependency Graph Extraction #

The foundation of dependency mapping lies in pg_depend, which tracks object-level references across the cluster. Unlike application-level package managers, PostgreSQL extensions register dependencies at the catalog level, meaning a missing upgrade script or orphaned object will block pg_upgrade during its --check phase.

Execute the following query to extract the explicit dependency tree, current versions, and available upgrade paths:

SELECT
    e.extname,
    e.extversion,
    ev.version AS available_version,
    d.deptype,
    CASE d.deptype
        WHEN 'n' THEN 'normal'
        WHEN 'a' THEN 'auto'
        WHEN 'i' THEN 'internal'
        WHEN 'e' THEN 'extension'
        WHEN 'p' THEN 'pin'
    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
WHERE ev.version != e.extversion
ORDER BY e.extname, ev.version, d.deptype;

Key interpretation rules:

• deptype = 'e' indicates an extension-to-extension dependency. These must be upgraded in topological order.
• deptype = 'i' (internal) usually points to core system functions. If these reference removed or renamed C APIs, the extension will fail to load post-upgrade.
• deptype = 'p' (pin) objects cannot be dropped and often indicate tightly coupled core components.

For shared objects like pg_stat_statements or pgaudit, cross-reference against pg_shdepend. These bypass standard schema-level tracking and reside in the shared catalog space, making them prone to version skew during binary upgrades. Consult the official pg_depend catalog documentation for edge-case dependency semantics.

Control File & Shared Library Validation #

PostgreSQL extensions rely on .control definitions and compiled .so binaries. Control files are located in pg_config --sharedir/extension/ and shared objects in pg_config --pkglibdir. During major version jumps, ABI breaks (e.g., changes to fmgr interfaces, Datum alignment, or MemoryContext APIs) or relocated library paths trigger immediate load failures. Validate library presence and control file compatibility before executing pg_upgrade:

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

NEW_SHAREDIR="/usr/share/postgresql/16"   # Adjust for target major version
NEW_LIBDIR="/usr/lib/postgresql/16/lib"   # Adjust for target major version

psql -t -A -c "SELECT extname FROM pg_extension;" | while read -r ext; do
    # Check for the shared object (some extensions use a versioned name like postgis-3.so)
    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

    # Validate control file exists in share dir (not libdir)
    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

This script prevents ERROR: could not load library "..." by verifying binary existence before promotion. When planning version transitions, validate that every extversion has a corresponding upgrade script in the target release’s share directory. This aligns with standard Extension Registry Mapping practices for tracking control file metadata and version transition chains.

Upgrade Path Resolution & Version Transition Chains #

PostgreSQL requires explicit extension--from-version--to-version.sql upgrade scripts. If a direct upgrade path is missing, the extension manager will halt. Query the available transition matrix:

-- Extensions whose installed version differs from any available version
SELECT name, version, superuser, trusted, relocatable, schema, requires
FROM pg_available_extension_versions
WHERE name IN (SELECT extname FROM pg_extension)
ORDER BY name;

-- Check for a missing upgrade path between current and target. The built-in
-- pg_extension_update_paths() returns a non-NULL path only when PostgreSQL can
-- chain the required upgrade scripts.
SELECT e.extname AS name, e.extversion AS from_ver, 'TARGET_VERSION' AS to_ver
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
);

If a direct path is absent, chain incremental upgrades:

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

Never skip intermediate versions unless the extension maintainer explicitly documents a jump-compatible upgrade script. For extensions with broken chains, fall back to pg_dump/pg_restore with --extension filtering, or rebuild from source against the target PostgreSQL headers.

CI/CD-Safe Automation & Pre-Flight Validation #

Production upgrades require deterministic, repeatable validation. Embed pre-flight checks into your deployment pipeline using pg_upgrade --check combined with catalog diffing:

#!/usr/bin/env bash
# Pre-flight validation for CI/CD pipelines
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"

# 1. Run dry-run compatibility check
$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]}

# 2. Capture and parse extension warnings
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."

For containerized environments, mount the target libdir into a temporary PostgreSQL instance and run CREATE EXTENSION ... WITH VERSION 'target' in a sandboxed database. This catches control file syntax errors and missing dependencies without touching production data.

Symptom Identification & Step-by-Step Resolution #

When encountering shared_preload_libraries divergence, synchronize the configuration before promotion:

# Extract and diff preload configurations
diff <(psql -h old_host -t -A -c "SHOW shared_preload_libraries;") \
     <(psql -h new_host -t -A -c "SHOW shared_preload_libraries;")

Mismatched preload libraries cause startup panics, not upgrade-time failures. Always validate runtime configuration alongside catalog state.

Conclusion #

Mapping PostgreSQL extension dependencies across major versions is a deterministic process when anchored to system catalog interrogation, control file validation, and explicit upgrade path verification. By treating extensions as first-class dependency nodes rather than opaque binaries, platform teams eliminate silent promotion failures and maintain upgrade idempotency. Integrate catalog diffing and pg_upgrade --check into CI/CD gates, enforce strict version chaining, and maintain a curated registry of ABI-compatible binaries to ensure zero-downtime major version transitions.