Automated Execution & Rollback Workflows for PostgreSQL Extension Upgrades #

Once an upgrade candidate has cleared validation, the last mile — actually mutating a live catalog and being able to undo it — is where most extension incidents are made or avoided. This guide is the anchor reference for building the execution engine of a production PostgreSQL fleet: it explains the transactional boundaries that decide whether a failed ALTER EXTENSION rolls back cleanly or half-applies, walks every phase from pre-flight snapshot through health-gated commit to automated point-in-time rewind, and shows exactly where Python and CI/CD logic hook in so that every version transition either lands atomically or returns the database to a known-good state — never leaves it stranded in between.

Execution is the downstream half of the upgrade lifecycle: candidates arrive here already gated by Extension Upgrade Planning & Compatibility Validation, and this workflow assumes that catalog-state model. Its single responsibility is to run the promoted transition inside a bounded safety envelope and to guarantee a recovery path when the transition — or the runtime it produces — misbehaves.

Execution & Rollback at a Glance #

Every upgrade runs inside a bounded safety envelope: validate, snapshot, execute, and fall back automatically if health checks fail.

Every box in that flow is an automation boundary where the engine reads state, makes a decision, and records a verifiable result. Dry-run validation confirms the plan is still sound against live catalog state; the snapshot establishes an immutable rewind anchor; execution applies the transition inside the tightest transactional boundary the operation permits; the health gate decides whether the new runtime is releasable; and the fallback path restores the anchor when it is not. The rest of this guide decomposes each boundary and names the deeper reference for it.

Core Concepts: Transactionality Is the Whole Problem #

Extension execution fails when teams assume ALTER EXTENSION UPDATE behaves like ordinary migration DDL. It usually does — and the exceptions are exactly the operations that cannot be rolled back, which is why the execution engine must classify every step before it runs.

• Transactional catalog DDL. Most of what an update script does — CREATE FUNCTION, ALTER OPERATOR, CREATE TYPE, rewriting pg_proc and pg_extension rows — executes inside the calling transaction. A failure or an explicit ROLLBACK unwinds all of it and leaves no half-created objects. For these steps the transaction is the rollback mechanism, and no external safety net is needed.
• Immediate-commit operations. Registering a background worker, allocating shared memory, or changing cluster-global state commits the moment it runs and is not undone by ROLLBACK. A mid-flight failure after such a step leaves the catalog advanced but the runtime half-configured — the single most dangerous state in extension automation, because the database looks upgraded and behaves broken.
• Restore points and WAL. A named restore point (pg_create_restore_point('pre_postgis_34')) writes a labelled marker into the write-ahead log. Point-in-time recovery replays WAL forward from a base backup and stops at that marker, reconstructing the exact catalog and heap state that existed the instant before the transition began. This is the only rewind mechanism that survives an immediate-commit failure, a crashed backend, or a corrupted shared library.
• Health as a release gate, not a success signal. A transition that returns without error is not the same as a transition that is safe to release. Cold caches, invalidated plans, missing C symbols, and planner regressions all surface after the statement succeeds, which is why the engine treats post-execution health checks — not the exit code — as the promotion decision.

The engine’s entire purpose is to route each step to the right recovery mechanism: the transaction for reversible DDL, and a pre-captured Snapshot & Point-in-Time Recovery anchor for everything the transaction cannot cover.

Architecture & State Model: What the Catalog Commits, and When #

The execution engine reads and writes the same three catalog surfaces that the planning pipeline inspects, but during execution the critical question shifts from is this reachable to what has already been made durable.

Three facts govern every execution decision:

1. The catalog can advance without the runtime following. pg_extension.extversion is updated by the SQL-level script, but the C entry points it now references live in the shared library. If the new .so was never staged on this node, the row says 3.4.1 while every call resolves against 3.3.0 symbols and fails with could not find function ... in file. The engine must verify library load, not just the catalog row, before it trusts a success.
2. Immediate-commit steps make pg_extension an unreliable rollback target. Once a background-worker registration has committed, resetting extversion with a downgrade script does not un-register the worker. Only a WAL rewind to the pre-transition marker restores true consistency, which is why the restore point is captured before the first statement, not after the failure.
3. The restore anchor is only as good as the base backup behind it. A named restore point is a marker, not a backup. PITR replays WAL forward from a physical base backup up to that marker; if retention has expired the oldest base backup, the marker is unreachable. The engine validates that a replayable base exists as part of the pre-flight gate, not at recovery time.

