Tracking Extension Lifecycle States in Production #

This page shows how to derive a deterministic lifecycle state — available, installed, upgrading, stable, deprecated — for every installed extension by correlating the PostgreSQL system catalogs, then gate deployments on that state so drift never reaches production silently.

Up: Version Control & Branching governs how extension artifacts move through branches and CI gates; this page supplies the catalog-derived state those gates read, and the underlying loader and catalog mechanics live in PostgreSQL Extension Architecture & Lifecycle Fundamentals.

When This Applies #

This technique is for DBAs, platform engineers, and database SREs who manage extensions across more than one PostgreSQL cluster and need a single source of truth for “what state is this extension actually in right now.” It applies whenever any of the following holds:

• You run a fleet of clusters where versions can drift. Package upgrades, base-image rebuilds, and manual psql sessions all mutate the catalog independently, so pg_extension.extversion on one node rarely matches another without enforcement.
• Your pipeline promotes schema changes on a branch cadence. A branch-aware release process needs a machine-readable state to gate on before it runs ALTER EXTENSION Automation against production.
• You have been surprised by a stuck or partial upgrade. An interrupted ALTER EXTENSION UPDATE can leave the catalog in a state that neither \dx nor a naive version check reveals.

The queries below assume PostgreSQL 11 or newer (for pg_available_extension_versions and pg_extension_update_paths) and read-only catalog access. Nothing here writes to the database — state tracking must be observation-only so it can run continuously against production and its replicas.

How PostgreSQL Records Extension State #

There is no single state column to read. A trustworthy lifecycle state is a join across four catalog surfaces, each answering a different question:

• pg_extension — the authoritative record of what is installed: extname, extversion, extnamespace, extowner. This is the only place the currently-live version is recorded.
• pg_available_extension_versions — what the server could install from the on-disk control files and SQL scripts. Comparing this against pg_extension.extversion is how you distinguish stable from upgradeable.
• pg_extension_update_paths(name) — whether a legal migration script chain exists from the installed version to a target. A version can be newer yet unreachable if the name--old--new.sql files are missing; that is upgradeable in name only.
• pg_depend and pg_shdepend — how tightly the extension is wired into user objects (deptype e/n) and into shared objects such as roles or tablespaces. Dependency weight is what makes a transition safe or dangerous, and it is invisible to a plain version comparison.

Relying on pg_extension alone is the most common tracking mistake: it tells you the installed version but nothing about whether that version is current, reachable, or safe to change. The state machine below is the model those four surfaces let you reconstruct — including the failure-and-rollback loop that a version check can never see.

Enumerating Catalog State #

The following diagnostic query normalizes version drift and exposes upgrade readiness across every installed extension in one pass. It resolves transitive coupling the same way Dependency Tree Analysis does, but collapses the result to a per-extension state label your automation can branch on:

SELECT
    e.extname,
    e.extversion,
    v.version AS available_version,
    CASE
        WHEN e.extversion = v.version THEN 'stable'
        WHEN string_to_array(v.version, '.')::int[] > string_to_array(e.extversion, '.')::int[] THEN 'upgradeable'
        WHEN string_to_array(v.version, '.')::int[] < string_to_array(e.extversion, '.')::int[] THEN 'downgrade_required'
        ELSE 'version_mismatch'
    END AS lifecycle_state,
    (SELECT count(*) FROM pg_depend d
     WHERE d.refobjid = e.oid AND d.deptype IN ('e', 'n')) AS dependency_weight,
    EXISTS (
        SELECT 1 FROM pg_extension_update_paths(e.extname) up
        WHERE up.source = e.extversion AND up.target = v.version AND up.path IS NOT NULL
    ) AS has_valid_update_path
FROM pg_extension e
JOIN pg_available_extension_versions v
    ON e.extname = v.name
-- Pick the highest available version using numeric (not lexical) ordering,
-- so 1.10.0 sorts above 1.9.0.
WHERE v.version = (
    SELECT version FROM pg_available_extension_versions
    WHERE name = e.extname
    ORDER BY string_to_array(version, '.')::int[] DESC
    LIMIT 1
);

Each lifecycle_state maps to a distinct root cause. Use this table to turn the label into an action:

Cross-reference pg_shdepend when extensions create shared objects — custom roles, tablespaces, or event triggers. Shared dependencies bypass database-level isolation and will make ALTER EXTENSION fail with ERROR: cannot drop object because other objects depend on it if they are not resolved first.

Runnable Implementation: A Drift-Detection Validator #

Production pipelines should run the state check as read-only diagnostics against a staging replica before promoting any change. The validator below opens a connection forced into read-only mode, resolves the installed version against the expected version, confirms a legal update path exists, and returns a structured verdict a pipeline can gate on:

import psycopg2
from psycopg2.extras import RealDictCursor
from typing import Dict, Any

