Routing Staging Upgrades to Mirror Production Topology #

This page shows how to make a staging cluster a byte-faithful mirror of production across the three layers an extension upgrade actually touches — the extension catalog, the on-disk shared objects, and the connection-routing plane — so a version promotion that passes in staging cannot behave differently in production.

Up: Test Environment Routing — the control plane that dispatches upgrade payloads through isolated validation stages. That parent page decides where a payload is routed; this page defines what parity the destination must prove before the payload is allowed to land.

Context & When This Applies #

Reach for this technique whenever staging and production are provisioned by different pipelines, at different times, or from different package mirrors — the normal case for any fleet older than a few months. Divergence in pg_extension state, shared_preload_libraries ordering, or the parameterized routing rules in front of the database causes an ALTER EXTENSION UPDATE to succeed in staging and then fail in production with a FATAL the test run never surfaced. It applies to PostgreSQL 12 through 17, where pg_extension, pg_available_extensions, and pg_available_extension_versions are all present, and it assumes an upgrade is promoted through a CI/CD gate rather than applied by hand.

The technique is most valuable for extensions whose runtime footprint reaches outside the catalog: PostGIS (versioned .so plus GEOS/PROJ/GDAL), pg_partman and pg_cron (both requiring shared_preload_libraries entries), and timescaledb (a background worker that must be preloaded in a specific order). For those, catalog parity alone is a false signal — the version string can match while the binary or the preload list does not.

Concept: Three Layers That Must All Agree #

“Mirror production topology” is not one comparison; it is three independent parity checks that must all hold before staging is a trustworthy rehearsal:

1. Catalog layer — what the server records as installed. pg_extension holds the installed extversion, relocatability, and extconfig dump table set; pg_available_extension_versions computes the installable surface from control files on disk. Staging must match production on the installed row and on the installable set, or the upgrade path that exists in one cluster is absent in the other. Understanding pg_available_extensions vs installed extensions is the prerequisite for reading these two catalogs correctly.
2. Binary layer — what the loader can actually resolve. The shared_preload_libraries GUC and the physical .so files in $(pg_config --pkglibdir) must align. A library present in production but missing in staging (or preloaded in a different order) triggers FATAL: could not access file at CREATE EXTENSION or on the next restart — never during the version-string comparison.
3. Routing layer — how connections reach the catalog. The proxy in front of the database (PgBouncer, HAProxy, or a service mesh) must expose staging through an isolated endpoint with the same max_connections, work_mem, and shared_buffers ratios, so parameter skew cannot mask an extension’s memory-allocation failure during promotion. Traffic that must never touch production pools is the concern the parent test environment routing layer owns; this page only asserts the destination is configured identically.

The declared version each layer is measured against must originate from an auditable extension registry mapping rather than whatever a mirror serves that hour, and where an extension pulls in siblings, the order they upgrade in comes from dependency tree analysis. A cell only counts as “in parity” when all three layers agree; any single disagreement is drift the promotion must not ship — the same intersection model the fleet-wide compatibility matrix synchronization pass applies across servers.

Baseline manifest #

Extract production state first. pg_dump --schema-only --section=pre-data captures the object skeleton, but the authoritative catalog manifest comes from a direct query. Run this identically on both clusters and diff the results:

-- Catalog-layer manifest: installed extensions and their exact state.
SELECT extname,
       extversion,
       extrelocatable,
       extnamespace::regnamespace AS schema,
       extconfig
FROM pg_extension
ORDER BY extname;

Any mismatch in extversion, or a missing row, indicates a broken upgrade-planning workflow that must be resolved before staging can rehearse anything. Cross-reference the installable surface with SELECT name, version FROM pg_available_extension_versions ORDER BY name, version; so you also catch a control file that exists in production but not staging.

Runnable Implementation #

The script below resolves topology parity for one extension end to end. It samples both clusters with a read-only role, compares the catalog row, the installable set, and the shared_preload_libraries slice for that extension, then — only if all layers agree — applies the routed upgrade inside a bounded transaction that snapshots the catalog first. It issues no DDL during the parity phase, so the comparison is safe to run against production.

