Snapshot & Point-in-Time Recovery for PostgreSQL Extension Upgrades #

When ALTER EXTENSION UPDATE registers a background worker, changes shared_preload_libraries, or issues its own internal COMMIT, a transactional ROLLBACK no longer restores prior state — the catalog is advanced but the runtime is half-configured, and there is nothing left to unwind. This is the failure class that snapshot capture and point-in-time recovery (PITR) exist to cover: a WAL-anchored rewind that survives an immediate-commit step, a crashed backend, or a mismatched shared library. This page is for database SREs and platform engineers who need every extension upgrade to carry a deterministic recovery floor — a labelled restore point taken before the first statement, a verified base backup behind it, and an automated replay path the pipeline can trigger without hand-editing the catalog.

Up: Automated Execution & Rollback Workflows — the parent process that decides whether a transition is releasable; this page owns the mechanics of the rewind anchor it depends on, capturing the restore point and replaying WAL when the health gate rejects an upgrade.

Recovery Workflow at a Glance #

The three phases below turn an extension upgrade into a recoverable operation, with an automated PITR path if production health checks fail.

Prerequisites #

The recovery model below fails closed: any precondition it cannot positively verify — an inactive archiver, an unreplayable backup, a missing restore point — blocks the upgrade rather than proceeding on the assumption it will be fine.

• PostgreSQL version: 12 or newer. The single-file recovery configuration used here (recovery.signal plus restore_command in postgresql.auto.conf) replaced the standalone recovery.conf file in PostgreSQL 12; on 11 and earlier the same parameters live in recovery.conf instead. pg_verifybackup is available from 13 onward.
• WAL archiving active. PITR replays WAL forward from a base backup, so archive_mode must be on and archive_command (or an archive library) must be reliably shipping completed segments to durable storage. A restore point in a database cluster that is not archiving is a marker pointing at WAL that no longer exists.
• Python packages: Python 3.8+ for the dry-run harness, which drives the local PostgreSQL binaries through subprocess and needs only the standard library. The orchestration wrapper that captures the anchor before the apply is covered under ALTER EXTENSION automation.
• Required privileges: pg_create_restore_point(), pg_switch_wal(), and pg_basebackup require a superuser or a role granted the pg_checkpoint / replication attributes as appropriate. Keep the acting role least-privilege and out of the deploy path per Security Boundaries & Permissions — the restore anchor and the backup behind it are themselves an access-controlled surface.
• Catalog & storage state: enough retained WAL and a base backup old enough to replay from the marker must both exist before the upgrade starts; the extension’s dependency graph should already be resolved per Dependency Tree Analysis so the snapshot captures a consistent, restorable baseline.

Core Concept: How a Restore Point Anchors a Rewind #

PITR is not a copy of the database; it is a replay. Understanding three mechanics tells you exactly why the restore point must be captured before the transition and why it recovers states that ROLLBACK cannot.

A base backup plus continuous WAL reconstructs any intermediate state. A physical base backup (pg_basebackup) is a byte-consistent copy of the data directory taken at a known WAL location. Every change after that location is durably recorded in the write-ahead log, as documented in the official PostgreSQL Continuous Archiving guide. Recovery starts from the base backup and replays archived WAL forward; because the log is a complete ordered record of every block change, PostgreSQL can stop replay at any point in that stream and reconstruct the exact catalog and heap state that existed at that instant.

A named restore point is a labelled stop target in the WAL stream. pg_create_restore_point('pre_ext_upgrade') writes a marker record into the WAL at the current location. During recovery, recovery_target_name tells PostgreSQL to replay forward and halt precisely at that marker rather than at a wall-clock time or transaction ID. This is what makes the rewind deterministic: the target is the exact byte position of the known-good state immediately before the first ALTER EXTENSION statement, not an approximate timestamp that might land mid-transition.

This is the only rewind that survives an immediate-commit failure. A transactional ROLLBACK covers catalog-only DDL. But once an upgrade script has registered a background worker or allocated shared memory, that action has committed and is invisible to ROLLBACK. Replaying WAL up to a marker taken before the script ran reconstructs the state as if the commit never happened — the worker is un-registered, the shared memory reclaimed — because recovery simply never replays the WAL records that registered it. Capture the marker late, after the failure, and you have anchored to the broken state instead.

