Configuring WAL Archiving for Upgrade Recovery

How to set up continuous write-ahead-log archiving so a failed extension upgrade can be rewound to any second before it ran — the prerequisite that turns “we have a nightly backup” into a real point-in-time recovery capability.

Up: Snapshot & Point-in-Time Recovery — this page is the WAL-archiving setup that PITR depends on, under the Automated Execution & Rollback Workflows area of the site.

Context & When This Applies

Point-in-time recovery needs two things: a base backup and an unbroken chain of archived WAL segments from that backup forward. A nightly dump alone cannot rewind to just before a 02:00 upgrade — only continuously archived WAL can, because it records every change with enough granularity to stop at an exact time or transaction. This page applies to PostgreSQL 12–17 for any fleet that runs extension upgrades and needs a non-transactional failure (a background-worker registration, a partial catalog change) to be recoverable to a precise moment. It is the infrastructure prerequisite behind every rollback that PITR restore vs schema-level rollback routes to a full rewind.

Configuring it correctly is a one-time setup with an ongoing invariant: the archive must never have a gap. A single missing WAL segment breaks the replay chain past that point, so archiving reliability — not just its existence — is what makes upgrade recovery real.

A base backup plus continuous WAL enables recovery to a precise target A base backup is taken at a point in time. From there, the server continuously archives each completed WAL segment to durable storage, forming an unbroken chain. To recover, the base backup is restored and the archived WAL segments are replayed forward, stopping at a chosen recovery target time — for example one second before a failed upgrade began. A gap in the archived WAL chain would break replay at that gap, so archive reliability is essential. Base backup t0 anchor WAL WAL WAL WAL recovery target 1s before upgrade stop replay here Recovered known-good state continuous archive — no gaps

Concept: The Archive Chain Is the Recovery Granularity

A base backup captures the whole cluster at one instant; WAL captures every change after it. Recovery works by restoring the base backup and replaying WAL forward, and it can stop at any point the WAL records — a timestamp, a named restore point, a transaction ID. That means your recovery granularity equals your archive completeness: with continuous archiving you can rewind to one second before an upgrade; with only a nightly backup you can rewind only to last night, losing a day. For extension recovery specifically, the ability to stop just before the ALTER EXTENSION — created as a named restore point by the automation — is what makes a non-transactional failure recoverable without losing unrelated writes.

The invariant that makes this trustworthy is no gaps. PostgreSQL calls archive_command for each completed WAL segment; if that command ever succeeds without actually durably storing the segment (or is skipped), the chain breaks and recovery cannot pass the gap. So archive_command must be reliable and fail-loud: it returns success only when the segment is safely stored, and returns non-zero otherwise so PostgreSQL retries rather than advancing. This is the same fail-closed discipline the compatibility validation pipeline applies to gates, here applied to durability.

Runnable Implementation

The configuration below enables archiving with a fail-loud archive_command, and the automation creates a named restore point immediately before an upgrade so recovery has an exact target.

# postgresql.conf — enable continuous WAL archiving. Requires a restart.
wal_level = replica                 # or higher; 'minimal' cannot archive
archive_mode = on
# Fail-loud: copy only if the destination file does NOT already exist, and
# return non-zero on any failure so PostgreSQL retries instead of advancing.
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'
archive_timeout = 60                # force a segment at least every 60s
#!/usr/bin/env python3
"""Create a named restore point right before an extension upgrade."""
import psycopg2


def mark_pre_upgrade(dsn: str, label: str) -> str:
    conn = psycopg2.connect(dsn)
    conn.autocommit = True                      # pg_create_restore_point is admin
    try:
        with conn.cursor() as cur:
            # A named target recovery can stop at, cleaner than guessing a time.
            cur.execute("SELECT pg_create_restore_point(%s);", (label,))
            lsn = cur.fetchone()[0]
            # Force the current WAL segment to be archived now so the restore
            # point is durably in the archive, not just in the live WAL.
            cur.execute("SELECT pg_switch_wal();")
        return lsn
    finally:
        conn.close()


if __name__ == "__main__":
    print("restore point at LSN", mark_pre_upgrade("dbname=appdb",
                                                   "before_postgis_3.4.2_upgrade"))

Call mark_pre_upgrade as the snapshot step of the execution envelope, then run the upgrade through ALTER EXTENSION Automation; the named restore point becomes the recovery target if the upgrade must be rewound.

Expected Output & Verification

The restore-point call returns the LSN it marked:

restore point at LSN 0/9A3F1B8

Verify archiving is actually working — the single most important check, because a silently broken archive is worthless until the moment you need it:

-- archived_count should be climbing and failed_count should be zero.
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal
FROM   pg_stat_archiver;

A non-zero failed_count or a stale last_archived_time means the chain is breaking now — fix it before trusting any recovery. Confirm the restore point is in the archive by checking the segment named in last_archived_wal advanced past the pg_switch_wal call, and record the archive health alongside the deploy the way Version Control & Branching records other deploy metadata.

Edge Cases & Gotchas

archive_command returning success without storing the segment is the silent killer. A command like cp %p /wal_archive/%f that succeeds even when the destination is full will report success while losing data, breaking recovery invisibly. The test ! -f ... && cp form above refuses to overwrite and fails loud; better still, use a tool that fsyncs and verifies. Monitor pg_stat_archiver.failed_count continuously.

wal_level = minimal cannot archive. If wal_level is minimal, archive_mode = on has nothing sufficient to archive and recovery is impossible. Set wal_level = replica (or higher) and restart; verify with SHOW wal_level before assuming archiving is viable.

A restore point only helps if its WAL segment reached the archive. pg_create_restore_point marks a position in the current WAL segment, which may not be archived yet. The pg_switch_wal() call forces the segment out so the restore point is durable; skipping it can leave your target un-recoverable if the primary is lost before the segment fills.

Archive storage must outlive the changes it protects. WAL segments needed for a recovery must be retained at least until a newer base backup makes them obsolete. An over-aggressive archive-cleanup job that deletes segments still bridging your oldest base backup breaks PITR; align retention with your base-backup cadence.