Fallback Routing Strategies for PostgreSQL Extension Upgrades #

When an automated PostgreSQL extension upgrade fails a compatibility check mid-pipeline, someone has to decide — in seconds, without a human in the loop — whether to abort, retry, or divert the deploy to a known-good version. Fallback routing is the practice of encoding that decision as a deterministic gate: a router that inspects catalog state, ABI compatibility, and upgrade-path validity, then routes the deploy to the primary target when every check passes and to a pinned stable version when any check fails. Without it, a single unavailable binary or a broken update script leaves the catalog half-migrated and the fleet stalled. This page is for platform engineers and database SREs who need extension upgrades to degrade gracefully to a safe version instead of paging a human at 3 a.m.

Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals — the loader mechanics, control-file structure, and catalog registration this router depends on are governed there, and understanding them is a prerequisite for the routing logic below.

Routing Decision Tree #

The fallback router evaluates a fixed sequence of gates. Each gate is fail-safe: any failed check pivots the deploy to a pinned stable version rather than proceeding on a target that might corrupt the catalog. The order matters — cheapest and most decisive checks run first, so an unavailable target short-circuits before any expensive ABI probing.

Every terminal branch converges on a single telemetry emission point, so the pipeline always records why it routed the way it did, whether the run took the primary or the fallback path.

Prerequisites #

The router fails closed: if any prerequisite is unverifiable, it routes to fallback rather than guessing. Confirm the environment meets these assumptions before wiring it into a pipeline.

• PostgreSQL version: 12 or newer on every node. The transactional-rollback guarantees the dry-run gate leans on are only reliable from 12 onward, and pg_extension_update_paths() — the function that proves an upgrade path exists — has been present since 9.1 but returns path IS NULL for unreachable hops, which the router treats as a hard fallback trigger.
• Python packages: Python 3.8+ with psycopg2-binary (pip install psycopg2-binary) for the routing controller. Only the standard library plus psycopg2 is required. Asynchronous stacks can port the same logic to asyncpg; the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required privileges: A read-only role is sufficient for the dry-run gate and every validation query. The guarded apply and fallback steps need a role that owns the extensions 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 router resolves candidate versions against an authoritative artifact store, not transient upstream mirrors. Keep the version-to-artifact mapping current with Extension Registry Mapping so fallback targets are signed, auditable releases rather than whatever a package feed happens to serve.
• Shared-library reachability: The target .so files must be present in $libdir on the node being upgraded. A binary installed on the primary but missing on a replica is the single most common cause of a fallback that fires only in production.

Core Concept: Transactional Dry-Run Semantics #

The entire strategy rests on one PostgreSQL guarantee: DDL is transactional. CREATE EXTENSION and the catalog-only portions of ALTER EXTENSION ... UPDATE run inside the surrounding transaction, so a ROLLBACK erases every catalog change the statement made. That property lets the router simulate an upgrade — actually issue the DDL, observe whether it succeeds, and then discard the result — without ever mutating committed cluster state.

Fallback routing exploits this in two distinct phases:

1. Probe phase (read-only + rolled-back write). Inside a single transaction the router asserts the target version exists in pg_available_extension_versions, proves an upgrade path exists via pg_extension_update_paths(), executes the real CREATE EXTENSION ... VERSION to force the server to parse and apply the update script, and then ROLLBACKs. If any assertion raises, the transaction aborts and the router has learned the target is unsafe — at zero cost to the live catalog.
2. Commit phase (guarded apply or fallback). Only a clean probe reaches this phase. The router re-issues the DDL in a committing transaction against the real target, or — if the probe failed — issues it against the pinned fallback version instead.

The critical caveat is that transactional safety is not universal. Update scripts that register background workers, allocate shared memory, or touch shared_preload_libraries take effect at a level a plain ROLLBACK cannot undo. The router must classify each target as transaction-safe or not before trusting the probe, and route the non-transactional class through the explicit recovery path described later rather than a naive rollback.

Step-by-Step Implementation #

The following procedure turns the decision tree into a runnable gate. Each step is complete and copy-pasteable; run steps 1–2 with a read-only role and reserve the privileged connection for the guarded apply.

Step 1 — Prove the target version is available and reachable #

Before anything else, confirm the server can actually resolve the requested version and that a path exists from the installed version to it. This runs read-only and decides the first branch of the decision tree:

-- Availability + path check for a single extension/target version.
-- Returns one row per candidate path; zero rows means route to fallback.
WITH target AS (
    SELECT 'pg_stat_statements'::text AS ext_name,
           '1.10'::text              AS target_ver
)
SELECT t.ext_name,
       e.extversion                       AS current_ver,
       t.target_ver,
       EXISTS (
           SELECT 1 FROM pg_available_extension_versions av
           WHERE av.name = t.ext_name AND av.version = t.target_ver
       )                                  AS version_available,
       up.path IS NOT NULL                AS path_reachable
FROM target t
LEFT JOIN pg_extension e
       ON e.extname = t.ext_name
LEFT JOIN LATERAL pg_extension_update_paths(t.ext_name) up
       ON up.source = e.extversion AND up.target = t.target_ver
;

If version_available is false, or the extension is already installed and path_reachable is false, the router does not attempt the primary upgrade at all — it routes straight to fallback.

Step 2 — Run the transactional dry-run gate #

This gate performs the probe phase: it asserts availability and path validity, then forces the server to parse and apply the update script inside a transaction that is always rolled back. PostgreSQL’s transactional DDL guarantees the rolled-back probe leaves zero committed side effects.

-- Dry-run validation gate: executed within a client-managed transaction.
-- The final ROLLBACK guarantees no committed side effects.
BEGIN;
SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '3s';

DO $$
DECLARE
    ext_name    TEXT := 'pg_stat_statements';
    target_ver  TEXT := '1.10';
    current_ver TEXT;
    path_exists BOOLEAN;
BEGIN
    -- 1. Verify the target version exists in the server's extension registry.
    IF NOT EXISTS (
        SELECT 1 FROM pg_available_extension_versions
        WHERE name = ext_name AND version = target_ver
    ) THEN
        RAISE EXCEPTION 'FALLBACK_TRIGGER: Target version % not available in registry', target_ver;
    END IF;

    -- 2. Validate an upgrade path exists from the current state (if installed).
    SELECT extversion INTO current_ver FROM pg_extension WHERE extname = ext_name;
    IF current_ver IS NOT NULL THEN
        SELECT EXISTS (
            SELECT 1 FROM pg_extension_update_paths(ext_name)
            WHERE source = current_ver AND target = target_ver AND path IS NOT NULL
        ) INTO path_exists;

        IF NOT path_exists THEN
            RAISE EXCEPTION 'FALLBACK_TRIGGER: No valid upgrade path from % to %', current_ver, target_ver;
        END IF;
    END IF;

    -- 3. Force the server to parse and apply the update script (rolled back below).
    EXECUTE format('SET LOCAL search_path = public');
    EXECUTE format('CREATE EXTENSION IF NOT EXISTS %I VERSION %L', ext_name, target_ver);

    RAISE NOTICE 'DRY_RUN_PASSED: Extension % version % validated successfully', ext_name, target_ver;
END $$;
ROLLBACK;

A FALLBACK_TRIGGER exception aborts the transaction and tells the routing controller to select the pinned version. A clean DRY_RUN_PASSED notice means the target is safe to commit.

Step 3 — Scan dependencies and validate the ABI #

Extensions rarely stand alone. Before committing, the router parses transitive prerequisites and probes the target .so for missing symbols, because a link-time failure surfaces as a server FATAL at load — long after the catalog probe passed. Systematic prerequisite ordering is the job of Dependency Tree Analysis; the router consumes its resolved order and adds a binary check on top:

#!/usr/bin/env bash
# ABI pre-flight: fail (non-zero) if the target .so has unresolved symbols.
set -euo pipefail

EXT_SO="${1:?usage: abi_check.sh /path/to/extension.so}"

# Any 'not found' line means a required shared library is missing on this node.
if ldd "$EXT_SO" 2>&1 | grep -q 'not found'; then
    echo "FALLBACK_TRIGGER: unresolved shared library for ${EXT_SO}" >&2
    ldd "$EXT_SO" | grep 'not found' >&2
    exit 1
fi

echo "ABI_OK: ${EXT_SO} links cleanly"

If the primary version introduces a breaking ABI change or requires a newer libc than the host provides, this check exits non-zero and the router pivots to the fallback branch before any DDL commits.

Step 4 — The routing controller #

This controller ties the gates together: it runs the probe, and on any failure resolves the highest pinned stable version that satisfies pg_extension_update_paths for the current major version, then applies it. It emits structured telemetry at every branch.

