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 after that backup, an exact recovery target (time, LSN, XID, or named restore point), and an application-level check that data before the target exists while later data does not. Without that proof, restore day is the first time anyone learns the archive is broken.

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. The same sequence is what you run against production archives on a spare host.

Simple terms

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. SQL path and harness verified.

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.failed_count is 0 and archived_count / last_archived_wal are advancing. failed_count > 0 or a stalled last_archived_time means the restorable window is already broken.
›A base backup exists independently of the live data directory (pg_basebackup -D elsewhere, or a backup tool's catalog entry).
›The recovery target is recorded in UTC (or with an explicit offset) with a deliberate commit on each side of the target.
›restore_command can fetch the backup label's start WAL and every segment through the target from the archive.
›The restored server promotes (pg_is_in_recovery() = false) and contains exactly the expected side of the marker rows.

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 (or UTC) 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.
✕Do not leave recovery_target_inclusive at the default (on) if your target timestamp falls on the damaging commit; you can replay the damage you meant to exclude.
✕Do not omit recovery.signal (PG12+). Without it the instance starts as a normal primary and ignores recovery targets.

Trace it

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

01
Diagnose: verify archiving before attempting recovery
A restore cannot succeed if archive failures are accumulating. Columns: archived_count (successful archive_command runs), failed_count (non-zero exits), last_archived_wal (newest segment name PostgreSQL believes is safe), last_failed_wal (last segment that failed). Keep the archive on storage independent of PGDATA. Docs: https://www.postgresql.org/docs/current/continuous-archiving.html
Shell
```
PGPASSWORD=postgres psql   -h localhost -p 55470 -U postgres -d labdb   -c "SELECT archived_count, failed_count,
             last_archived_wal, last_archived_time,
             last_failed_wal, last_failed_time
      FROM pg_stat_archiver;"
```
Captured before source shutdown
```
 archived_count | failed_count |    last_archived_wal     | last_failed_wal
----------------+--------------+--------------------------+-----------------
             6 |            0 | 000000010000000000000005 |
(1 row)
```

Diagnose: restore with an explicit target (production path)

On a spare host: restore the base backup into a new data directory, configure restore_command, set recovery_target_time (or xid/lsn/name), set recovery_target_action=promote, set recovery_target_inclusive deliberately, create recovery.signal (PostgreSQL 12+), start the server. Do not touch the live cluster's PGDATA. Docs: https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET

Shell

# After copying base backup into /restore/pgdata:
# postgresql.auto.conf (or conf.d) on the restored instance:
#   restore_command = 'cp /archive/%f %p'          # lab only; production uses your real fetch
#   recovery_target_time = '2026-08-16 01:48:23.805578+00'
#   recovery_target_action = 'promote'
#   recovery_target_inclusive = off                 # stop before a commit at the exact target
# touch /restore/pgdata/recovery.signal
# pg_ctl -D /restore/pgdata -o "-p 55471" start
#
# Success signals in the log:
#   starting point-in-time recovery to ...
#   consistent recovery state reached at ...
#   recovery stopping before commit of transaction ...
#   archive recovery complete / database system is ready

03
Diagnose: verify the restored application marker
Run on the restored instance only. The base and before-target rows should exist; exclude-after-target must be absent. This is the only proof that matters more than log text.
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
```

Resolution approach

1.1. Confirm archive health: failed_count=0 and last_archived_wal present on disk at the archive destination.
2.2. Take or locate a base backup that includes or streams the WAL needed to reach consistency (pg_basebackup -X stream, or tool equivalent).
3.3. Record recovery_target_time/LSN/XID/name in UTC with marker rows (or a named restore point) on each side of the cut.
4.4. Restore into an isolated data directory; set restore_command, recovery_target_*, recovery_target_action=promote; create recovery.signal; start on a spare port.
5.5. Success signals: log shows recovery stopping at/before the target; pg_is_in_recovery() becomes false; kept marker = 1 and excluded marker = 0.
6.6. Measure complete service recovery (app config, DNS, connection pools), not only PostgreSQL readiness. Lab restore_ready_ms is not a production RTO.

Stop it recurring

Keep a repeatable restore assertion

A marker query makes recovery testable and automatable instead of relying on log text. Success: kept=1, excluded=0 after every drill.

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

02
Create a named restore point before risky work
Named restore points avoid reconstructing timestamps under pressure. Recover with recovery_target_name. Docs: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP
SQL
```
SELECT pg_create_restore_point('before_risky_migration');
-- Later, on the restore instance:
-- recovery_target_name = 'before_risky_migration'
-- recovery_target_action = 'promote'
```

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.

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. Lab-verified on the repository harness.
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.

08006Connection to client lost

Related runbooks

Continue the same incident path

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

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 →