Version Control & Branching for PostgreSQL Extension Upgrades #

Managing PostgreSQL extensions in production means treating database artifacts with the same rigor as application code — but a Git branch that flips a stateless binary cannot, on its own, mutate pg_catalog, register a shared library, or shift query-planner behavior on a running cluster. That gap is where uncontrolled ALTER EXTENSION rollouts, schema drift, and un-reviewed downgrade paths creep in. This page is for platform engineers and database SREs who need a branching model where every extension version bump is a reviewable, dry-run-validated, revertible change rather than a hopeful manual edit applied straight to production. It anchors that model to PostgreSQL Extension Architecture & Lifecycle Fundamentals, which governs how control files, shared library loading, and catalog registration actually behave.

The core problem is that Git tracks the intended state of an extension — its control file and migration scripts — while the live catalog holds the actual state. Branching discipline only pays off when the pipeline reconciles the two on every merge, and refuses to promote a branch whose upgrade path has not executed against a catalog-identical replica.

Branching Pipeline at a Glance #

Every extension change flows through the same fail-fast pipeline: a short-lived branch, a static gate, a transactional dry-run against a staging replica, and only then a guarded apply. Any stage that detects a missing downgrade script, a catalog drift, or a non-zero dry-run exit stops the deploy before DDL touches production.

Prerequisites #

The branching workflow below assumes the environment can reproduce production catalog state on demand. It is deliberately conservative and fails closed whenever an assumption cannot be verified.

• PostgreSQL version: 12 or newer on every node. The transactional dry-run relies on ALTER EXTENSION UPDATE running inside the caller’s transaction so its catalog changes can be rolled back; that behavior is only dependable from 12 onward, and extnamespace::regnamespace schema reporting needs 9.5+.
• Python packages: Python 3.8+ with psycopg2-binary (pip install psycopg2-binary). The wrapper uses only the standard library plus psycopg2. If your automation stack is asynchronous, the same logic ports to asyncpg — the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required privileges: A read-only role suffices for static analysis; the dry-run and apply need a role that owns the extension or holds the grants each ALTER EXTENSION demands — often SUPERUSER for C-language extensions. Scope those grants tightly per Security Boundaries & Permissions.
• Catalog state: The CI runner must provision an ephemeral instance matching the exact major version of the target cluster, restored to production’s installed extension set. Drift between pg_available_extensions on the runner and production is the most common cause of a branch that passes staging and fails in production; keep the mapping authoritative with Extension Registry Mapping.

Core Concept: Branching Stateful Database Objects #

Extension version bumps follow a trunk-based model with short-lived feature branches. Long-lived divergent histories are the enemy here: two branches that each edit the same --<from>--<to>.sql migration produce a merge whose resulting upgrade path was never actually executed anywhere. Each branch must encapsulate three artifacts that move together:

1. The extension control file (.control) whose default_version is the single source of truth for the target version.
2. The upgrade and downgrade SQL scripts (<ext>--<from>--<to>.sql and the reverse), so every forward hop has a backward hop.
3. A compatibility manifest mapping target PostgreSQL major versions to supported extension releases. This manifest feeds Extension Registry Mapping so the runner resolves the correct binary and SQL payload for each target cluster, and it is reconciled fleet-wide against the Compatibility Matrix Synchronization source of truth.

Transactional vs non-transactional ALTER semantics #

The property that makes a dry-run possible is that ALTER EXTENSION UPDATE executes its catalog changes inside the calling transaction. Run it, verify pg_extension.extversion, then ROLLBACK, and the catalog is untouched — you have validated the upgrade path without persisting it.

The critical exception is that some upgrade scripts have effects a ROLLBACK cannot reverse: registering a background worker, allocating shared memory, or changing shared_preload_libraries. Those take effect at a level outside transactional control. The branching rule that follows from this: a branch whose upgrade script touches any non-transactional surface must carry an explicit, tested downgrade script and a snapshot reference — a plain rollback is not a recovery plan for it. Distinguishing the two classes per branch is what keeps the recovery path deterministic, and it is the same distinction that governs Dependency Tree Analysis when it schedules ordered DDL.

Branch protection rules #

Branch protection is where the model becomes enforceable rather than aspirational. Require, on every branch that touches an extension:

• Semantic version tagging (v1.2.0) that strictly matches default_version in the .control file — a tag/control mismatch is an automatic rejection.
• Static SQL validation via pg_dump --schema-only dry-runs and plpgsql_check for procedural logic.
• A present downgrade path. Every forward migration script must have a matching backward script. A missing downgrade hop fails the pipeline before it ever reaches the dry-run stage.

Step-by-Step Implementation #

The following procedure turns the branching rules into a runnable gate. Each step is complete and copy-pasteable.

Step 1 — Assert the branch’s version contract #

Before any database work, confirm the branch is internally consistent: the Git tag, the .control file’s default_version, and a matching pair of upgrade/downgrade scripts must all agree. Run this in the CI runner’s checkout:

#!/usr/bin/env bash
# Fail the branch if the version contract is inconsistent.
set -euo pipefail