#!/usr/bin/env python3
"""
PostgreSQL extension fallback routing controller.
Runs a transactional dry-run probe; commits the primary on success,
otherwise routes to a pinned stable version. Emits JSON routing telemetry.
Requires: psycopg2-binary (pip install psycopg2-binary)
"""

import json
import sys
import psycopg2
from psycopg2 import sql, errors


def emit(phase, ext, target, resolved=None, reason=None, rollback=None):
    """Structured routing telemetry — one line of JSON per decision."""
    print(json.dumps({
        "routing_phase": phase,
        "extension_name": ext,
        "target_version": target,
        "resolved_version": resolved,
        "failure_reason": reason,
        "rollback_status": rollback,
    }))


def probe(conn, ext, target):
    """Return True if the target upgrade is safe to commit (rolled-back probe)."""
    try:
        with conn.cursor() as cur:
            cur.execute("SET LOCAL statement_timeout = '5s'")
            cur.execute("SET LOCAL lock_timeout = '3s'")
            cur.execute(
                "SELECT 1 FROM pg_available_extension_versions "
                "WHERE name = %s AND version = %s", (ext, target))
            if cur.fetchone() is None:
                emit("dry_run", ext, target, reason="path_missing")
                return False
            cur.execute(
                sql.SQL("CREATE EXTENSION IF NOT EXISTS {} VERSION {}").format(
                    sql.Identifier(ext), sql.Literal(target)))
        emit("dry_run", ext, target, resolved=target)
        return True
    except errors.Error as exc:
        emit("dry_run", ext, target, reason=exc.pgcode or "unknown")
        return False
    finally:
        conn.rollback()  # probe never commits


def pinned_fallback(conn, ext):
    """Highest reachable stable version from the current installed version."""
    with conn.cursor() as cur:
        cur.execute("SELECT extversion FROM pg_extension WHERE extname = %s", (ext,))
        row = cur.fetchone()
        if row is None:
            return None
        current = row[0]
        cur.execute(
            "SELECT target FROM pg_extension_update_paths(%s) "
            "WHERE source = %s AND path IS NOT NULL "
            "AND target NOT LIKE '%%beta%%' AND target NOT LIKE '%%rc%%' "
            "ORDER BY string_to_array(target, '.')::int[] DESC LIMIT 1",
            (ext, current))
        hit = cur.fetchone()
        return hit[0] if hit else current


def commit_upgrade(conn, ext, version):
    with conn.cursor() as cur:
        cur.execute(
            sql.SQL("ALTER EXTENSION {} UPDATE TO {}").format(
                sql.Identifier(ext), sql.Literal(version)))
    conn.commit()


def route(db_uri, ext, target):
    conn = psycopg2.connect(db_uri)
    conn.autocommit = False
    try:
        if probe(conn, ext, target):
            commit_upgrade(conn, ext, target)
            emit("primary_attempt", ext, target, resolved=target, rollback="success")
            return 0
        fallback = pinned_fallback(conn, ext)
        if fallback is None:
            emit("fallback_execution", ext, target,
                 reason="no_stable_target", rollback="manual_intervention_required")
            return 1
        commit_upgrade(conn, ext, fallback)
        emit("fallback_execution", ext, target,
             resolved=fallback, rollback="success")
        return 0  # degraded_stable, not failed
    except errors.Error as exc:
        conn.rollback()
        emit("fallback_execution", ext, target,
             reason=exc.pgcode or "unknown", rollback="partial")
        return 2
    finally:
        conn.close()


if __name__ == "__main__":
    if len(sys.argv) != 4:
        print("usage: route_extension.py <db-uri> <extension> <target-version>",
              file=sys.stderr)
        sys.exit(2)
    sys.exit(route(sys.argv[1], sys.argv[2], sys.argv[3]))

Dry-Run & Validation Gate #

The probe is the safety interlock: it runs the full availability, path, and script-parse checks and then discards them. A clean run emits a machine-readable line your pipeline archives as a deploy artifact:

{
  "routing_phase": "dry_run",
  "extension_name": "pg_stat_statements",
  "target_version": "1.10",
  "resolved_version": "1.10",
  "failure_reason": null,
  "rollback_status": null
}

Gate the committing apply on three conditions before promoting to production:

• The probe emitted a non-null resolved_version. A failure_reason of path_missing, an SQLSTATE, or unknown means the target is unsafe and the controller has already selected fallback.
• The ABI check exited 0. A binary that fails ldd will pass the catalog probe and still FATAL at load, so the shared-library scan is a separate, mandatory gate.
• The resolved version matches the change request. If the controller routed to fallback, the deploy is degraded_stable — allowed to proceed, but flagged for a follow-up patch cycle rather than silently accepted as success.

