PostgreSQL Point-in-Time Recovery

Point-in-time recovery (PITR) restores a base backup and replays archived WAL up to a chosen moment. It is how you recover from a destructive statement rather than from hardware failure.

Prerequisites

PITR is only possible if it was configured beforehand:

• archive_mode = on with a working archive_command.
• A base backup taken after archiving started.
• Every WAL segment from that backup to the target time, present and readable.

Verify archiving is healthy continuously, not when you need it:

SELECT last_archived_wal, last_archived_time, last_failed_wal, failed_count
FROM pg_stat_archiver;

A non-zero failed_count means the chain has holes, and a hole makes recovery stop there.

Recovery targets

# In postgresql.conf of the restored cluster; choose exactly one target.
restore_command = 'pgbackrest --stanza=main archive-get %f "%p"'
 
recovery_target_time = '2026-07-30 14:22:00+00'
# recovery_target_xid  = '785412'
# recovery_target_lsn  = '3F/A2000140'
# recovery_target_name = 'before_release_42'
 
recovery_target_inclusive = false   # stop *before* the target
recovery_target_action = 'pause'    # pause so you can inspect before promoting

recovery_target_action = 'pause' is the safety-critical one: the cluster stops at the target and waits. You can connect read-only, verify you picked the right instant, and only then call pg_wal_replay_resume() or promote.

Named restore points make this precise. Create one before any risky operation:

SELECT pg_create_restore_point('before_release_42');

Procedure

# On a separate host, with the target cluster stopped.
pgbackrest --stanza=main \
  --type=time --target="2026-07-30 14:22:00+00" \
  --target-action=pause \
  --pg1-path=/srv/pgdata/17/recovery \
  restore
 
pg_ctlcluster 17 recovery start

Then verify before promoting:

SELECT pg_is_in_recovery();                     -- true while paused
SELECT count(*) FROM orders WHERE created_at > '2026-07-30';
-- Satisfied it is the right instant?
SELECT pg_promote();

Timelines

Promoting creates a new timeline, so recoveries from the same archive do not overwrite each other. If you need to recover again to a different instant, set recovery_target_timeline explicitly — by default a restore follows the latest timeline, which may not be the branch you want.

Practise it

The two numbers that matter — how long a full restore takes, and how far back the archive really reaches — are only knowable by doing it. Restore drills belong on a schedule; see Restore Testing.