Step-by-Step Implementation: Capturing the Pre-Upgrade Anchor #

The capture procedure establishes the recovery floor before any statement mutates the catalog. It verifies archiving is live, forces a WAL boundary, writes the marker, takes a stream-consistent base backup, and tags the snapshot with the exact restore-point name so the recovery path can find its target without guessing.

Step 1 — Verify archiving and create the restore point #

Prove archive_mode is on, switch to a fresh WAL segment so the marker sits on a clean boundary, and create the named restore point. A restore point in a non-archiving cluster is worthless, so this check is a hard gate.

#!/usr/bin/env bash
set -euo pipefail

DB_NAME="${1:-production_db}"
PG_USER="${2:-postgres}"
SNAPSHOT_DIR="/var/lib/pg_snapshots/pre_upgrade_$(date +%s)"
RESTORE_POINT="pre_ext_upgrade_$(date +%s)"

# Verify WAL archiving is active (mandatory for PITR)
ARCHIVE_STATUS=$(psql -U "$PG_USER" -d "$DB_NAME" -tAc "SHOW archive_mode;")
if [[ "$ARCHIVE_STATUS" != "on" ]]; then
  echo "[FATAL] archive_mode must be 'on' to enable PITR. Aborting."
  exit 1
fi

# Force WAL segment switch and create a named restore point
psql -U "$PG_USER" -d "$DB_NAME" -c "SELECT pg_switch_wal();"
psql -U "$PG_USER" -d "$DB_NAME" -c "SELECT pg_create_restore_point('${RESTORE_POINT}');"

Step 2 — Take a stream-consistent base backup and tag it #

pg_basebackup with --wal-method=stream streams the WAL generated during the backup alongside the data files, guaranteeing the snapshot is self-consistent and immediately replayable. Persisting the restore-point name into the snapshot directory means the recovery path never has to reconstruct which marker to target.

# Capture stream-consistent base backup (PostgreSQL 10+ syntax)
pg_basebackup \
  -D "$SNAPSHOT_DIR" \
  -Ft -z -P \
  --wal-method=stream \
  -R \
  --checkpoint=fast \
  -U "$PG_USER"

# Tag snapshot with recovery metadata
echo "restore_point=${RESTORE_POINT}" > "${SNAPSHOT_DIR}/recovery_metadata.txt"
echo "[SUCCESS] Snapshot captured at ${SNAPSHOT_DIR} with restore point: ${RESTORE_POINT}"

The -R flag writes primary_conninfo to postgresql.auto.conf and creates a standby.signal file, which is what you want when the snapshot seeds a streaming replica. For a standalone PITR restore you will instead inject a restore_command pointing at your WAL archive and use a recovery.signal file — the distinction is exactly what the recovery path in Step 3 handles. Reconcile the on-disk extension artifacts against the catalog while the baseline is fresh, using Extension Registry Mapping, so a restore never resurrects a .so that no longer matches the recorded version.

Step 3 — Verify backup integrity before trusting it #

A backup you have not verified is a hope, not a floor. Run pg_verifybackup against the manifest before the upgrade proceeds; a corrupt or truncated backup discovered at recovery time is an unrecoverable incident.

# A base backup is only a recovery floor once its manifest verifies clean.
pg_verifybackup "$SNAPSHOT_DIR" \
  && echo "[SUCCESS] Backup manifest verified; anchor is replayable." \
  || { echo "[FATAL] Backup verification failed; do not proceed."; exit 1; }

With the anchor captured, verified, and tagged, the pipeline may hand control to the ALTER EXTENSION automation wrapper that performs the actual upgrade behind this recovery floor.

Dry-Run & Validation Gate #

Before committing an upgrade to production, prove that both the upgrade path and the recovery path work against the captured snapshot. A Python orchestration layer restores the snapshot into an ephemeral instance, replays to the exact restore point, runs the dry-run upgrade there, and asserts the catalog reached the target version — validating the rewind mechanics as a side effect. This gate aligns with the transactional-safety analysis in ALTER EXTENSION automation: if the ephemeral restore cannot even reach the marker, the production recovery path is not armed and the deployment must block.

import os
import subprocess
import tempfile
import time
import sys