EXT="$1"                      # e.g. pg_partman
CONTROL="./${EXT}.control"
MIGR_DIR="./migrations"

# default_version declared in the control file
TARGET_VER="$(grep -oP "default_version\s*=\s*'\K[^']+" "$CONTROL")"

# The current git tag must match the control file's default_version.
GIT_TAG="$(git describe --tags --exact-match 2>/dev/null | sed 's/^v//' || true)"
if [[ -n "$GIT_TAG" && "$GIT_TAG" != "$TARGET_VER" ]]; then
  echo "REJECT: git tag ($GIT_TAG) != control default_version ($TARGET_VER)" >&2
  exit 1
fi

# Every forward migration into TARGET_VER must have a matching downgrade script.
shopt -s nullglob
for up in "$MIGR_DIR/${EXT}"--*--"${TARGET_VER}.sql"; do
  from_ver="$(basename "$up" | sed -E "s/^${EXT}--(.+)--${TARGET_VER}\.sql$/\1/")"
  down="$MIGR_DIR/${EXT}--${TARGET_VER}--${from_ver}.sql"
  if [[ ! -f "$down" ]]; then
    echo "REJECT: missing downgrade script $down" >&2
    exit 1
  fi
done
echo "OK: version contract for $EXT -> $TARGET_VER is consistent"

Step 2 — Capture the baseline installed state #

The installed set is both the input that decides whether each hop is a CREATE or an ALTER, and the exact state a rollback must restore to. Archive it as a deploy artifact:

SELECT extname,
       extversion,
       extnamespace::regnamespace::text AS schema
FROM pg_extension
ORDER BY extname;

Step 3 — Run the transactional dry-run #

This wrapper drives the upgrade through ALTER EXTENSION on an ephemeral instance, verifies the post-upgrade catalog version, then rolls back so no state is persisted. It resolves the upgrade script by branch convention and fails closed on any doubt.

import os
import logging
import psycopg2
from psycopg2 import sql

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

