Extension Upgrade Planning & Compatibility Validation #

Promoting a PostgreSQL extension from one version to the next is not an isolated ALTER EXTENSION UPDATE; it is a gated validation pipeline that intersects schema migration ordering, connection pooler behaviour, replication topology, and SLO-bound maintenance windows. This guide is the anchor reference for building that pipeline across a production fleet: it explains the catalog surfaces that decide whether an upgrade is even reachable, walks every stage from compatibility mapping through dry-run simulation to a pass/fail gate, and shows exactly where Python and CI/CD logic hook in so that a promotion either advances deterministically or blocks and routes to a rollback — never half-applies silently.

If you have not yet modelled extensions as managed infrastructure, start with PostgreSQL Extension Architecture & Lifecycle Fundamentals; this page assumes that on-disk artifact model and focuses on the validation discipline that decides whether a candidate is safe to ship.

The Validation Pipeline at a Glance #

Upgrade planning is a staged pipeline: a candidate only promotes after every gate passes, and any failure routes to triage and rollback rather than a partial write.

Every box is an automation boundary where the pipeline reads state, makes a decision, and records a verifiable result. The compatibility matrix decides whether the version tuple is allowed; dependency resolution decides in what order transitions run; simulation decides what would actually happen against production-sized data; error categorization decides whether a failure is transient or terminal; and threshold tuning decides whether the operation fits the window. The rest of this guide decomposes each boundary and names the deeper reference for it.

Core Concepts: What “Compatible” Actually Means #

Compatibility validation fails when teams treat “the new version installs” as equivalent to “the new version is safe.” Four distinct surfaces must each be validated independently, because a candidate can pass three and still break production on the fourth.

• Update-path reachability. PostgreSQL computes the sequence of migration scripts (extension--A--B.sql) from files present on disk on that specific node. A version is only reachable if an unbroken chain of --from--to-- scripts exists locally. A node that received a partial package upgrade will report extension "x" has no update path from version "A" to version "B" while its neighbours succeed — the single most common source of fleet-wide inconsistency.
• ABI and shared-library compatibility. The compiled .so referenced by module_pathname exposes C symbols that the SQL-level function definitions call. A library built against a different PostgreSQL major, or a mismatched libc/libpq, produces could not find function ... in file errors that surface only at call time, not at install time — which is why static linkage checks (ldd) belong in the pipeline, not the incident review.
• Catalog-state compatibility. An update rewrites rows in pg_proc, pg_class, pg_type, and pg_extension. Some of those rewrites (function signature changes, dropped operators) invalidate dependent objects, cached plans, or prepared statements in live sessions.
• PostgreSQL-version compatibility. Each extension version supports a bounded band of server majors. Promotion has to cross-reference extension version against server version explicitly, which is the job of a maintained compatibility matrix rather than tribal knowledge.

The pipeline’s entire purpose is to convert these four latent, call-time or restart-time failure classes into deterministic, pre-flight decisions.

Architecture & State Model: How the Catalog Exposes Upgrade Candidacy #

Automation that plans upgrades reads three catalog surfaces, and the critical one for planning is the third — the one that enumerates reachable versions and their per-version constraints.

Three facts shape every planning query:

1. pg_extension is per-database, not per-cluster. A candidate must be planned and validated per database, iterating across the whole cluster rather than assuming a single global state.
2. Availability and installed state are decoupled. Dropping a new .so and control file makes a version available but does not apply it; planning must diff “reachable default” against “installed” to know whether work is pending.
3. The reachable-version graph is computed from disk. Because pg_available_extension_versions reflects the scripts present on this node, reconciling those artifacts across every node before planning is a prerequisite — a discipline developed as Extension Registry Mapping.

A single query snapshots a database’s upgrade candidacy for the first gate — installed version, reachable default, and whether the target hop even exists:

SELECT
    e.extname,
    e.extversion                         AS installed,
    ae.default_version                   AS reachable_default,
    (e.extversion IS DISTINCT FROM ae.default_version) AS upgrade_pending,
    n.nspname                            AS schema
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_available_extensions ae ON ae.name = e.extname
ORDER BY upgrade_pending DESC, e.extname;

To confirm the path itself is reachable rather than just the endpoint, planning also inspects the version graph directly:

-- Does an update path from the installed version to the target exist on THIS node?
SELECT version, superuser, requires
FROM pg_available_extension_versions
WHERE name = 'postgis'
ORDER BY version;

If the target version is absent from that result on any node, the candidate is unreachable there and the gate must block before a single ALTER EXTENSION runs.