def validate_extension_state(dsn: str, ext_name: str, expected_version: str) -> Dict[str, Any]:
    # Force the session read-only so a validation run can never mutate the catalog.
    with psycopg2.connect(dsn, options="-c default_transaction_read_only=on") as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute("""
                SELECT
                    e.extversion,
                    ae.default_version,
                    EXISTS(
                        SELECT 1 FROM pg_extension_update_paths(%s)
                        WHERE source = e.extversion AND target = %s AND path IS NOT NULL
                    ) AS has_update_path
                FROM pg_extension e
                JOIN pg_available_extensions ae ON e.extname = ae.name
                WHERE e.extname = %s;
            """, (ext_name, expected_version, ext_name))

            row = cur.fetchone()
            if not row:
                return {"state": "absent", "action": "block", "reason": "Extension not installed"}
            if row["extversion"] != expected_version:
                return {"state": "drift_detected", "action": "block",
                        "current": row["extversion"], "expected": expected_version}
            if not row["has_update_path"]:
                return {"state": "missing_update_paths", "action": "block",
                        "reason": "No valid migration path in catalog"}
            return {"state": "stable", "action": "allow"}

Wire the verdict into the pipeline as a hard gate rather than an advisory log line:

1. Pre-merge validation. Run the validator against an ephemeral staging database and block the merge unless action == "allow". This is the same promotion boundary described in Test Environment Routing.
2. Nightly drift reconciliation. Snapshot production state on a schedule and compare it against your Infrastructure-as-Code definitions, alerting on any drift_detected or version_mismatch. Feed persistent gaps into the Compatibility Matrix Synchronization process so the recorded target and the live catalog converge.
3. State-to-branch alignment. Tie extension versioning to application release branches so a missing_update_paths result on any target blocks the release before code and schema drift apart.

Expected Output & Verification #

A healthy extension at its expected version returns a clean allow verdict:

{ "state": "stable", "action": "allow" }

A database cluster that has drifted returns the exact versions in conflict, which the pipeline archives as a deploy artifact:

{ "state": "drift_detected", "action": "block", "current": "1.9.0", "expected": "1.10.0" }

Confirm three things before you trust a passing result:

• The read-only guard held. Run SHOW transaction_read_only; inside the same session — it must return on. If a connection pooler resets session GUCs, the options string may not survive; pin it per-transaction with SET LOCAL default_transaction_read_only = on; instead.
• Numeric version ordering, not lexical. Verify that 1.10.0 is treated as newer than 1.9.0. A lexical comparison silently inverts this and reports a false downgrade_required; the string_to_array(...)::int[] cast in the enumeration query is what prevents it.
• The update path is real, not merely newer. A stable verdict on an upgradeable extension is fine, but before you act on upgradeable confirm pg_extension_update_paths returns a non-null path for the target — a newer available_version with no script chain is not actually reachable.

Resolving Stuck and Blocked States #

When the state check flags something other than stable, remediate through an explicit, reversible procedure. Capture a pre-change snapshot first and drive any restore through Snapshot & Point-in-Time Recovery.

1. Repair a stuck upgrading state. Confirm catalog consistency with SELECT extversion, extnamespace FROM pg_extension WHERE extname = 'target_ext';, verify the target is reachable via pg_extension_update_paths('target_ext'), then re-run the update inside a bounded transaction so a second failure cannot hold a catalog lock indefinitely:

   BEGIN;
   SET LOCAL statement_timeout = '30s';
   ALTER EXTENSION target_ext UPDATE TO 'target_version';
   COMMIT;

2. Clear dependency locks. When high dependency_weight blocks a transition, enumerate the exact blockers before touching anything:

   SELECT pg_describe_object(classid, objid, objsubid) AS dependent_object
   FROM pg_depend d
   JOIN pg_extension e ON d.refobjid = e.oid
   WHERE e.extname = 'target_ext' AND d.deptype = 'n';

   Recreate or migrate those objects in a maintenance window rather than forcing a DROP EXTENSION, which would cascade through every dependent listed above.
3. Reconcile a downgrade_required state. Native downgrades are rarely supported. Export the dependent data and schema, DROP EXTENSION ... CASCADE only inside a controlled window, reinstall the target version, reapply the schema migrations, and re-run the validator until it returns stable before resuming application traffic.
4. Verify and record. After any transition, re-run the enumeration query and confirm the extension reports stable at the expected version, then log the transition to external monitoring so the state history survives the next base-image rebuild.

Edge Cases & Gotchas #

• version_mismatch with an empty pg_available_extension_versions join. If the on-disk control files were removed by a package upgrade, the enumeration query drops the row entirely instead of flagging it. Guard with a LEFT JOIN and treat a null available_version as orphaned — the extension is installed but the server can no longer describe or upgrade it.
• ERROR: cannot drop object because other objects depend on it (SQLSTATE 2BP01) during ALTER EXTENSION. A shared object created by the extension is referenced elsewhere. Query pg_shdepend before the transition, resolve the shared dependency, and classify the failure through Error Categorization Frameworks rather than retrying blindly.
• ERROR: extension "target_ext" has no update path from version "X" to "Y". The catalog reports upgradeable but the migration script chain is incomplete. Confirm the name--X--Y.sql files exist in $(pg_config --sharedir)/extension/; a missing intermediate script breaks the whole chain even when the endpoints are present.
• A pooled connection silently commits during validation. If default_transaction_read_only was not applied (a pooler reset it), a stray write in a diagnostic script can mutate the catalog. Always assert SHOW transaction_read_only returns on at the top of the run, and prefer SET LOCAL inside an explicit transaction so the guard cannot outlive or precede the statements it protects.

Related Pages #

• Up: Version Control & Branching
• PostgreSQL Extension Architecture & Lifecycle Fundamentals
• Dependency Tree Analysis
• ALTER EXTENSION Automation
• Compatibility Matrix Synchronization
• Snapshot & Point-in-Time Recovery