Test Environment Routing for PostgreSQL Extension Upgrades #

Test environment routing is the control plane that decides where an extension upgrade payload runs, in what order it passes each validation stage, and whether it is ever allowed to reach production. For PostgreSQL DBAs and platform engineers, routing is not a network concern — it is a policy-driven workflow that provisions an isolated target, replays the upgrade as a transactional dry-run, and blocks promotion the instant a gate fails. Done well, it turns a fleet-wide ALTER EXTENSION UPDATE from a manual, high-blast-radius operation into a repeatable, auditable pipeline stage. This guide covers the routing controller end to end: dependency-pinned dispatch, ephemeral provisioning, idempotent dry-run execution, structured verdicts, and fail-fast error handling.

Up: Extension Upgrade Planning & Compatibility Validation — routing is the stage that consumes a validated version tuple and proves it survives a production-shaped rehearsal before any gate promotes it.

The Routing Decision Flow #

Routing is a decision tree, not a straight pipe: every payload is resolved, provisioned, rehearsed, and then either promoted or quarantined. A candidate advances only when every stage returns a clean, machine-readable verdict; any failure short-circuits to triage instead of a partial write.

Each box is an automation boundary that reads state, makes one decision, and records a verifiable result. Dependency resolution decides whether the version tuple is even reachable; provisioning decides what shape of node it is rehearsed on; the transactional dry-run decides what would actually happen to the catalog; and the verdict gate decides whether the payload promotes or is quarantined. The rest of this page decomposes each boundary.

Prerequisites #

The routing controller fails closed: if a source is unreachable, a dependency is unresolved, or the dry-run cannot roll back cleanly, it refuses to promote rather than guessing. Confirm the environment before wiring it into a pipeline.

• PostgreSQL version: 9.6 or newer on the target node. The pre-flight reads pg_extension and pg_available_extension_versions (present since 9.6); PostgreSQL’s transactional DDL — the basis for the dry-run — has been reliable for ALTER EXTENSION UPDATE across all supported majors.
• Python packages: Python 3.8+ with psycopg2-binary (pip install psycopg2-binary). Only the standard library plus this driver is required. Async fleets can port the controller to asyncpg; the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required privileges: The dry-run must run as a role that owns the extension (or a superuser where the extension demands it), because ALTER EXTENSION UPDATE rewrites catalog rows. Scope that credential to the ephemeral environment only and keep it least-privilege per Security Boundaries & Permissions — production never needs to hand routing a standing superuser.
• A pinned compatibility tuple: Routing must receive an already-validated (server_major, extension, version) from Compatibility Matrix Synchronization. Routing rehearses a tuple; it does not decide whether the tuple is allowed.
• A topology descriptor: The provisioner needs a machine-readable description of production — primary/replica counts, connection-pooler layer, shared_preload_libraries order — so staging is a true mirror. Building that descriptor is the subject of Routing Staging Upgrades to Mirror Production Topology.

Core Concept: Transactional Dry-Run as the Routing Primitive #

The primitive that makes routing safe is PostgreSQL’s transactional DDL. ALTER EXTENSION UPDATE TO runs inside a transaction block, so the controller can BEGIN, apply the update, inspect the resulting catalog state, and ROLLBACK — proving the migration scripts execute end-to-end without leaving a single committed byte behind. The dry-run is therefore not a static lint of the --from--to-- script; it is a real execution whose only difference from production is the missing COMMIT.

Two properties make this the right routing primitive:

1. Idempotence. Because every rehearsal ends in ROLLBACK, the same payload can be routed repeatedly against the same environment with zero state drift. A retried pipeline run produces the same verdict, which is what lets routing sit inside a CI/CD stage that may re-execute on flakes.
2. Determinism against production shape. A dry-run against an empty scratch database proves only that the script parses. A dry-run against a node whose catalog, extension set, and shared_preload_libraries order mirror production proves the update behaves under real dependency chains — the difference that catches could not find function and update-path gaps that a bare instance hides. Where those transitive requires chains govern the order of transitions, routing consumes the ordering computed by Dependency Tree Analysis rather than re-deriving it.