PG_BIN = "/usr/lib/postgresql/16/bin"
PG_PORT = 5433

def run_cmd(cmd: list[str], check: bool = True) -> subprocess.CompletedProcess:
    """Execute command with explicit error trapping."""
    result = subprocess.run(cmd, capture_output=True, text=True)
    if check and result.returncode != 0:
        print(f"[ERROR] Command failed: {' '.join(cmd)}\n{result.stderr}")
        sys.exit(result.returncode)
    return result

def validate_extension_version(conn_str: str, ext_name: str, expected_version: str) -> bool:
    """Verify extension version matches target after dry-run."""
    query = f"SELECT extversion FROM pg_extension WHERE extname = '{ext_name}';"
    result = run_cmd(["psql", conn_str, "-tAc", query])
    return result.stdout.strip() == expected_version

def main():
    snapshot_dir = sys.argv[1]
    ext_name = sys.argv[2]
    target_version = sys.argv[3]

    with tempfile.TemporaryDirectory() as pg_data:
        # 1. Extract base backup (ensure pg_wal exists before extracting WAL)
        run_cmd(["tar", "-xzf", f"{snapshot_dir}/base.tar.gz", "-C", pg_data])
        os.makedirs(f"{pg_data}/pg_wal", exist_ok=True)
        run_cmd(["tar", "-xzf", f"{snapshot_dir}/pg_wal.tar.gz", "-C", f"{pg_data}/pg_wal"])

        # 2. Configure ephemeral instance for standalone recovery. Read the
        #    exact restore point captured in Phase 1 so the target name matches.
        with open(f"{snapshot_dir}/recovery_metadata.txt") as meta:
            restore_point = meta.read().split("=", 1)[1].strip()
        with open(f"{pg_data}/postgresql.auto.conf", "a") as f:
            f.write("restore_command = 'cp /mnt/wal_archive/%f %p'\n")
            f.write(f"recovery_target_name = '{restore_point}'\n")
            f.write("recovery_target_action = 'promote'\n")
        open(f"{pg_data}/recovery.signal", "w").close()

        # 3. Start ephemeral PostgreSQL
        run_cmd([f"{PG_BIN}/pg_ctl", "-D", pg_data, "-o", f"-p {PG_PORT}", "start"])

        try:
            conn_str = f"postgresql://postgres@localhost:{PG_PORT}/production_db"

            # Poll until recovery completes and the server accepts connections
            for _ in range(30):
                if run_cmd(["pg_isready", "-p", str(PG_PORT)], check=False).returncode == 0:
                    break
                time.sleep(1)
            else:
                raise RuntimeError("Ephemeral instance did not become ready in time")

            # 4. Execute dry-run upgrade
            upgrade_cmd = f"ALTER EXTENSION {ext_name} UPDATE TO '{target_version}';"
            run_cmd(["psql", conn_str, "-c", upgrade_cmd])

            # 5. Validate catalog consistency
            if not validate_extension_version(conn_str, ext_name, target_version):
                raise RuntimeError(f"Extension {ext_name} did not reach version {target_version}")

            print("[SUCCESS] Dry-run validation passed. Safe for production promotion.")
        except Exception as e:
            print(f"[FAILURE] Dry-run validation failed: {e}")
            sys.exit(1)
        finally:
            # 6. Idempotent teardown
            run_cmd([f"{PG_BIN}/pg_ctl", "-D", pg_data, "stop", "-m", "immediate"], check=False)

if __name__ == "__main__":
    if len(sys.argv) != 4:
        print("Usage: python dry_run_validate.py <snapshot_dir> <ext_name> <target_version>")
        sys.exit(1)
    main()

Wire this into the deployment workflow as a mandatory gate that blocks progression on any non-zero exit code. A healthy run emits a structured verdict the pipeline can branch on:

{
  "status": "dry_run_approved",
  "extension": "postgis",
  "restore_point_reached": true,
  "catalog_version_after_upgrade": "3.4.1",
  "recovery_path_armed": true
}

Because the ephemeral instance restores from the same snapshot the production rewind would use, a passing gate proves the recovery floor is genuine — not just that the upgrade SQL is syntactically valid.

Failure Modes & Error Taxonomy #