For fleet-wide upgrades, reconcile the resolved plan against the Compatibility Matrix Synchronization source of truth so a target that is safe on one major version is not blindly routed to on another.

Failure Modes & Error Taxonomy #

Routing fails in a small, well-defined set of ways. Each has a distinctive SQLSTATE, log signature, or exit code, and each maps to a specific branch of the router and a recovery action.

When these cluster during a batch upgrade, route them into a structured classifier rather than reading logs by hand — Error Categorization Frameworks turns these SQLSTATEs into automated triage signals that can themselves trigger a fallback branch.

Rollback & Recovery Path #

The router’s per-decision conn.commit() boundary means a transaction-safe upgrade that fails mid-flight leaves only the failed statement uncommitted — abort and the node is untouched. The recovery path only becomes non-trivial for the non-transactional class of updates.

For a transaction-safe update, verify inside the block and roll back on any doubt:

-- Catalog-only update: the transaction never commits, so recovery is free.
BEGIN;
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';
-- run a health check against the extension's functions here; on any doubt:
ROLLBACK;

For a non-transactional update — one that registered a background worker, allocated shared memory, or changed shared_preload_libraries — a plain ROLLBACK is not enough. Follow this deterministic sequence:

1. Snapshot the pre-upgrade version set. Capture SELECT extname, extversion FROM pg_extension as a deploy artifact before touching anything — it is the target state the rollback restores to.
2. Wrap the attempt in a SAVEPOINT. On failure, ROLLBACK TO SAVEPOINT and immediately issue the fallback ALTER EXTENSION ... UPDATE TO '<stable_version>'.
3. Disable concurrent DDL sources. Pause conflicting event_triggers and any pg_cron jobs during the routing window so nothing races the fallback.
4. Re-register the prior shared library and restart the node so shared_preload_libraries loads the correct binary when no in-catalog downgrade script exists.
5. Restore from a verified pre-upgrade snapshot when no downgrade path exists at all — drive that restore through Snapshot & Point-in-Time Recovery.

When routing experimental or beta extensions, keep the fallback traffic inside strict isolation boundaries — dedicated schemas or read-only replicas — so a failed probe never pollutes the primary catalog. The full sandboxing procedure is in Best Practices for Isolating Experimental Extensions. Pairing every rollback target with Version Control & Branching makes each recovery a reproducible, reviewable change rather than an improvised fix.

Telemetry & Performance Considerations #

Deterministic routing needs an observable feedback loop. Every decision emits a structured line carrying routing_phase (dry_run, dependency_scan, primary_attempt, fallback_execution), the extension and both target and resolved versions, a failure_reason (abi_mismatch, catalog_drift, path_missing, timeout), and a rollback_status (success, partial, manual_intervention_required). Pipelines parse these to choose their own downstream branch: a fallback_execution with rollback_status = success marks the deploy degraded_stable rather than failed, letting a later patch cycle address the root cause.

The routing logic itself is cheap; the cost lives in the DDL it schedules and the probes it runs.

• Probe overhead: Each transactional probe opens a short-lived transaction and applies the update script, then discards it. On catalog-only extensions this is sub-second, but the probe holds the same locks the real apply would, so run it during the same low-traffic window you intend to apply in.
• Lock contention: ALTER EXTENSION on widely-used types or operators briefly blocks DML. The lock_timeout in the gate bounds that wait and turns an indefinite block into a clean, retryable 55P03 — tune the window per Threshold Tuning for Downtime Windows.
• Fleet parallelism: Probe and dry-run every node in parallel, but apply in a controlled rollout. The controller is idempotent — an extension already at target is skipped — so re-running across a partially-routed fleet converges safely.
• Repeated fallback alerting: Alert on the same extension tripping fallback across multiple runs. A recurring fallback is rarely a one-off; it usually signals an upstream packaging regression or a PostgreSQL cluster-version incompatibility that no amount of retrying will fix. Validate suspects against a production-fidelity replica per Test Environment Routing.

FAQ #

Related Pages #

• Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals
• Best Practices for Isolating Experimental Extensions
• Dependency Tree Analysis
• Extension Registry Mapping
• Security Boundaries & Permissions
• Snapshot & Point-in-Time Recovery