Routing stays strictly decoupled from execution: it dispatches and rehearses, and the committing ALTER EXTENSION UPDATE against production is owned separately by ALTER EXTENSION Automation. Keeping the boundary sharp is what lets routing run on every candidate without a maintenance window.

Step-by-Step Implementation #

The following procedure turns the decision flow into a runnable controller. Each code block is complete and copy-pasteable; run every step against the ephemeral environment, never against production.

Step 1 — Resolve dependencies and pin the version tuple #

Before dispatch, confirm the target version is reachable and that no transitive dependency demands a co-upgrade. Query pg_available_extension_versions on the target and cross-reference the published compatibility tuple rather than trusting a static manifest.

#!/usr/bin/env python3
"""
Pre-dispatch dependency and reachability check.
Confirms the target version is installable on this node before routing.
Requires: psycopg2-binary
"""
import json
import sys
import psycopg2


def resolve_target(dsn: str, extension: str, target_version: str) -> dict:
    """Return a routing decision for a single (extension, version) tuple."""
    conn = psycopg2.connect(dsn)
    try:
        with conn.cursor() as cur:
            # Is the extension installed, and at what version?
            cur.execute(
                "SELECT extversion FROM pg_extension WHERE extname = %s;",
                (extension,),
            )
            row = cur.fetchone()
            if row is None:
                return {"route": "reject", "reason": f"{extension} not installed"}
            current = row[0]

            # Is the target version actually offered by this node's catalog?
            cur.execute(
                """
                SELECT 1 FROM pg_available_extension_versions
                WHERE name = %s AND version = %s;
                """,
                (extension, target_version),
            )
            if cur.fetchone() is None:
                return {"route": "reject",
                        "reason": f"version {target_version} not in catalog"}

            if current == target_version:
                return {"route": "skip", "reason": "target already active"}

        return {"route": "dispatch", "from": current, "to": target_version}
    finally:
        conn.close()


if __name__ == "__main__":
    if len(sys.argv) != 4:
        print("usage: resolve.py <dsn> <extension> <target_version>")
        sys.exit(1)
    decision = resolve_target(sys.argv[1], sys.argv[2], sys.argv[3])
    print(json.dumps(decision))
    sys.exit(0 if decision["route"] in ("dispatch", "skip") else 2)

For comprehensive catalog behaviour and version-resolution semantics, consult the official PostgreSQL documentation on available extension versions.

Step 2 — Provision an ephemeral environment that mirrors topology #

Routing must dispatch to a node whose architecture matches production, not a bare instance. The following provisioner spins up a disposable container, restores a production-shaped template (correct shared_preload_libraries, extension set, and pooler in front), and prints a DSN the controller routes against.

#!/usr/bin/env bash
# Provision a disposable, production-shaped test node and print its DSN.
# Fails closed: any provisioning error exits non-zero before routing runs.
set -euo pipefail

RUN_ID="route-$(date +%s)-$$"
PG_IMAGE="${PG_IMAGE:-postgres:16}"
TEMPLATE_DUMP="${1:?usage: provision.sh <production_template.sql>}"

# Match production shared library preloading exactly; ordering is significant.
PRELOAD="${SHARED_PRELOAD_LIBRARIES:-pg_stat_statements,pg_cron}"

cid="$(docker run -d --name "$RUN_ID" \
    -e POSTGRES_PASSWORD=routing \
    "$PG_IMAGE" \
    -c "shared_preload_libraries=${PRELOAD}")"

# Wait for readiness, then restore the production-shaped template.
until docker exec "$cid" pg_isready -q; do sleep 1; done
docker exec -i "$cid" psql -U postgres -v ON_ERROR_STOP=1 < "$TEMPLATE_DUMP"

port="$(docker port "$cid" 5432/tcp | cut -d: -f2)"
echo "{\"run_id\":\"${RUN_ID}\",\"dsn\":\"postgresql://postgres:routing@127.0.0.1:${port}/postgres\"}"

The provisioner is deliberately dumb about what to rehearse — it only guarantees the shape. The exact primary/replica and pooler-aware descriptor it consumes is built in Routing Staging Upgrades to Mirror Production Topology.

Step 3 — Run the idempotent transactional dry-run #

With a dispatch decision and a DSN in hand, replay the upgrade inside a transaction that always rolls back. This verifies extension availability, checks the target version, applies the update, asserts the resulting catalog state, and undoes every change — returning a structured payload for the pipeline to gate on.

