Async Upgrade Simulation for PostgreSQL Extension Lifecycle Management #

Async upgrade simulation decouples extension validation from production traffic, so a candidate version is proven safe against an ephemeral clone and rolled back before any live ALTER EXTENSION UPDATE or binary swap touches a customer-facing cluster. The operational problem it solves is specific: PostgreSQL surfaces the most damaging upgrade failures — catalog corruption, shared-library ABI mismatches, and cascade-breaking dependency changes — only at apply time or call time, never at plan time. DBAs, platform engineers, and database SREs who run extensions like PostGIS or pgvector across a fleet feel this every maintenance window: an upgrade that “installed fine” in a notebook wedges a production node under load. This guide builds the deterministic validation gate that converts those latent failures into a pre-flight pass/fail signal.

This page sits inside Extension Upgrade Planning & Compatibility Validation; it assumes the staged-pipeline model described there and drills into the simulation stage that proves a promotion is actually safe.

Simulation Pipeline at a Glance #

Async simulation validates an upgrade against an ephemeral instance and rolls everything back before promotion.

Each box is an automation boundary: the pipeline reads catalog or on-disk state, makes a decision, and records a verifiable artifact. A candidate only advances to promotion when the transactional dry-run exits cleanly and the schema diff is empty of unexpected mutations.

Prerequisites #

Before wiring simulation into a pipeline, confirm the following are in place:

• PostgreSQL 12 or newer on both the baseline and the ephemeral target. pg_available_extension_versions (needed to enumerate reachable versions) exists from 9.6, but the transactional-DDL guarantees this guide relies on are stable and well-documented from 12 onward.
• Matching major version and build between baseline and clone. The ephemeral instance must run the same server major and the same compiled extension .so set as the node you intend to promote — a dry-run against a mismatched build validates the wrong ABI. Reconcile on-disk artifacts across nodes first, a discipline covered in Extension Registry Mapping.
• Python 3.9+ for the orchestrator, plus a PostgreSQL client (psql) on the runner. The examples use only the standard library (subprocess, contextlib) so they run in a minimal CI image; swap in psycopg[binary] if you prefer a native driver.
• Privileges. ALTER EXTENSION ... UPDATE requires ownership of the extension, and many extensions require SUPERUSER to run their update scripts. Grant these only inside the disposable simulation environment; never mirror production superuser credentials into a shared runner. Enforce the boundaries in Security Boundaries & Permissions before any credential reaches CI.
• A resolved target version tuple. Simulation validates one concrete (extension, from_version, to_version, server_major) tuple that a maintained compatibility matrix has already declared allowed.

How Transactional Dry-Runs Actually Work #

The core mechanism is PostgreSQL’s transactional DDL. Most of what ALTER EXTENSION ... UPDATE does is ordinary catalog DDL — it rewrites rows in pg_proc, pg_class, pg_type, pg_depend, and pg_extension — and those writes are wrapped in the same MVCC machinery as any other statement. Running the update inside BEGIN ... ROLLBACK therefore lets the server execute every migration script, resolve every dependency, and raise every error it would raise for real, then discard all of it. If the transaction rolls back cleanly, the catalog mutations were valid; if it aborts, you have captured the exact failure without persisting a half-applied state.

The critical caveat — and the reason simulation is a distinct stage rather than a one-liner — is that not every side effect is transactional. Steps that register a background worker, allocate shared memory, load a library into shared_preload_libraries, or write to cluster-global state commit or take effect immediately and are not undone by ROLLBACK. pg_stat_statements resetting its shared counters and PostGIS raster GUCs are common examples. A transactional dry-run proves the catalog transition is sound; it does not prove a restart-time or shared-memory step is safe. That is why the pipeline pairs this stage with a full-cluster check via pg_upgrade for major-version moves, and why the orchestrator captures a schema diff rather than trusting the exit code alone.

Contrast this with pg_upgrade --check, which validates full-cluster major-version migrations but says nothing about a single extension’s update path. Extension-scoped upgrades are best simulated with transactional rollbacks plus schema diffing; cluster-scoped moves need pg_upgrade. A complete gate runs both.

Step-by-Step Implementation #

1. Pre-Simulation Dependency Resolution & Matrix Alignment #

Before simulation can execute, dependency graphs must be resolved against the target PostgreSQL major version and the extension release candidate. Extract pg_extension, pg_depend, and the reachable-version graph from the baseline cluster, then reconcile them with the target environment’s $libdir shared-object paths. This step builds on Dependency Tree Analysis to topologically order transitive requires chains, and on Compatibility Matrix Synchronization to reject conflicting CREATE EXTENSION directives, deprecated internal functions, and ABI breaks in C-based extensions.

The following idempotent script extracts the dependency tree, normalizes output to CSV, and validates it against a centralized matrix. It includes a cleanup trap so no artifact leaks across pipeline runs.

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

PG_BIN="${PG_BIN:-/usr/pgsql-16/bin}"
DB_NAME="${DB_NAME:-target_baseline_db}"
TARGET_EXT="${TARGET_EXT:-postgis}"
TARGET_VER="${TARGET_VER:-3.4.2}"
DEP_CSV="/tmp/pg_ext_dep_graph_$$"
MATRIX_PATH="${MATRIX_PATH:-/etc/pg-ext-matrix.yaml}"

