Idempotent Migration Orchestration #

An automated upgrade pipeline retries — a runner dies mid-run, a network blip re-queues a job, an operator re-triggers a stuck deploy — and every retry re-executes migration steps that already ran. If those steps are not idempotent, the second run collides with the first: CREATE EXTENSION fails because it already exists, an ALTER re-applies a change that is no longer valid, a partition is created twice. This guide is for platform engineers building the orchestration layer that runs extension transitions across a fleet, and it shows how to make every step safe to re-run so a retried pipeline converges on the intended state rather than erroring or corrupting it. The whole discipline reduces to one property: running the migration once and running it five times must leave the catalog identical.

Up: Automated Execution & Rollback Workflows — idempotency is the property that makes the execution engine’s retries safe, and this guide assumes that engine’s transactional model.

The Convergence Loop #

An idempotent orchestrator reads the current state, computes the diff to the desired state, applies only what is missing, and re-checks — so a re-run with nothing left to do is a clean no-op.

Each box is guarded by the diff, so re-entry is safe: a retried run re-observes, finds less (or nothing) to do, and applies only the remainder. That is what turns a fragile “run exactly once” pipeline into a robust “run until converged” one.

Prerequisites #

• PostgreSQL version: 12 or newer. The guards here use CREATE EXTENSION IF NOT EXISTS, pg_extension/pg_available_extension_versions reads, and advisory locks, all stable across 12–17.
• Python packages: Python 3.8+ with psycopg2-binary. The orchestrator is a thin state machine over catalog reads and a small set of guarded writes.
• Required privileges: Whatever the underlying transition needs (extension install/update rights), plus the ability to take a session-level advisory lock. A read-only role covers the observe and diff stages; reserve write access for apply, per Security Boundaries & Permissions.
• Catalog state: A desired-state definition pinned per Pinning Extension Versions in Deployment Manifests, so “desired” is a versioned artifact rather than an implicit assumption.

Core Concept: Guard Every Write on Observed State #

Idempotency is not a property of a command; it is a property of a command plus its guard. ALTER EXTENSION ext UPDATE TO '3.4.2' is not idempotent on its own — run it twice and the second call errors because the version is already 3.4.2 (no update path from 3.4.2 to 3.4.2). Wrap it in a guard that reads the installed version first and skips when it already matches, and the pair becomes idempotent: the effect of running it N times equals running it once. Every step in an extension migration must be expressed as such a guarded pair — observe the catalog, act only on the gap.

Two mechanisms make this robust under concurrency. Native guards — CREATE EXTENSION IF NOT EXISTS, DROP ... IF EXISTS — let PostgreSQL do the check-and-act atomically for the cases it supports. Advisory locks serialize the orchestrator so two runners cannot both observe “not yet applied” and both apply; the second waits, re-observes, and finds nothing to do. Together they let a retried pipeline converge instead of collide — the same fail-safe philosophy the compatibility validation pipeline applies before execution, extended into the execution layer itself. Steps that touch shared, non-transactional state still need the care from ALTER EXTENSION automation, because a guard cannot roll back a background-worker registration.

Step-by-Step Implementation #

Step 1 — Serialize runners with an advisory lock #

Take a session advisory lock keyed to the extension so only one orchestrator acts at a time. A second runner blocks, then re-observes an already-converged state.

#!/usr/bin/env python3
"""Serialize migration runners on a per-extension advisory lock."""
import hashlib
import psycopg2


def lock_key(extname: str) -> int:
    # Stable 63-bit signed key from the extension name.
    h = hashlib.sha256(extname.encode()).digest()
    return int.from_bytes(h[:8], "big", signed=True)


def with_extension_lock(conn, extname: str):
    with conn.cursor() as cur:
        cur.execute("SELECT pg_advisory_lock(%s);", (lock_key(extname),))
    return conn  # release with pg_advisory_unlock or at session end

Step 2 — Observe and diff against the desired version #

Read the installed version and compute whether any action is needed. This is the guard every write hangs off.