#!/usr/bin/env python3
"""
Idempotent transactional dry-run for a PostgreSQL extension upgrade.
Emits a structured verdict and never commits a catalog change.
Requires: psycopg2-binary
"""
import json
import sys
from contextlib import contextmanager

import psycopg2
from psycopg2 import sql


@contextmanager
def routing_connection(dsn: str):
    """Connection whose transaction is always rolled back on exit."""
    conn = psycopg2.connect(dsn)
    conn.autocommit = False
    try:
        yield conn
    finally:
        conn.rollback()   # guarantee zero side effects
        conn.close()


def dry_run_upgrade(dsn: str, extension: str, target_version: str) -> dict:
    result = {"status": "pending", "extension": extension,
              "target_version": target_version}

    with routing_connection(dsn) as conn:
        cur = conn.cursor()

        # 1. Extension must already be present in this environment.
        cur.execute("SELECT extversion FROM pg_extension WHERE extname = %s;",
                    (extension,))
        current = cur.fetchone()
        if current is None:
            result.update(status="failed", reason=f"{extension} not installed")
            return result
        if current[0] == target_version:
            result.update(status="skipped", reason="target already active")
            return result

        # 2. Target version must be offered by the catalog.
        cur.execute(
            "SELECT 1 FROM pg_available_extension_versions "
            "WHERE name = %s AND version = %s;",
            (extension, target_version),
        )
        if cur.fetchone() is None:
            result.update(status="failed",
                          reason=f"{target_version} unavailable in catalog")
            return result

        # 3. Transactional dry-run: apply, assert, roll back.
        try:
            cur.execute(
                sql.SQL("ALTER EXTENSION {} UPDATE TO {}").format(
                    sql.Identifier(extension), sql.Literal(target_version)
                )
            )
            cur.execute("SELECT extversion FROM pg_extension WHERE extname = %s;",
                        (extension,))
            applied = cur.fetchone()[0]
            if applied != target_version:
                raise RuntimeError("dry-run did not reach expected version")
            result.update(status="success", from_version=current[0],
                          dry_run_version=applied)
        except psycopg2.Error as exc:
            result.update(status="failed",
                          sqlstate=exc.pgcode,
                          reason=str(exc).strip())
        except RuntimeError as exc:
            result.update(status="failed", reason=str(exc))

    return result   # connection context has already rolled back


if __name__ == "__main__":
    if len(sys.argv) != 4:
        print("usage: dry_run.py <dsn> <extension> <target_version>")
        sys.exit(1)
    payload = dry_run_upgrade(sys.argv[1], sys.argv[2], sys.argv[3])
    print(json.dumps(payload, indent=2))
    sys.exit(0 if payload["status"] in ("success", "skipped") else 2)

Step 4 — Emit a routing verdict and gate promotion #

The controller’s only output is a machine-readable verdict that downstream stages consume to decide promotion. On success it routes the payload forward; on failure it captures the exact SQLSTATE, tags the environment, and halts.

#!/usr/bin/env python3
"""Turn a dry-run payload into a promotion verdict for the pipeline."""
import json
import sys


def routing_verdict(payload: dict) -> dict:
    status = payload.get("status")
    if status in ("success", "skipped"):
        return {"decision": "promote", "next_stage": "async-simulation",
                "evidence": payload}
    # Any failure quarantines the environment and blocks promotion.
    return {"decision": "quarantine",
            "label": "FAILED_ROUTING",
            "sqlstate": payload.get("sqlstate"),
            "evidence": payload}


if __name__ == "__main__":
    verdict = routing_verdict(json.load(sys.stdin))
    print(json.dumps(verdict, indent=2))
    sys.exit(0 if verdict["decision"] == "promote" else 2)

A promote verdict routes the payload to the next validation tier; a quarantine verdict is a hard stop. Where the next tier models the upgrade against production-sized data across parallel clusters, routing hands off to Async Upgrade Simulation, which reuses the same checksummed tuple so staging and simulation evaluate byte-identical inputs.

Dry-Run & Validation Gate #

The dry-run is the safety interlock: it exercises the real migration scripts but archives a verdict instead of committing. A clean pass looks like this:

