WAL & replicationadvancedFree in full

Prove point-in-time recovery from archived WAL

The native PG16.14 lab took a streaming base backup, archived WAL, restored to a timestamp between two commits, kept the before-target row, excluded the after-target row, and promoted in 4.128s on this host.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

A successful backup job is not recovery proof. Point-in-time recovery needs a usable base backup, every required WAL segment, an exact recovery target, and an application-level check that data before the target exists while later data does not.

The lab inserts a base row, takes pg_basebackup with WAL streaming, inserts a keep-before-target row, records a UTC timestamp, inserts an exclude-after-target row, then restores and promotes a separate PostgreSQL instance.

In plain English

PITR restores a base backup and replays archived WAL until a chosen moment. The only useful proof is data: one known row committed before the target must exist, and one committed after it must not. The timer here measures this small local restore, not a production database recovery promise.

Before you start

  • • Docker Compose and the repository harness at docker/labs/ha-perf/pitr/run-pitr.sh.
  • • A WAL archive on storage independent of the live PGDATA.
  • • Enough time and space to restore into a separate data directory.
  • • All local passwords and filesystem-copy archive commands are lab-only examples.

How to identify it

  • ›pg_stat_archiver has no failed archive commands and archived_count is advancing.
  • ›A base backup exists independently of the live data directory.
  • ›The recovery target is recorded in UTC with a deliberate commit on each side.
  • ›The restored server promotes and contains exactly the expected side of the marker.

Pitfalls to avoid

  • ✕Do not call backup completion a restore test. Run the restore into a separate data directory and query application data.
  • ✕Do not choose a target without recording timezone and a known transaction on each side.
  • ✕Do not delete WAL based only on max_wal_size; archive failures and slots can retain or lose required segments independently.
  • ✕Do not call 4.128s a production RTO. The lab has tiny data, local volumes, no object-store latency, and no application bootstrap.
  • ✕Do not restore a Patroni member in place while the cluster is active; cluster-level PITR requires a controlled re-bootstrap procedure.

Trace it

Run these against the affected server. To build a copy of this scenario instead, see “Reproduce it in a lab” below.

  1. 01

    Diagnose: verify the restored application marker

    Run on the restored instance. The base and before-target rows should exist; exclude-after-target must be absent.

    Shell
    PGPASSWORD=postgres psql   -h localhost -p 55471 -U postgres -d labdb   -c "SELECT note, created_at FROM recovery_probe ORDER BY id;"
    Captured after PITR
    base-backup-row
keep-before-target
  2. 02

    Diagnose: verify archiving before attempting recovery

    A restore cannot succeed if archive failures are accumulating. Check the source before shutdown and keep the archive independently of PGDATA.

    Shell
    PGPASSWORD=postgres psql           -h localhost -p 55470 -U postgres -d labdb           -c "SELECT archived_count, failed_count, last_archived_wal, last_failed_wal FROM pg_stat_archiver;"
    Captured before source shutdown
    archived_count > 0
        failed_count = 0
        last_archived_wal = 000000010000000000000005
        last_failed_wal = [NULL]

Resolution approach

  1. 1.Take a base backup that includes or streams the required WAL.
  2. 2.Monitor archive success and retain WAL through the recovery window.
  3. 3.Restore to an isolated data directory with recovery_target_time/LSN/XID and promote.
  4. 4.Verify application markers and measure the complete service recovery path, not just PostgreSQL readiness.

Stop it recurring

  1. 01

    Keep a repeatable restore assertion

    A marker query makes recovery testable and automatable instead of relying on log text.

    Shell
    PGPASSWORD=postgres psql   -h localhost -p 55471 -U postgres -d labdb   -c "SELECT
        count(*) FILTER (WHERE note = 'keep-before-target') AS kept,
        count(*) FILTER (WHERE note = 'exclude-after-target') AS excluded
      FROM recovery_probe;"
    Captured live on PostgreSQL 16.14 (docker/labs/ha-perf/pitr)
     kept | excluded
------+----------
    1 |        0

Reproduce it in a lab

Builds the scenario above on a throwaway database so you can practise the fix. Skip this if you are working a live incident.

  1. 01

    Lab setup (run this first)

    Creates a PG16 source, takes pg_basebackup -Xs, archives switched WAL, restores to the timestamp target, and asserts the before row exists while the after row does not.

    Shell
    cd docker/labs/ha-perf/pitr
bash run-pitr.sh
    Measured restore sample · PostgreSQL 16.14
    recovery_target_time=2026-08-16 01:48:23.805578+00
restore_ready_ms=4128
kept_before_target=1
excluded_after_target=0
promoted=true
status=PASS

Verify you're done

SELECT 'Run the PITR harness and require kept=1, excluded=0, promoted=true' AS verification;

Last verified 2026-08-16 · PostgreSQL 16.14 (docker/labs/ha-perf/pitr)

Related errors08006

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

Related runbooks

Continue the same incident path

Sibling procedures that cover the adjacent setup, recovery, or prevention step.

More in this category

Other WAL & replication runbooks

Neighbouring incidents that share the same diagnostic surface.

All WAL & replication runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references — what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →