ALTER EXTENSION Automation in PostgreSQL Upgrade Pipelines #

Turning ALTER EXTENSION UPDATE into a repeatable pipeline step is harder than it looks, because the statement is not ordinary DDL: it chains version-specific SQL scripts, may load a new shared library into the server process, and can silently break rollback guarantees when an upgrade script issues its own internal COMMIT. Run it by hand across a fleet and you accumulate schema drift, untracked dependency conflicts, and half-applied catalog states that only surface days later as planner regressions. This page is for database SREs, platform engineers, and Python DevOps teams who need every extension upgrade to run unattended inside a bounded safety envelope — validated by a zero-side-effect dry-run, gated on deterministic catalog metadata, and wired to an automatic recovery path when post-checks fail.

Up: Automated Execution & Rollback Workflows — the parent process that decouples deployment intent from execution reality; the pre-flight snapshotting, health-check routing, and failure envelope described there wrap the ALTER EXTENSION mechanics detailed below.

Automation Flow at a Glance #

The wrapper gates execution on a dry-run payload and triggers recovery automatically when post-checks fail.

Prerequisites #

The automation model below fails closed: any precondition it cannot positively verify is treated as unsafe rather than assumed benign.

• PostgreSQL version: 12 or newer. pg_extension_update_paths(), pg_available_extension_versions, and the transactional rollback that catalog-only updates rely on are all dependable from 12 onward. Extensions marked trusted require 13+.
• Python packages: Python 3.8+ with psycopg2-binary (pip install psycopg2-binary). The synchronous wrapper here uses only the standard library plus psycopg2; if your orchestration stack is asynchronous the same logic ports directly to asyncpg, and the driver trade-offs are covered under the Automated Execution & Rollback Workflows overview.
• Required privileges: the acting role must own the target extension (or hold membership in its owner) to run ALTER EXTENSION, and extensions whose control file sets superuser = true need elevation. Never wire a raw superuser DSN into the pipeline — provision the least-privilege deploy role and audited wrapper described in Security Boundaries & Permissions instead.
• Catalog state assumptions: the extension is already installed (CREATE EXTENSION is a separate concern), its dependency graph is resolved so prerequisites precede dependents per Dependency Tree Analysis, and the target share/extension/ directory ships the control file and every intermediate <ext>--<old>--<new>.sql migration script.

Core Concept: Transactional vs Non-Transactional ALTER EXTENSION #

The single fact that governs safe automation is that ALTER EXTENSION ... UPDATE is not an atomic operation you can universally wrap in a transaction. PostgreSQL upgrades an extension by locating a path through its version graph and executing each intermediate script in order. Understanding three properties of that process tells you exactly where automation can and cannot guarantee rollback.

Upgrades are chained, not jumped. PostgreSQL never performs an arbitrary version jump. It reads the extension’s control file, enumerates the available <name>--<from>--<to>.sql scripts, and computes a route from the installed version to the requested target — the same catalog surface a compatibility matrix is built from. If any intermediate script is missing, the update aborts before touching a single catalog row. The server exposes this graph directly through pg_extension_update_paths('<name>'), which returns a non-NULL path only when a valid chain exists. Querying it up front is how automation distinguishes “unsupported jump” from “genuine failure” without guessing.

Most catalog-only updates are transactional; some are not. An update script that only creates functions, operators, or types runs inside the surrounding transaction, so a failure or an explicit ROLLBACK leaves the catalog untouched. But an extension whose upgrade script issues an internal COMMIT, registers a background worker, allocates shared memory, or requires a change to shared_preload_libraries breaks that guarantee. For those, a ROLLBACK after a partial apply does not restore prior state — recovery must fall back to a snapshot restore. Detecting which class an upgrade falls into, before you commit to it, is the core job of the validation gate.

A new .so may not be live until restart. When an upgrade ships a new shared library, the running backend may continue to serve the old symbols until the library is reloaded. Automation must verify that the loaded library matches the catalog version — a mismatch surfaces later as function ... does not exist in library rather than at upgrade time. This is why post-execution validation probes function signatures, not just extversion.

The whole rollback strategy hinges on which class a given upgrade falls into — the difference between a ROLLBACK that restores prior state and one that silently does not.

Because a mid-flight failure can leave a node in an unrecoverable-by-rollback state, every apply is preceded by a restore point. The mechanics of capturing and restoring that baseline live in the sibling Snapshot & Point-in-Time Recovery topic; this page assumes that gate is armed and focuses on the ALTER EXTENSION execution itself.

Step-by-Step Implementation #

The procedure builds the wrapper bottom-up: resolve state, prove a path exists, check for contention, execute inside a bounded transaction, then wire the whole thing behind a CI gate. Each block is complete and copy-pasteable.

Step 1 — Resolve current and target state from the catalog #

Before anything mutates, read the installed version from pg_extension and confirm the target is actually available. A missing extension or an already-satisfied target must short-circuit the pipeline, not raise an ambiguous error deep inside the apply.

-- Read installed vs available versions in one round trip.
SELECT
    e.extname,
    e.extversion                              AS installed_version,
    a.default_version                         AS catalog_default,
    (a.name IS NOT NULL)                      AS target_available
FROM pg_extension e
LEFT JOIN pg_available_extensions a ON a.name = e.extname
WHERE e.extname = 'pgvector';

Step 2 — Prove a real upgrade path exists #

Never assume installed -> target is reachable. Ask the server. pg_extension_update_paths() returns one row per candidate (source, target) pair with a path column that is non-NULL only when the chain of migration scripts is complete.

-- Non-NULL path == the server can chain the required scripts.
SELECT source, target, path
FROM pg_extension_update_paths('pgvector')
WHERE source = '0.5.1'
  AND target = '0.7.0';

An empty result or a NULL path means an intermediate script is absent from share/extension/ — a packaging problem to fix at the artifact layer (verify provenance against Extension Registry Mapping), not something to force past.

Step 3 — Guard against lock contention #

ALTER EXTENSION takes an AccessExclusiveLock on the objects it rewrites. If another session already holds a conflicting lock, the statement blocks — potentially for the whole maintenance window. Detect contention before opening the write transaction and abort cleanly rather than hang.

-- Any AccessExclusiveLock on a relation is a stop signal for the apply.
SELECT count(*) AS blocking_locks
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
WHERE l.locktype = 'relation'
  AND l.mode = 'AccessExclusiveLock'
  AND l.granted;

Step 4 — Execute inside a bounded, SQLSTATE-aware wrapper #

The Python wrapper composes the previous checks into a single idempotent operation. It supports a dry-run mode that emits a structured payload without mutating the catalog, maps PostgreSQL SQLSTATE codes to distinct exit statuses, and rolls back on any unexpected failure. It can be driven by CI runners or by orchestration tools such as Ansible or Terraform.

import json
import os
import sys

import psycopg2
from psycopg2 import errors, sql


def run_extension_upgrade(dsn, ext_name, target_version, dry_run=False):
    """Idempotent, SQLSTATE-aware ALTER EXTENSION UPDATE wrapper."""
    conn = psycopg2.connect(dsn)
    cur = conn.cursor()
    try:
        # 1. Resolve current state; a missing extension is a hard error.
        cur.execute(
            "SELECT extversion FROM pg_extension WHERE extname = %s",
            (ext_name,),
        )
        row = cur.fetchone()
        if row is None:
            raise RuntimeError(f"extension '{ext_name}' is not installed")
        current_ver = row[0]

        # Idempotency: already at target means nothing to do.
        if current_ver == target_version:
            print(json.dumps({
                "status": "skipped",
                "current_version": current_ver,
                "target_version": target_version,
            }))
            return

        # 2. Prove an upgrade path exists before committing to anything.
        cur.execute(
            """
            SELECT EXISTS (
                SELECT 1 FROM pg_extension_update_paths(%s)
                WHERE source = %s AND target = %s AND path IS NOT NULL
            )
            """,
            (ext_name, current_ver, target_version),
        )
        if not cur.fetchone()[0]:
            raise RuntimeError(
                f"no upgrade path from {current_ver} to {target_version}"
            )

        # 3. Refuse to proceed while a conflicting lock is held.
        cur.execute(
            """
            SELECT count(*) FROM pg_locks l
            JOIN pg_class c ON l.relation = c.oid
            WHERE l.locktype = 'relation'
              AND l.mode = 'AccessExclusiveLock'
              AND l.granted
            """
        )
        if cur.fetchone()[0] > 0:
            raise RuntimeError("active exclusive locks detected; aborting")

        # 4a. Dry-run: emit a machine-readable verdict, mutate nothing.
        if dry_run:
            print(json.dumps({
                "status": "dry_run_approved",
                "extension": ext_name,
                "current_version": current_ver,
                "target_version": target_version,
                "path_exists": True,
                "blocking_locks": 0,
            }, indent=2))
            return

        # 4b. Apply. %I / %L quote the identifier and literal to block injection.
        cur.execute(
            sql.SQL("ALTER EXTENSION {} UPDATE TO {}").format(
                sql.Identifier(ext_name), sql.Literal(target_version)
            )
        )
        conn.commit()
        print(json.dumps({
            "status": "success",
            "extension": ext_name,
            "new_version": target_version,
        }))

    except errors.lookup("55006") as exc:  # object_in_use
        conn.rollback()
        print(json.dumps({
            "status": "error", "sqlstate": "55006", "message": str(exc),
        }), file=sys.stderr)
        sys.exit(1)
    except Exception as exc:
        conn.rollback()
        print(json.dumps({"status": "failed", "error": str(exc)}), file=sys.stderr)
        sys.exit(2)
    finally:
        cur.close()
        conn.close()


if __name__ == "__main__":
    run_extension_upgrade(
        dsn=os.environ["PG_DSN"],
        ext_name="pgvector",
        target_version="0.7.0",
        dry_run="--dry-run" in sys.argv,
    )

Step 5 — Validate the result before marking success #

An exit code of zero from the apply is necessary but not sufficient. Confirm the catalog reached the target version and that the running library actually resolves the extension’s functions before the pipeline reports success.

-- Post-apply assertions: version alignment plus function resolvability.
SELECT extversion FROM pg_extension WHERE extname = 'pgvector';

-- A row here proves the new .so exposes the expected symbol; an empty
-- result means the library and catalog are at mismatched versions.
SELECT proname
FROM pg_proc
WHERE proname = 'vector_dims'
  AND pronamespace = 'public'::regnamespace;

Dry-Run & Validation Gate #

The dry-run is the contract between the pipeline and the database: it must produce a deterministic verdict that a CI gate can branch on without re-querying the server. Running the wrapper with --dry-run emits exactly that:

{
  "status": "dry_run_approved",
  "extension": "pgvector",
  "current_version": "0.5.1",
  "target_version": "0.7.0",
  "path_exists": true,
  "blocking_locks": 0
}

Wire it into the deployment workflow as a hard gate: parse the payload, promote only on status == "dry_run_approved", and halt on anything else. The GitHub Actions job below chains validation, gate, and apply, and preserves the dry-run output as a build artifact for audit.

jobs:
  extension-upgrade:
    runs-on: ubuntu-latest
    env:
      PG_DSN: ${{ secrets.PROD_DATABASE_URL }}
    steps:
      - uses: actions/checkout@v4
      - name: Install dependencies
        run: pip install psycopg2-binary
      - name: Dry-run validation
        id: dryrun
        run: |
          python upgrade_ext.py --dry-run > dryrun_output.json
          cat dryrun_output.json
          {
            echo "payload<<EOF"
            cat dryrun_output.json
            echo "EOF"
          } >> "$GITHUB_OUTPUT"
      - name: Gate approval
        if: fromJSON(steps.dryrun.outputs.payload).status != 'dry_run_approved'
        run: |
          echo "Dry-run blocked promotion; halting."
          exit 1
      - name: Execute upgrade
        run: python upgrade_ext.py

For upgrades that cross a major PostgreSQL boundary rather than bumping an extension version in place, pair this gate with an out-of-band Async Upgrade Simulation so pg_upgrade --check proves the whole cluster survives before any binary swap.

Failure Modes & Error Taxonomy #

Extension upgrades fail through a small, well-defined set of SQLSTATE codes. Each has a distinct root cause and remediation; feeding them into a structured classifier turns them into automated triage signals, as developed in Error Categorization Frameworks.

The pair to encode first is 22023 versus 42501: the former is a build-artifact problem you fix upstream and re-run deterministically, while the latter is a privilege gap that must be closed by policy, not by retry.

Rollback & Recovery Path #

When an apply fails mid-flight, the recovery branch depends entirely on whether the upgrade was transaction-safe — the distinction drawn in the core concept above.

1. Catalog-only failure. The wrapper already issued ROLLBACK in its exception handler, so a purely catalog-mutating upgrade is fully undone. Confirm pg_extension.extversion still reads the prior version and mark the node healthy.
2. Non-transactional failure. If the upgrade registered a background worker, altered shared_preload_libraries, or ran its own internal COMMIT, a ROLLBACK is insufficient. Halt dependent workloads and run the extension’s shipped downgrade script — ALTER EXTENSION X UPDATE TO '<prior_version>' — through the same wrapper, then restart the node if a preload library changed.
3. No downgrade path. When no reverse script exists, restore from the pre-upgrade restore point rather than hand-editing the catalog. Driving the restore through Snapshot & Point-in-Time Recovery keeps the recovery itself auditable and reproducible.
4. Route the incident, don’t loop. A failure that is neither cleanly rolled back nor restorable by downgrade routes to the recovery logic in Fallback Routing Strategies rather than retrying blindly.

Capture the pre-upgrade version set as a deploy artifact so the rollback target is always reproducible; pairing that snapshot with Version Control & Branching makes every ALTER EXTENSION a reviewable, revertible change.

Performance & Scale Considerations #

The ALTER EXTENSION itself is usually fast; the operational cost is in lock windows and fleet rollout discipline.

• Lock hold time. The AccessExclusiveLock blocks reads and writes on the affected objects for the duration of the rewrite. Keep each apply in its own short transaction so the contention window is bounded, and schedule it against a low-traffic slot sized by Threshold Tuning for Downtime Windows.
• Downtime estimate. For catalog-only upgrades the window is typically sub-second; upgrades that rebuild indexes or rewrite large relations scale with table size and must be measured on a production-shaped replica, not assumed.
• Parallel validate, serialized apply. The dry-run and pre-flight queries never mutate state, so fan them out across every node at once. Roll the apply out in controlled waves — canary first — so a bad upgrade is caught before it reaches the whole fleet.
• Staging fidelity. Validate the upgrade against a replica whose extension set and shared_preload_libraries ordering mirror production, using Test Environment Routing; a staging cluster missing one preload library is the classic reason a gate passes and the apply fails.

FAQ #

Related Pages #

• Up: Automated Execution & Rollback Workflows
• Snapshot & Point-in-Time Recovery
• Security Boundaries & Permissions
• Dependency Tree Analysis
• Error Categorization Frameworks
• Compatibility Matrix Synchronization