PITR failures cluster around a small set of misconfigurations, each with a distinct log signature. Feeding these into a structured classifier turns a stalled recovery into an actionable signal, as developed in Error Categorization Frameworks.

The pair to encode first is the expired-WAL case (58P01) versus the missing-marker case: the former demands a fresher base backup and longer retention, while the latter is a capture-ordering bug that no amount of storage fixes.

Rollback / Recovery Path: Automated PITR Restore #

When the upgrade fails mid-execution or the post-upgrade health gate rejects the new runtime, do not attempt manual DDL reversals — an immediate-commit step cannot be undone that way. Instead, trigger a deterministic PITR restore targeting the exact marker captured in Step 1: halt the primary, replace the data directory with the archived snapshot, inject the restore_command and recovery_target_name, and start PostgreSQL with recovery.signal present.

#!/usr/bin/env bash
set -euo pipefail

# Production fallback execution
PG_DATA="/var/lib/postgresql/16/main"
WAL_ARCHIVE="/mnt/wal_archive"
RESTORE_POINT="$(cut -d= -f2 /var/lib/pg_snapshots/latest/recovery_metadata.txt)"

# 1. Stop primary instance gracefully
pg_ctlcluster 16 main stop -m fast

# 2. Replace data directory with snapshot (clear contents without a literal glob)
find "${PG_DATA:?}" -mindepth 1 -delete
tar -xzf "/var/lib/pg_snapshots/latest/base.tar.gz" -C "$PG_DATA"
mkdir -p "${PG_DATA}/pg_wal"
tar -xzf "/var/lib/pg_snapshots/latest/pg_wal.tar.gz" -C "${PG_DATA}/pg_wal"

# 3. Configure PITR recovery
cat >> "${PG_DATA}/postgresql.auto.conf" <<EOF
restore_command = 'cp ${WAL_ARCHIVE}/%f %p'
recovery_target_name = '${RESTORE_POINT}'
recovery_target_action = 'promote'
EOF
touch "${PG_DATA}/recovery.signal"

# 4. Start instance and verify recovery completion
pg_ctlcluster 16 main start
pg_isready -t 60

echo "[SUCCESS] PITR restore completed to ${RESTORE_POINT}. Verify application connectivity."

This procedure is idempotent — the find … -delete and re-extract converge on the same state no matter how far a partial upgrade progressed, so a restore interrupted by a network partition can simply be re-run. For incidents where the orchestration layer itself is unavailable, operators reach this same anchor through the documented emergency procedures in Fallback Routing Strategies, and every restore is recorded against the deployment manifest per Version Control & Branching so post-incident review can reconstruct exactly what state was rewound from.

Performance & Scale Considerations #

The cost of PITR is dominated by base-backup size and WAL replay volume, not by the restore point itself, which is a single cheap WAL record.

• Backup capture window. pg_basebackup --checkpoint=fast forces an immediate checkpoint so the backup starts without waiting for the next scheduled one, trading a brief I/O spike for a shorter capture window. On multi-terabyte clusters, prefer an incremental or block-level backup tool over a full pg_basebackup per upgrade, and stage the base backup on separate spindles so capture does not contend with production I/O.
• Replay time is proportional to WAL volume since the base. Recovery must replay every segment from the base backup’s start LSN to the marker. Taking the base backup shortly before the upgrade keeps that distance — and therefore the recovery time objective — small; an aged base backup means replaying hours of unrelated WAL to reach the marker. Size the maintenance window with headroom for this replay, using Threshold Tuning for Downtime Windows.
• Restore is a full-cluster operation. PITR rewinds the entire instance, not one extension or one database. On a fleet, validate the rewind against a production-shaped replica first — via Test Environment Routing — and roll upgrades out in canary waves so a bad transition never forces a fleet-wide restore.
• WAL retention is the real ceiling. The marker is only reachable while every intervening segment survives in the archive. Set retention to comfortably exceed the longest plausible time between base backup and rollback decision; the withdrawn-support and version-band checks that gate an upgrade are kept current in the compatibility matrix.

FAQ #

Related Pages #

• Up: Automated Execution & Rollback Workflows
• ALTER EXTENSION Automation
• Fallback Routing Strategies
• Threshold Tuning for Downtime Windows
• Error Categorization Frameworks
• Version Control & Branching