Pipeline Stages Walkthrough #

The upgrade pipeline moves a candidate through five ordered stages. Each has a distinct failure mode, and the job of the pipeline is to make each one either a clean block or a recoverable rollback rather than a production incident.

Stage 1 — Compatibility mapping #

The pipeline resolves the target version tuple against a maintained matrix that binds PostgreSQL minor releases, extension binaries, and shared-library dependencies. This is where an unsupported server/extension combination is rejected before anything is staged. Because static spreadsheets go stale, the matrix is generated from control files and artifact registries and checked in as a version-locked artifact — the mechanics of which live in Compatibility Matrix Synchronization.

Failure mode: a version tuple absent from the matrix, or a mismatched server major. The gate rejects the candidate at pull-request time with a structured error, never at 02:00 in production.

Stage 2 — Dependency resolution #

Extensions are not independent: postgis_topology requires postgis, which may require others, and every hop must be applied in dependency order. The pipeline topologically sorts the transitive requires graph and validates each member’s version against the target, exactly as developed in Dependency Tree Analysis.

Failure mode: applying an upgrade out of dependency order, which cascades into required extension "x" is not installed or an ABI break in a dependent. A correct topological sort prevents the cascade entirely.

Stage 3 — Asynchronous simulation and dry-run #

Rather than trusting the plan, the pipeline replays the full upgrade sequence against a snapshot clone, capturing the exact SQL execution order, lock acquisition sequence, and catalog diffs without blocking live transactions. This is where a missing update path or an unexpected AccessExclusiveLock is discovered in staging instead of production, and it is the subject of Async Upgrade Simulation. Routing that simulation onto an environment that mirrors production topology, configuration, and data cardinality is itself a discipline — see Test Environment Routing — because a simulation on a mismatched cluster validates nothing.

Failure mode: a non-transactional step (background-worker registration, shared-memory allocation) that commits immediately and cannot be rolled back. Simulation surfaces these so the plan can stage an explicit downgrade script and snapshot before the real run.

Stage 4 — Error categorization and the gate #

Simulation output is only useful if the pipeline can decide whether a surfaced error is transient (retry), terminal-but-recoverable (rollback), or terminal (block and page a human). That taxonomy — mapping PostgreSQL SQLSTATE classes to automated responses — is the job of Error Categorization Frameworks. The gate consumes the categorized result and emits a single binary decision: promote or block.

Failure mode: miscategorising a terminal error as transient, which loops a doomed upgrade until the window closes. The taxonomy exists precisely to prevent that.

Stage 5 — Threshold check and promotion #

A candidate that passes every prior gate still must fit the maintenance window. The pipeline estimates lock-hold duration, pooler drain time, and replication-lag tolerance against a computed threshold, promoting only if the operation fits — the calculation developed in Threshold Tuning for Downtime Windows. On promotion, execution is handed to the automated execution and rollback workflows; on any mid-flight failure it routes to Fallback Routing Strategies.

Failure mode: an operation whose real lock window exceeds the estimate, aborting mid-flight when the window closes. Historical lock-metric baselines keep the estimate honest.

Dependency & Compatibility Surface #

The reason batch promotions fail is that three coupling mechanisms compound risk, and the validation pipeline must model all three before issuing any command.

• Transitive requires chains. The full directed acyclic graph must be resolved and applied in topological order; validating version constraints across the whole graph is the guard against out-of-order cascades.
• shared_preload_libraries ordering. Extensions with C-level interdependencies must appear in the correct order in the preload list. Reordering or inserting an entry changes shared-memory allocation and, if wrong, aborts cluster bootstrap — and because it lives in postgresql.conf, it requires a restart that the threshold check must account for.
• Version constraint bands. Each extension version supports a bounded range of server majors, maintained as a live matrix rather than assumed.

A minimal constraint view — the authoritative version is generated and kept current in the matrix synchronization guide:

As documented in the official PostgreSQL Extension Development Guide, extensions may register background workers, custom GUCs, or catalog modifications that must load in a precise sequence; misordered preload directives or unresolved symbols abort bootstrap before any matrix can help, which is why preload ordering is validated in simulation on a production-mirroring topology.

Automation Integration Points #

The pipeline is driven from CI/CD, not a psql prompt, and there are three points where Python logic hooks into every candidate.

1. Pre-flight gates. Before any command runs, the pipeline snapshots pg_extension, confirms the target is reachable in pg_available_extension_versions, resolves the dependency graph, and validates the tuple against the matrix. Any failure blocks the deployment rather than attempting it. Storing control-file checksums alongside deployment manifests — aligned with Version Control & Branching — lets the gate assert strict environment parity so the version that was validated is the version that ships.