{
  "status": "success",
  "extension": "postgis",
  "target_version": "3.5.0",
  "from_version": "3.4.1",
  "dry_run_version": "3.5.0"
}

Gate the promotion on three conditions before the payload advances:

• status is success or skipped, never failed. A failed verdict carries an SQLSTATE and a reason; the controller must quarantine and route to triage rather than promote.
• The rollback completed. The routing_connection context manager guarantees a ROLLBACK, so a post-run SELECT extversion FROM pg_extension on the ephemeral node must still report the original version. If it does not, the environment is contaminated and must be destroyed, not reused.
• The verdict is pinned to the same tuple the matrix published. A dry-run that succeeds against a different (server_major, extension, version) than the one under promotion proves nothing. Carry the compatibility checksum through the verdict so the gate can reject a mismatched rehearsal.

Failure Modes & Error Taxonomy #

Routing fails in a small, well-defined set of ways. Each has a distinctive signal — an SQLSTATE, a log line, or a controller verdict — and each maps to a specific recovery action. Capture the SQLSTATE from psycopg2.Error.pgcode so triage keys off a code, not a scraped string.

When these signals recur across runs, route them into a structured classifier instead of reading logs by hand — Error Categorization Frameworks turns these SQLSTATEs and verdicts into automated triage signals, and known-bad tuples can be diverted early with Fallback Routing Strategies.

Rollback & Recovery Path #

Routing never mutates production, so recovery is about the ephemeral environment and the verdict artifact, not the live catalog. A failed route is undone by tearing down the disposable node and reverting the promotion pointer.

1. Trust the transaction, then verify it. The dry-run’s ROLLBACK already restores the ephemeral catalog; confirm with a post-run extversion read before the node is considered clean. A confirmed rollback means the environment is reusable.
2. Tag and isolate on failure. Apply the FAILED_ROUTING label so the environment is never silently reused for another payload, and archive the full verdict — SQLSTATE, reason, and the pinned tuple — to an immutable artifact store for the change record.
3. Destroy contaminated nodes. If the post-run read shows the version advanced, the rollback did not fire; destroy the container outright rather than attempting a manual ALTER EXTENSION ... UPDATE TO back to the prior version, which itself may fail.
4. Block production promotion. Hold the payload out of the promotion queue until the dependency graph is reconciled or the compatibility tuple is re-published. Routing must never advance a payload whose last verdict was quarantine.
5. Restore production if a live upgrade already leaked past routing. Routing is a rehearsal; if a committing upgrade downstream still fails, drive the restore through Snapshot & Point-in-Time Recovery rather than hand-editing the catalog.

Pair every verdict with Version Control & Branching so each routing decision is a reviewable commit and every quarantine is a reproducible record rather than an improvised fix.

Performance & Scale Considerations #

The dry-run itself is cheap; the cost lives in provisioning and in fleets with many distinct topologies.

• Provisioning is the long pole. Restoring a production-shaped template dominates route latency. Keep a warm pool of pre-restored ephemeral nodes keyed by topology descriptor so a route claims a ready node instead of building one, and bound every provisioning step with a timeout so a hung restore cannot stall the pipeline.
• Parallelise across topologies, serialise per node. Distinct (server_major, topology) combinations can be routed concurrently on separate nodes, but a single ephemeral node runs one dry-run at a time — the transaction would otherwise contend for the same AccessExclusiveLock that ALTER EXTENSION UPDATE takes on the extension’s objects.
• Lock window is short but real. Even in a rolled-back transaction, the update acquires locks for its duration; on a node carrying production-sized objects, measure that window so the eventual committing upgrade is budgeted correctly under Threshold Tuning for Downtime Windows.
• Sample topologies, don’t rehearse every replica. On fleets of hundreds of identical replicas, route one representative node per topology generation and reconcile the rest against their package manifest, accepting that a divergent replica outside the sample is caught at the compatibility gate rather than rehearsed individually.

FAQ #

Related Pages #

• Up: Extension Upgrade Planning & Compatibility Validation
• Routing Staging Upgrades to Mirror Production Topology
• Compatibility Matrix Synchronization
• Async Upgrade Simulation
• Error Categorization Frameworks
• ALTER EXTENSION Automation