#!/usr/bin/env python3
"""
Assert staging mirrors production topology for one extension, then route the
upgrade through a bounded, snapshot-guarded transaction. Read-only during the
parity phase; the ALTER EXTENSION runs only on staging and only if parity holds.
Requires: psycopg2-binary
"""
import json
import sys

import psycopg2
from psycopg2 import errors


def catalog_row(cur, extname: str) -> dict | None:
    cur.execute(
        "SELECT extversion, extrelocatable FROM pg_extension WHERE extname = %s;",
        (extname,),
    )
    row = cur.fetchone()
    return {"version": row[0], "relocatable": row[1]} if row else None


def installable(cur, extname: str) -> set:
    cur.execute(
        "SELECT version FROM pg_available_extension_versions WHERE name = %s;",
        (extname,),
    )
    return {r[0] for r in cur.fetchall()}


def preload_libraries(cur) -> list:
    cur.execute("SHOW shared_preload_libraries;")
    raw = cur.fetchone()[0]
    return [lib.strip() for lib in raw.split(",") if lib.strip()]


def sample(dsn: str, extname: str) -> dict:
    conn = psycopg2.connect(dsn, connect_timeout=5)
    try:
        with conn.cursor() as cur:
            return {
                "catalog": catalog_row(cur, extname),
                "installable": sorted(installable(cur, extname)),
                # Only the libraries relevant to this extension family matter.
                "preload": [p for p in preload_libraries(cur)
                            if extname.split("_")[0] in p],
            }
    finally:
        conn.close()


def parity(prod: dict, stg: dict, target: str) -> dict:
    reasons = []
    if prod["catalog"] != stg["catalog"]:
        reasons.append("catalog_drift")
    if set(prod["installable"]) != set(stg["installable"]):
        reasons.append("installable_set_drift")
    if prod["preload"] != stg["preload"]:
        reasons.append("shared_preload_libraries_drift")
    if target not in stg["installable"]:
        reasons.append("target_not_installable_on_staging")
    return {"in_parity": not reasons, "reasons": reasons}


def routed_upgrade(dsn: str, extname: str, target: str) -> str:
    """Snapshot the catalog, then apply the update under a bounded lock wait."""
    conn = psycopg2.connect(dsn)
    conn.autocommit = False
    try:
        with conn.cursor() as cur:
            # Deterministic rollback anchor: capture pre-upgrade catalog state.
            cur.execute(
                "CREATE TABLE IF NOT EXISTS ext_pre_upgrade_snapshot AS "
                "SELECT * FROM pg_extension;"
            )
            # Bound the wait so a contended catalog lock raises LockNotAvailable
            # (SQLSTATE 55P03) instead of blocking the drain window indefinitely.
            cur.execute("SET lock_timeout = '30s';")
            cur.execute(
                f'ALTER EXTENSION "{extname}" UPDATE TO %s;', (target,)
            )
        conn.commit()
        return "upgraded"
    except errors.LockNotAvailable:
        conn.rollback()
        return "blocked_lock_timeout"
    finally:
        conn.close()


if __name__ == "__main__":
    prod_dsn, stg_dsn = sys.argv[1], sys.argv[2]
    extname, target = sys.argv[3], sys.argv[4]

    prod = sample(prod_dsn, extname)
    stg = sample(stg_dsn, extname)
    verdict = parity(prod, stg, target)

    result = {"extension": extname, "target": target,
              "production": prod, "staging": stg, "parity": verdict}
    if verdict["in_parity"]:
        result["upgrade"] = routed_upgrade(stg_dsn, extname, target)
    else:
        result["upgrade"] = "skipped"
    print(json.dumps(result, indent=2))

The parity phase fails cheapest-first: three catalog round trips before any lock is taken, and the ALTER EXTENSION fires only when every layer agrees and the target is installable on staging. Before firing it, drain idle sessions on the routed endpoint so a stale connection cannot hold AccessShareLock past the window:

# Drain idle staging sessions before the routed upgrade, then confirm readiness.
psql -d staging_db -c "SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = 'staging_db' AND state = 'idle' AND pid <> pg_backend_pid();"
pg_isready -d staging_db && echo "endpoint drained and accepting"