cleanup() { rm -f "$DEP_CSV"; }
trap cleanup EXIT

echo "Resolving dependency graph for ${TARGET_EXT} -> ${TARGET_VER}..."

psql -X -t -A -F',' \
  -c "
    SELECT e.extname, e.extversion, p.extname AS dep_ext, p.extversion AS dep_ver
    FROM pg_extension e
    JOIN pg_depend d ON e.oid = d.objid
    JOIN pg_extension p ON d.refobjid = p.oid
    WHERE e.extname = '${TARGET_EXT}';
  " -d "$DB_NAME" > "$DEP_CSV"

if [ ! -s "$DEP_CSV" ]; then
  echo "WARN: No internal extension dependencies found for ${TARGET_EXT}. Proceeding with isolated validation."
fi

if ! python3 scripts/validate_matrix.py --matrix "$MATRIX_PATH" --deps "$DEP_CSV"; then
  echo "FATAL: Dependency resolution failed. Aborting simulation."
  exit 2
fi

echo "Dependency matrix aligned. Ready for ephemeral provisioning."

2. Ephemeral Routing & Transactional Dry-Run Execution #

Simulation payloads must never touch production replicas or active standby nodes. Apply strict Test Environment Routing to provision isolated, ephemeral PostgreSQL instances via Terraform, Kubernetes operators, or containerized CI runners. Within those targets, execute transactional dry-runs to validate catalog migrations, function signatures, and index rebuilds without committing WAL or inducing replication lag on any live topology.

The Python orchestrator below wraps dry-run execution, enforces an explicit timeout boundary, captures structured output, and guarantees idempotency through automatic teardown of its working schema.

#!/usr/bin/env python3
"""Idempotent async extension upgrade simulator."""
import subprocess
import sys
import logging
from pathlib import Path
from contextlib import contextmanager

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

@contextmanager
def ephemeral_db(dsn: str, schema: str = "sim_ext_upgrade"):
    """Provision and teardown an isolated schema for dry-run execution."""
    try:
        subprocess.run(
            ["psql", dsn, "-c", f"CREATE SCHEMA IF NOT EXISTS {schema};"],
            check=True, capture_output=True
        )
        yield schema
    finally:
        subprocess.run(
            ["psql", dsn, "-c", f"DROP SCHEMA IF EXISTS {schema} CASCADE;"],
            check=False, capture_output=True
        )

def run_extension_dry_run(dsn: str, ext_name: str, target_ver: str, timeout: int = 120):
    """Execute ALTER EXTENSION UPDATE inside a transactional rollback."""
    # The BEGIN/ROLLBACK envelope is what makes this a dry-run: the extension's
    # objects live in their own schema, so a search_path change would not isolate
    # the upgrade. Rolling back discards every catalog mutation.
    sql = (
        f"BEGIN; "
        f"ALTER EXTENSION {ext_name} UPDATE TO '{target_ver}'; "
        f"ROLLBACK;"
    )
    try:
        result = subprocess.run(
            ["psql", dsn, "-c", sql],
            capture_output=True, text=True, timeout=timeout, check=True
        )
        logging.info("Dry-run completed successfully. No catalog corruption detected.")
        return True
    except subprocess.TimeoutExpired:
        logging.error("Simulation exceeded timeout boundary.")
        return False
    except subprocess.CalledProcessError as e:
        logging.error(f"Simulation failed: {e.stderr.strip()}")
        return False

if __name__ == "__main__":
    DSN = sys.argv[1]
    EXT = sys.argv[2]
    VER = sys.argv[3]

    with ephemeral_db(DSN) as schema:
        success = run_extension_dry_run(DSN, EXT, VER)
        sys.exit(0 if success else 1)

3. CI/CD Integration & Deterministic Promotion Gates #

To operationalize async simulation, embed the orchestrator into your pipeline as a blocking validation stage. The workflow provisions the ephemeral target, executes the dry-run, parses the exit code and stderr for known-safe warnings (for example NOTICE: version "1.10" of extension "pg_stat_statements" is already installed), and gates promotion accordingly. A green simulation is the precondition that lets ALTER EXTENSION Automation run the real update against production unattended.

A GitHub Actions or GitLab CI pipeline enforces deterministic promotion by requiring a successful simulation artifact before merging infrastructure-as-code changes or triggering automated updates.

# .github/workflows/pg-ext-simulation.yml
name: PostgreSQL Extension Async Simulation
on:
  pull_request:
    paths: ['extensions/**', 'terraform/**']

jobs:
  validate-extension:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: simpass
          POSTGRES_DB: sim_db
        ports: ['5432:5432']
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4
      - name: Install PostgreSQL Client & Python Dependencies
        run: sudo apt-get update && sudo apt-get install -y postgresql-client python3 python3-pip
      - name: Run Async Upgrade Simulation
        run: |
          python3 scripts/async_ext_sim.py \
            "postgresql://postgres:simpass@localhost:5432/sim_db" \
            postgis 3.4.2
      - name: Gate Promotion
        if: failure()
        run: echo "::error::Simulation failed. Blocking promotion to production."