A single probe confirms, immediately after a transition, that the catalog row and the loaded library agree rather than diverging silently:

-- Does the recorded version match a version the loaded library can actually serve?
SELECT
    e.extname,
    e.extversion                                   AS catalog_version,
    ae.default_version                             AS ondisk_default,
    (e.extversion = ANY(
        SELECT version FROM pg_available_extension_versions v
        WHERE v.name = e.extname
    ))                                             AS version_installable_here
FROM pg_extension e
LEFT JOIN pg_available_extensions ae ON ae.name = e.extname
WHERE e.extname = 'postgis';

Execution Phases Walkthrough #

The engine moves every promoted candidate through five ordered phases. Each has a distinct failure mode, and the job of the engine is to make each one either a clean block or a deterministic rewind rather than a production incident.

Phase 1 — Pre-flight snapshot and restore point #

Before any statement runs, the engine captures the rewind anchor: it confirms a replayable base backup exists, issues pg_create_restore_point() with a labelled marker tied to the deployment ID, and records the current pg_extension state for post-check comparison. This is a mandatory gate — a transition with no anchor is never allowed to proceed, because the immediate-commit class of failure has no other recovery path. The full mechanics of anchor capture and replay live in Snapshot & Point-in-Time Recovery.

Failure mode: retention has already expired the base backup behind the marker, or the replica the restore would promote is lagging. The gate blocks and the deployment is rescheduled behind a fresh base backup — never run blind.

Phase 2 — Dry-run revalidation #

The plan was validated upstream, but catalog state can drift between planning and execution (a concurrent update, a package change on one node). The engine re-emits the ordered statement plan against the live catalog and asserts it still matches expectations — same installed version, same reachable target, same dependency order — before committing to anything irreversible. A dry run here is the difference between catching drift in the first millisecond and discovering it three statements deep.

Failure mode: the installed version no longer matches the plan’s assumed starting point (extension "postgis" has no update path from version "3.3.1" to "3.4.1" where planning assumed 3.3.0). The engine halts before Phase 3 and returns the candidate to planning.

Phase 3 — Bounded execution #

The engine applies the transition inside the tightest boundary the operation permits: a single explicit transaction for reversible DDL, with statement_timeout and lock_timeout set so a blocked AccessExclusiveLock aborts rather than hanging the maintenance window. It serializes ALTER EXTENSION calls, walks intermediate hops when a direct jump is unsupported, and — critically — isolates any immediate-commit step so it runs only after the transactional portion has succeeded, keeping the irreversible surface as small as possible. The transactional-safety analysis of exactly which object types commit immediately, and how to wrap the rest, is developed in ALTER EXTENSION automation.

Failure mode: a statement exceeds lock_timeout waiting on a long-running reader, raising 55P03 lock_not_available. Inside the transaction this rolls back cleanly; the engine routes the whole candidate to retry or reschedule rather than forcing the lock.

Phase 4 — Health-gated commit #

Execution returning without error is a signal, not a verdict. The engine runs a post-execution health suite — verifying C-symbol availability, checking function signatures against the target ABI, confirming pg_stat_activity shows no backends wedged on extension-owned locks, and comparing query plans for a canary workload against a pre-upgrade baseline. Only when every probe passes does the engine commit the outer boundary, archive a fresh backup that now includes the upgraded state, and release dependent workloads.

Failure mode: the statement committed but a planner regression or a missing symbol surfaces in the canary. Because the health gate sits before release, the engine treats this as a failed transition and routes to Phase 5 rather than exposing the regression to production traffic.

Phase 5 — Deterministic rollback and failure routing #

When any prior phase fails a check or exceeds a timeout, the engine halts further mutation and routes to a rollback whose form matches the failure class. A transactional failure is already undone by ROLLBACK; an immediate-commit or runtime failure triggers a PITR rewind to the Phase 1 marker. Rollback logic is idempotent — safe to run repeatedly with no side effects — and reverses catalog mutations in the exact inverse order of execution. For catastrophic states where automated reconciliation stalls, operators reach the same restore anchor through documented emergency procedures that bypass the orchestration layer entirely, a pattern developed under Fallback Routing Strategies.

Failure mode: the rollback itself is interrupted (a network partition mid-restore). Because both the marker and the recovery script are idempotent, re-running the rollback converges on the same known-good state rather than compounding the damage.

Dependency & Compatibility Surface #

Execution inherits the coupling that makes batch upgrades dangerous, and the engine must honour all three constraints even though planning has already modelled them — because live state can drift after the plan is frozen.

• Transitive requires chains. The engine applies hops in the topological order resolved during planning, but re-checks that each dependency is still at the expected version at execution time. Re-validating against the live graph — the discipline of Dependency Tree Analysis — prevents an out-of-order required extension "x" is not installed when a concurrent change has moved a dependency.
• shared_preload_libraries ordering. A transition that adds or reorders a preload entry cannot take effect without a restart, and the engine must sequence that restart inside the window rather than assuming the new library is live. Reconciling the on-disk artifacts against the catalog before execution — Extension Registry Mapping — is what confirms the staged .so actually matches the version the catalog is about to record.
• Version constraint bands. The engine refuses to execute a tuple absent from the live compatibility matrix, even if it was valid when planned, because a matrix regeneration may have withdrawn support in the interim.

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

As documented in the official PostgreSQL ALTER EXTENSION documentation, an update applies the chain of migration scripts computed from files on disk; steps that register background workers or touch shared memory commit immediately, which is precisely why the rollback mechanism differs by extension and why the engine selects it per candidate rather than assuming transactionality.

Automation Integration Points #

The engine is driven from CI/CD, not a psql prompt, and there are three points where Python — typically the modern psycopg3 driver with explicit transaction control and connection pooling — hooks into every transition.

1. Pre-flight gates. Before any command runs, the engine confirms the restore anchor is replayable, re-reads pg_extension for live state, drains or advisory-locks conflicting sessions by cross-referencing pg_locks against pg_stat_activity, and revalidates the plan. Any failure blocks execution rather than attempting it. Storing the restore-point label and control-file checksum alongside the deployment manifest — aligned with Version Control & Branching — lets post-incident review reconstruct exactly what state the engine rewound from.

2. Dry-run payloads. The engine emits the structured plan it would execute — every statement, in order, with from/to versions and the timeout envelope — and asserts it against expectations before committing. The dry-run payload is also the artifact the error-categorization layer consumes when a step fails.

3. Post-check assertions. After execution the engine re-reads the catalog and runtime, runs the health suite, and only then releases dependent workloads; any divergence routes to the fallback restore.

A minimal execution wrapper illustrates the pattern — it captures the anchor first, wraps the transactional portion in an explicit block, and routes to rollback on any failed health check rather than trusting a clean exit code:

#!/usr/bin/env python3
"""Bounded ALTER EXTENSION execution with a pre-captured PITR anchor."""
import psycopg  # psycopg3


def execute_transition(dsn: str, name: str, target: str, deploy_id: str) -> dict:
    label = f"pre_{name}_{deploy_id}"
    with psycopg.connect(dsn, autocommit=True) as conn:
        # Phase 1 — capture the rewind anchor before any mutation.
        with conn.cursor() as cur:
            cur.execute("SELECT pg_create_restore_point(%s)", (label,))
            cur.execute(
                "SELECT extversion FROM pg_extension WHERE extname = %s", (name,)
            )
            row = cur.fetchone()
            installed = row[0] if row else None

        if installed == target:
            return {"status": "noop", "installed": installed}

        # Phase 3 — apply the transition inside a bounded transaction.
        try:
            with conn.transaction():
                with conn.cursor() as cur:
                    cur.execute("SET LOCAL lock_timeout = '5s'")
                    cur.execute("SET LOCAL statement_timeout = '120s'")
                    cur.execute(
                        f'ALTER EXTENSION "{name}" UPDATE TO %s', (target,)
                    )
        except psycopg.errors.LockNotAvailable:
            return {"status": "retry", "reason": "lock_timeout", "anchor": label}

        # Phase 4 — health gate decides release, not the exit code.
        if not _healthy(conn, name, target):
            return {"status": "rollback", "anchor": label,
                    "restore_to": label, "from": installed, "to": target}

        return {"status": "committed", "from": installed, "to": target}