def diff_to_desired(conn, extname: str, desired_version: str) -> dict:
    with conn.cursor() as cur:
        cur.execute("SELECT extversion FROM pg_extension WHERE extname = %s",
                    (extname,))
        row = cur.fetchone()
        installed = row[0] if row else None

        reachable = True
        if installed is not None and installed != desired_version:
            cur.execute(
                "SELECT 1 FROM pg_available_extension_versions "
                "WHERE name = %s AND version = %s", (extname, desired_version))
            reachable = cur.fetchone() is not None

    if installed == desired_version:
        return {"action": "noop", "installed": installed}
    if installed is None:
        return {"action": "install", "to": desired_version}
    if not reachable:
        return {"action": "block", "reason": "desired version unreachable",
                "installed": installed}
    return {"action": "update", "from": installed, "to": desired_version}

Step 3 — Apply only the gap, idempotently #

Execute exactly the action the diff named, using native guards where they exist. A noop is a clean, successful no-op — the property that makes retries safe.

def apply(conn, extname: str, desired_version: str) -> dict:
    plan = diff_to_desired(conn, extname, desired_version)
    if plan["action"] in ("noop", "block"):
        return plan
    with conn.cursor() as cur:
        if plan["action"] == "install":
            # Native idempotent guard: safe even if a racing runner created it.
            cur.execute(
                f'CREATE EXTENSION IF NOT EXISTS "{extname}" VERSION %s',
                (desired_version,))
        else:  # update
            cur.execute(
                f'ALTER EXTENSION "{extname}" UPDATE TO %s', (desired_version,))
    conn.commit()
    # Re-observe to confirm convergence.
    return diff_to_desired(conn, extname, desired_version)

Dry-Run & Validation Gate #

The orchestrator’s dry run is simply the diff without the apply: it reports what would change and asserts convergence is reachable, emitting a machine-readable verdict the pipeline archives.

{
  "extension": "postgis",
  "action": "update",
  "from": "3.4.1",
  "to": "3.4.2",
  "idempotent": true
}

Gate the apply on two conditions: the action is not block (the desired version is reachable), and re-running the diff after a simulated apply returns noop. If a second diff does not converge to noop, a step is not truly idempotent — find it before production, exactly as Async Upgrade Simulation surfaces non-transactional steps. A converged dry run is the green light.

Failure Modes & Error Taxonomy #

Route recurring signals through Error Categorization Frameworks so a duplicate-object from a benign retry is not paged like a real fault.

Rollback / Recovery Path #

Idempotency and rollback are complementary: idempotency makes forward retries safe; rollback handles the steps a retry cannot fix.

1. Prefer convergence over rollback. For a transactional step that half-ran, the cheapest recovery is to re-run the orchestrator — it re-observes and finishes the gap. No rollback is needed.
2. Snapshot before non-idempotent steps. For a step that commits shared state, take a restore point first via Snapshot & Point-in-Time Recovery, because a retry cannot undo it.
3. Route irreversible damage to the right rollback. If a retry cannot converge, decide between a scoped reversal and a full rewind per PITR Restore vs Schema-Level Rollback.
4. Keep the desired-state artifact versioned. Roll forward or back by changing the pinned desired version under Version Control & Branching, so the orchestrator always converges to a reviewed target.

Performance & Scale Considerations #

• The advisory lock is per-extension, not global. Keying the lock on the extension name lets different extensions converge in parallel across the fleet while still serializing runners for the same extension.
• Observe is cheap; keep it that way. The diff is a couple of catalog reads. Run it liberally — before every apply and after — because its cost is negligible against the safety it buys.
• Batch idempotent applies within a maintenance window. Because each step is a guarded no-op when already done, you can safely re-run the whole batch across many databases; size the genuinely-changing steps with Threshold Tuning for Downtime Windows.
• Beware lock waits masquerading as hangs. A second runner blocked on the advisory lock looks stalled; bound the wait with a lock_timeout so a stuck holder does not freeze the fleet.

FAQ #

Related Pages #

• Up: Automated Execution & Rollback Workflows
• Writing Idempotent CREATE EXTENSION Guards
• ALTER EXTENSION Automation
• Blue-Green Extension Deployment
• Snapshot & Point-in-Time Recovery