Dry-Run & Validation Gate #

A clean exit code is necessary but not sufficient — some failures leave the exit status at zero while mutating the catalog in ways you did not intend. Harden the gate by capturing a schema diff and emitting a structured decision artifact the pipeline can store and assert against.

Take a pg_dump --schema-only snapshot before and after the dry-run and compute the delta. Because the update runs inside BEGIN ... ROLLBACK, the post-rollback schema must be byte-identical to the pre-run schema; any drift signals a non-transactional side effect that escaped the rollback and must be reviewed before promotion.

pg_dump --schema-only -d "$DSN" > /tmp/pre_sim.sql
python3 scripts/async_ext_sim.py "$DSN" postgis 3.4.2
pg_dump --schema-only -d "$DSN" > /tmp/post_sim.sql

if ! diff -q /tmp/pre_sim.sql /tmp/post_sim.sql; then
  echo "::error::Schema drifted after rollback — non-transactional side effect detected."
  exit 1
fi

Emit the gate decision as a JSON artifact so downstream stages and audits consume one canonical result:

{
  "extension": "postgis",
  "from_version": "3.4.1",
  "to_version": "3.4.2",
  "server_major": 16,
  "dry_run": "passed",
  "schema_drift": false,
  "duration_seconds": 8.4,
  "decision": "promote"
}

For trend analysis and auditability, add OpenTelemetry or Prometheus exporters that track simulation duration, rollback frequency, and dependency-resolution latency across the fleet.

Failure Modes & Error Taxonomy #

When a dry-run aborts, the SQLSTATE on the raised error tells the gate whether the failure is transient (retry), terminal-but-recoverable (route to rollback), or unrecoverable (block and page a human). Map every surfaced class explicitly — the discipline formalized in Error Categorization Frameworks — rather than treating any non-zero exit as a generic failure.

The three C-level failures (58P01, 42883, 42501) are exactly the class that never surfaces from a naive “does it install” check — they appear only when the migration script calls into the compiled library, which is precisely what the transactional dry-run forces to happen.

Rollback & Recovery Path #

Inside the simulation the rollback is automatic: ROLLBACK discards the transaction and the ephemeral_db context manager drops the working schema in its finally block, so a failed dry-run leaves no residue on the clone. Recovery of the simulation stage itself is therefore just re-provisioning a fresh ephemeral instance.

The path that matters is recovery of the real promotion when a candidate that passed simulation still fails in production — for instance because a non-transactional step behaved differently under live load. Because the dry-run cannot catch every restart-time side effect, always stage a promotion behind a recovery point:

1. Take a pre-upgrade snapshot or confirm a recent base backup + WAL archive is available before the live ALTER EXTENSION UPDATE.
2. If the live update aborts mid-flight with a recoverable SQLSTATE, roll the surrounding transaction back and let the automation retry per the error taxonomy.
3. If a non-transactional side effect has committed (a background worker registered, shared memory allocated), a plain rollback is insufficient — restore from the snapshot using Snapshot & Point-in-Time Recovery to return the database cluster to its exact pre-promotion state.
4. Record the failing tuple back into the compatibility matrix so the gate rejects it at pull-request time until a fix ships.

Performance & Scale Considerations #

Simulation cost is dominated by three factors: provisioning the ephemeral instance, seeding it with representative data, and the update’s own lock-and-rewrite time. Tune each for a fleet:

• Clone provisioning. Copy-on-write snapshots (ZFS/Btrfs clones, cloud volume snapshots, or CREATE DATABASE ... TEMPLATE) restore a production-sized instance in seconds versus minutes for a logical restore. Reuse a warmed base image across pull requests rather than initializing from scratch each run.
• Data representativeness. An update that rebuilds an index or rewrites a table scales with row count. Simulating against an empty schema hides lock-hold duration entirely; seed the clone with production-scale cardinality so the measured duration_seconds is meaningful for Threshold Tuning for Downtime Windows.
• Lock contention window. ALTER EXTENSION ... UPDATE can take AccessExclusiveLock on objects its scripts alter; on a busy production node that lock is the downtime. Keep the simulation clone quiescent so the measured duration reflects pure work, not queueing, then add a safety margin for live contention.
• Fleet parallelism. Simulate independent (database, extension) tuples concurrently — pg_extension is per-database, so hundreds of instances can be validated in parallel across ephemeral runners. Cap concurrency at the runner pool and the artifact-registry rate limit, not the database, since each dry-run targets its own throwaway clone.

FAQ #

Related Pages #

• Extension Upgrade Planning & Compatibility Validation — the staged pipeline this simulation stage plugs into.
• Simulating Async Extension Upgrades with pg_upgrade — chain extension validation with a whole-cluster major-version check.
• Compatibility Matrix Synchronization — resolve the allowed version tuple simulation validates.
• Test Environment Routing — provision the ephemeral, production-mirroring target.
• Error Categorization Frameworks — map surfaced SQLSTATE classes to retry, rollback, or block.
• Threshold Tuning for Downtime Windows — size the maintenance window from measured simulation duration.