Swapping psycopg2 for asyncpg to sample a large fleet concurrently is mechanical; the driver trade-offs and the retry semantics live under ALTER EXTENSION automation. For authoritative catalog structure, the PostgreSQL documentation on extensions and the psycopg2 transaction-control reference remain the canonical sources.

Expected Output & Verification #

When staging is a faithful mirror and the upgrade lands, the script serializes a verdict a promotion gate can key on directly:

{
  "extension": "postgis",
  "target": "3.4.2",
  "parity": { "in_parity": true, "reasons": [] },
  "upgrade": "upgraded"
}

A drift verdict names the exact layer that diverged, which is the signal the gate blocks on:

{
  "extension": "postgis",
  "target": "3.4.2",
  "parity": {
    "in_parity": false,
    "reasons": ["shared_preload_libraries_drift", "target_not_installable_on_staging"]
  },
  "upgrade": "skipped"
}

Confirm the routing layer independently — the script compares GUC-relevant preload state, but the proxy-facing parameters need their own assertion:

SELECT name, setting
FROM pg_settings
WHERE name IN ('max_connections', 'work_mem', 'shared_buffers',
               'shared_preload_libraries')
ORDER BY name;

Run it on both clusters and require the same values (or the same ratios where hardware differs). After the upgrade, re-run an extension-specific probe — SELECT PostGIS_Version(); or SELECT extversion FROM pg_extension WHERE extname = 'postgis'; — to prove functional parity, not just a catalog string. As a working baseline for the preload-sensitive families:

Edge Cases & Gotchas #

1. Catalog corruption on an interrupted upgrade. If ALTER EXTENSION UPDATE is cut off by a network partition, the OOM killer, or a forced pg_terminate_backend, the control file and the pg_extension row diverge. The next attempt raises:

ERROR:  extension "postgis" has no update path from version "3.3.4" to "3.4.2"

Halt all routing to the affected staging database, then audit the two sources: SELECT extversion FROM pg_extension WHERE extname = 'postgis'; against the control files in $(pg_config --sharedir)/extension/. Prefer DROP EXTENSION postgis CASCADE; followed by CREATE EXTENSION postgis VERSION '3.4.2'; in a controlled window over hand-editing the catalog. Editing a system catalog directly (SET allow_system_table_mods = on;) is a last resort that corrupts the catalog on a single wrong value.

2. shared_preload_libraries order skew. Staging preloads the same libraries as production but in a different order, and timescaledb refuses to start:

FATAL:  extension "timescaledb" must be loaded via shared_preload_libraries

The version-string comparison passed because the catalog matched; only the binary-layer check catches it. Align the GUC exactly — timescaledb must be first — and restart before the promotion. This is precisely the isolation that fallback routing strategies exist to contain when the mirror cannot be trusted.

3. Lock timeout during the routed upgrade. A stale idle-in-transaction session holds a catalog lock and the bounded wait fires:

ERROR:  canceling statement due to lock_timeout

The script returns "blocked_lock_timeout" and rolls back cleanly — no partial catalog change. Re-run the idle drain, confirm pg_stat_activity shows no idle in transaction sessions on the endpoint, then retry. Never raise lock_timeout to force it through; that trades a clean abort for a stuck promotion.

4. Superuser drift on the routed endpoint. Production installs extensions as a delegated role while staging runs as bare superuser, so a privilege failure that would block production never appears in the mirror. Assert the promotion role’s grants match by enforcing security boundaries & permissions on both clusters before trusting a staging pass.

If post-upgrade validation fails, restore from the ext_pre_upgrade_snapshot anchor where a downgrade script exists, otherwise recover the node through snapshot & point-in-time recovery rather than reconstructing the catalog by hand. Keep every validated topology manifest under version control & branching so each parity baseline is a reviewable commit, and route recurring drift signatures into error categorization frameworks instead of reading logs by hand.

FAQ #

Related Pages #

• Up: Test Environment Routing
• Extension Upgrade Planning & Compatibility Validation
• Building a Dynamic Compatibility Matrix for PostGIS and pgvector
• Tuning Maintenance Windows for High-Availability Clusters
• ALTER EXTENSION Automation