2. Dry-run payloads. The pipeline emits a structured plan — every command it would run, in order, with from/to versions — and asserts it against expectations before committing. A dry run is the difference between discovering a missing update path in staging versus at 02:00 in production.

3. Post-check assertions. After promotion the pipeline re-reads the catalog and runtime, confirms the observed version matches the intent, and only then releases dependent workloads; divergence routes to a fallback restore.

A minimal planner illustrates the pre-flight decision — it plans from catalog state and refuses to act when the target is unreachable:

#!/usr/bin/env python3
"""Upgrade planner: emit a dry-run plan only when the target is reachable."""
import psycopg2


def plan_upgrade(conn, name: str, target: str) -> dict:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT extversion FROM pg_extension WHERE extname = %s", (name,)
        )
        row = cur.fetchone()
        installed = row[0] if row else None

        # Confirm the target version is reachable on THIS node before planning.
        cur.execute(
            """
            SELECT 1
            FROM pg_available_extension_versions
            WHERE name = %s AND version = %s
            """,
            (name, target),
        )
        reachable = cur.fetchone() is not None

    if installed == target:
        return {"decision": "noop", "installed": installed}
    if not reachable:
        return {"decision": "block", "reason": "no update path to target",
                "installed": installed, "target": target}

    stmt = (
        f'CREATE EXTENSION "{name}" VERSION %s'
        if installed is None
        else f'ALTER EXTENSION "{name}" UPDATE TO %s'
    )
    return {"decision": "promote", "from": installed, "to": target,
            "stmt": stmt, "params": [target]}

The transactional-safety analysis of the ALTER EXTENSION step itself — which object types commit immediately and how to wrap the rest — is developed in ALTER EXTENSION automation, and the backup side of a failed promotion, including PITR restore, covers how to rewind a catalog that advanced past a partial failure.

Security & Privilege Enforcement #

An upgrade executes its migration SQL with elevated privilege and can register functions, composite types, and untrusted procedural languages directly into shared schemas — so the validation pipeline treats privilege as a gate, not an afterthought. Three enforcement practices contain the blast radius before promotion:

• Static payload analysis. CI scans the migration SQL for privilege-escalation patterns, untrusted-language invocations (plpythonu, plperlu), and unauthorized catalog modifications, rejecting the candidate before it reaches staging.
• Restricted SUPERUSER delegation. Promotion runs through a controlled installer role or trusted-extension marking rather than a broad superuser grant; the specific hazards of installing as superuser are enumerated in security implications of superuser extension installation.
• Schema isolation. Relocatable extensions promote into a dedicated schema with a pinned search_path, so an upgraded function cannot be shadowed by an untrusted caller mid-transition.

These practices are the operational core of Security Boundaries & Permissions and are non-negotiable before any candidate promotes into a shared production database.

Observability & Debugging #

A promotion is not complete until telemetry confirms a healthy post-state. Because extensions can introduce custom cost functions, index access methods, and background workers, upgrade events must be correlated with runtime behaviour: watch pg_stat_activity for lock waits and long-running backends immediately after the transition, track background-worker memory for extensions that register workers, and tail extension-specific log channels for the immediate-commit errors that non-transactional updates emit.

A practical post-promotion probe confirms the gate’s decision held:

-- Any backend blocked on a lock immediately after the promotion?
SELECT pid, wait_event_type, wait_event, state,
       now() - query_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY running_for DESC;

Correlating extension version against latency, lock contention, and cache-hit ratios via structured telemetry lets teams isolate a problematic promotion before it degrades the whole cluster; understanding which ALTER EXTENSION operations are transactional versus not is documented in the official PostgreSQL ALTER EXTENSION documentation and applied throughout the rollback workflows.

FAQ #

Related Pages #

• Compatibility Matrix Synchronization — generate a version-locked mapping of server releases, extension binaries, and shared libraries.
• Test Environment Routing — route candidates onto ephemeral clusters that mirror production topology and cardinality.
• Async Upgrade Simulation — replay the upgrade sequence against snapshot clones to capture locks and catalog diffs.
• Error Categorization Frameworks — map PostgreSQL error classes to retry, rollback, or block decisions.
• Threshold Tuning for Downtime Windows — size safe execution windows from lock metrics and replication tolerance.
• PostgreSQL Extension Architecture & Lifecycle Fundamentals — the on-disk artifact and catalog-state model this pipeline builds on.