def execute_extension_dry_run(
    ext_name: str,
    target_version: str,
    conn_str: str = "",
    script_dir: str = "./migrations"
) -> bool:
    """
    Executes a transactional dry-run of an extension upgrade.
    Validates catalog mutations and script resolution without persisting
    changes to the database.
    """
    if not conn_str:
        conn_str = os.getenv("PG_DSN")
    if not conn_str:
        raise ValueError("PG_DSN environment variable or conn_str argument is required.")

    conn = psycopg2.connect(conn_str)
    try:
        cur = conn.cursor()

        # Verify the extension exists and fetch its current version.
        cur.execute(
            "SELECT extversion FROM pg_extension WHERE extname = %s",
            (ext_name,)
        )
        row = cur.fetchone()
        if not row:
            raise RuntimeError(f"Extension '{ext_name}' is not installed in the target database.")

        current_version = row[0]
        if current_version == target_version:
            logging.info(f"Extension '{ext_name}' is already at version {target_version}. Skipping dry-run.")
            return True

        # Confirm the branch ships the expected upgrade script artifact.
        upgrade_script = os.path.join(script_dir, f"{ext_name}--{current_version}--{target_version}.sql")
        if not os.path.exists(upgrade_script):
            raise FileNotFoundError(f"Missing upgrade script: {upgrade_script}")

        logging.info(f"Starting transactional dry-run: {ext_name} {current_version} -> {target_version}")

        # Drive the upgrade through ALTER EXTENSION so PostgreSQL records the new
        # version in pg_extension. Executing the raw script body by hand would
        # mutate objects without updating the catalog version.
        # psycopg2 automatically begins a transaction on first execute.
        cur.execute(
            sql.SQL("ALTER EXTENSION {} UPDATE TO {}").format(
                sql.Identifier(ext_name), sql.Literal(target_version)
            )
        )

        # Verify the post-upgrade catalog state.
        cur.execute(
            "SELECT extversion FROM pg_extension WHERE extname = %s",
            (ext_name,)
        )
        actual_version = cur.fetchone()[0]
        if actual_version != target_version:
            raise RuntimeError(f"Version mismatch after dry-run. Expected {target_version}, got {actual_version}.")

        logging.info("Dry-run validation passed. Rolling back transaction to preserve catalog state.")
        conn.rollback()
        return True

    except psycopg2.Error as e:
        logging.error(f"PostgreSQL error during dry-run: {e}")
        conn.rollback()
        return False
    except Exception as e:
        logging.error(f"Execution error: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()

Step 4 — Author idempotent, reversible migration SQL #

The dry-run only proves what the upgrade script does; the script itself must be written to be safe and reversible. Prefer declarative CREATE OR REPLACE for functions, DO blocks for conditional setup, and explicit DROP sequences for deprecated objects over direct catalog manipulation. When authoring an upgrade .sql file:

1. Validate prerequisites by checking pg_available_extension_versions and pg_extension before applying schema changes, so a partially-provisioned node fails early.
2. Wrap destructive operations in explicit savepoints where partial rollback within the hop is required.
3. Avoid implicit type coercion that could invalidate prepared statements or break existing query plans.
4. Document side effects in the .control file’s comment field, giving runtime visibility into behavioral changes.

Step 5 — Gate the pipeline on the dry-run #

Invoke the dry-run as an isolated stage. The privileged apply against production is reached only when the gate exits zero:

# Gate: transactional dry-run on the ephemeral, catalog-identical instance.
python3 -c "
import sys
from dry_run import execute_extension_dry_run
ok = execute_extension_dry_run('pg_partman', '5.1.0',
    conn_str='postgresql://ci@staging:5432/appdb', script_dir='./migrations')
sys.exit(0 if ok else 1)
"

# Apply: only reached if the gate above exited 0 (privileged role).
python3 apply_upgrade.py \
  --db-uri "postgresql://deployer@prod:5432/appdb" \
  --ext pg_partman --target 5.1.0

Dry-Run & Validation Gate #

A dry-run that returns True is necessary but not sufficient to promote a branch. Gate the apply on three independent conditions and archive the evidence as a deploy artifact:

• The dry-run exited zero. A non-zero exit means the catalog version did not match after ALTER EXTENSION UPDATE, the upgrade script was missing, or PostgreSQL raised an error mid-hop.
• The plan diff is expected. Store the resolved upgrade path and diff it against the previous run. An unexpected reorder or an extra hop usually means a requires declaration or the compatibility manifest drifted, and should block review.
• The baseline snapshot exists. The Step 2 artifact must be present, because it is the state your rollback restores to.

A clean gate emits a machine-readable record your pipeline can attach to the merge:

{
  "extension": "pg_partman",
  "from_version": "5.0.1",
  "to_version": "5.1.0",
  "dry_run": "passed",
  "transactional": true,
  "downgrade_script": "pg_partman--5.1.0--5.0.1.sql",
  "baseline_snapshot": "snap-2026-07-04T09-12Z"
}

Comparing the live pg_extension catalog against this Git-tracked contract is also how automated drift detection catches an out-of-band CREATE EXTENSION or a manual ALTER that bypassed the pipeline entirely — reconcile it against the deployed ledger described in Tracking Extension Lifecycle States in Production.

Failure Modes & Error Taxonomy #

Branch validation fails in a small, well-defined set of ways. Each has a distinctive signal — a SQLSTATE, a log line, or a catalog state — and a specific recovery.

When failures cluster across a batch of branches, route the SQLSTATEs into a classifier rather than reading logs by hand — the taxonomy in Error Categorization Frameworks turns them into automated triage signals.

Rollback & Recovery Path #

Because the dry-run runs on an ephemeral instance, a failed dry-run needs no recovery — the instance is discarded. Recovery matters only when a validated branch fails during the real apply to production. The path depends on whether the hop was transactional.

For a transactional update that fails mid-flight, the apply’s own transaction boundary means nothing committed — abort and the node is untouched:

-- Catalog-only hop: fails cleanly because the transaction never commits.
BEGIN;
ALTER EXTENSION pg_partman UPDATE TO '5.1.0';
-- verify here; on any doubt:
ROLLBACK;

For a non-transactional update, ROLLBACK is not enough. Run the deterministic sequence:

1. Halt dependent workloads so no session writes against the half-migrated extension.
2. Apply the branch’s downgrade script — ALTER EXTENSION X UPDATE TO '<prior_version>' — which Step 1 guaranteed exists for this hop.
3. Re-register the prior shared library at the OS layer and restart the node so shared_preload_libraries loads the correct binary.
4. Restore from the verified pre-upgrade snapshot when no downgrade path is safe. Drive that restore through Snapshot & Point-in-Time Recovery, and let the automated recovery routing in Fallback Routing Strategies select the path.

Every one of these steps restores toward the Step 2 baseline artifact, which is precisely why capturing it is mandatory rather than optional — the branch defines the forward change, and the baseline defines the state it reverts to.

Performance & Scale Considerations #

The branching and validation logic is effectively free; the cost lives entirely in the DDL a merged branch schedules against production.

• Lock contention: ALTER EXTENSION UPDATE acquires locks on the objects it modifies. For extensions that alter widely-used types or operators, that briefly blocks DML — sequence the apply during a low-traffic window and keep each hop’s transaction short so lock hold time is bounded.
• Downtime estimates: Catalog-only hops are typically sub-second. Hops that rebuild indexes, rewrite tables, or require a restart to reload shared_preload_libraries are the ones to budget for. Measure them on the ephemeral instance, publish the number, and tune the window per Threshold Tuning for Downtime Windows.
• Fleet parallelism: Dry-run every node’s branch in parallel, but apply in a controlled rollout. The dry-run is idempotent — it skips any extension already at the target version — so re-running across a partially-upgraded fleet converges each node without redoing completed work.
• Staging fidelity: The dominant scale risk is topology drift, not throughput. Validate against an instance that mirrors production, as described in Test Environment Routing.

FAQ #

Related Pages #

• Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals
• Tracking Extension Lifecycle States in Production
• Dependency Tree Analysis
• Extension Registry Mapping
• Security Boundaries & Permissions
• ALTER EXTENSION Automation