def _healthy(conn, name: str, target: str) -> bool:
    """Confirm the catalog row matches the target and no lock is wedged."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT extversion FROM pg_extension WHERE extname = %s", (name,)
        )
        if cur.fetchone()[0] != target:
            return False
        cur.execute(
            "SELECT count(*) FROM pg_stat_activity "
            "WHERE wait_event_type = 'Lock' AND state = 'active'"
        )
        return cur.fetchone()[0] == 0

When _healthy returns false the caller invokes the PITR path against the recorded anchor; deciding which failures warrant a rewind versus a retry is the job of the categorization layer described below.

Security & Privilege Enforcement #

Execution is the moment elevated privilege is actually exercised: the migration SQL runs with the installer role’s rights and can register functions, composite types, and untrusted procedural languages directly into shared schemas. The engine treats privilege as a runtime gate, not an afterthought.

• Restricted SUPERUSER delegation. The engine executes through a controlled installer role or trusted-extension marking rather than a broad superuser session, so the blast radius of a compromised pipeline credential is bounded. The specific hazards of installing as superuser are enumerated in security implications of superuser extension installation.
• Schema isolation. Relocatable extensions execute into a dedicated schema with a pinned search_path, so an upgraded function cannot be shadowed by an untrusted caller during the transition window.
• Anchor integrity. The restore point and the base backup behind it must themselves be access-controlled; a rollback is only trustworthy if the WAL and backup it replays cannot be tampered with between capture and recovery.

These practices are the operational core of Security Boundaries & Permissions, and they are non-negotiable before any transition executes against a shared production database.

Observability & Debugging #

A transition is not complete until telemetry confirms a healthy post-state, and the failure classes that matter most are the ones that surface only after the statement returns. The engine correlates every execution with runtime behaviour: it watches pg_stat_activity for lock waits and long-running backends immediately after the transition, tracks background-worker memory for extensions that register workers, and tails extension-specific log channels for the immediate-commit errors that non-transactional updates emit.

A practical post-execution probe confirms the health gate’s decision held and no backend is wedged on an extension-owned lock:

-- Any backend blocked on a lock immediately after the transition?
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;

Mapping each surfaced error to the right recovery response — transient contention to retry, a recoverable terminal error to rollback, an unrecoverable one to block and page — is the discipline of Error Categorization Frameworks; miscategorising a terminal failure as transient loops a doomed transition until the window closes. Sizing that window in the first place, so the timeout envelope in Phase 3 is honest, is developed in Threshold Tuning for Downtime Windows. Correlating extension version against latency, lock contention, and cache-hit ratios via structured telemetry closes the loop between execution and runtime observability, letting teams isolate a problematic transition before it degrades the wider fleet.

FAQ #

Related Pages #

• ALTER EXTENSION Automation — wrap the transition in a dry-run-gated, timeout-bounded execution boundary.
• Snapshot & Point-in-Time Recovery — capture the restore anchor and replay WAL to rewind a partial failure.
• Fallback Routing Strategies — restore a prior version after an immediate-commit or runtime failure.
• Error Categorization Frameworks — map each surfaced error class to retry, rollback, or block.
• Extension Upgrade Planning & Compatibility Validation — the gated pipeline that promotes a candidate into this execution engine.
• PostgreSQL Extension Architecture & Lifecycle Fundamentals — the on-disk artifact and catalog-state model this workflow builds on.