PITR Basics

Point-in-time recovery (PITR) restores a base backup then replays archived WAL until a target time, transaction, or named restore point. It recovers from operator error between backups.

Recipe

Enable archiving on PostgreSQL 18.4 primary; take base backup; restore with recovery_target_time.

ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f';
-- restart required for archive_mode

# Base backup
pg_basebackup -h primary.internal -U replicator -D /restore/data -Fp -Xs -P
 
# recovery signal + target in postgresql.conf on restore host
# recovery_target_time = '2026-07-09 14:32:00+00'
# restore_command = 'cp /wal_archive/%f %p'

When to reach for this: Recovery from DROP TABLE, bad migration, or ransomware window when logical dumps are too stale.

Working Example

Archive check, base backup, and recovery configuration:

-- Primary: archiver health
SELECT archived_count, last_archived_wal, last_archived_time, failed_count
FROM pg_stat_archiver;
 
-- Create named restore point before risky change
SELECT pg_create_restore_point('before_index_rebuild');

# Restore host postgresql.conf (or postgresql.auto.conf)
cat >> /var/lib/postgresql/18/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /wal_archive/%f %p'
recovery_target_name = 'before_index_rebuild'
recovery_target_action = 'promote'
EOF
touch /var/lib/postgresql/18/main/recovery.signal
pg_ctl -D /var/lib/postgresql/18/main start

-- After promotion: confirm recovery finished
SELECT pg_is_in_recovery();
SELECT pg_wal_replay_resume();  -- only if paused

What this demonstrates:

• Continuous WAL archive bridges base backup time to target moment.
• recovery.signal starts instance in recovery mode (PostgreSQL 12+).
• Named restore points give precise, documented targets.

Deep Dive

PITR Timeline

[Base backup T0] ---- WAL archive ---- [Target T1] ---- [Now]
                      replay only this segment

Required Pieces

RPO Implication

PITR RPO equals last successfully archived WAL (plus any unarchived WAL on failed primary). Monitor pg_stat_archiver.failed_count.

Gotchas

• archive_mode off until incident - No WAL chain; only base backup time recoverable. Fix: Enable archiving day one.
• Missing WAL segment in archive - Recovery stops short of target. Fix: Replicate archives to second region; alert on gaps.
• Wrong timezone in recovery_target_time - Recover to wrong moment. Fix: Use explicit offset (+00) or timestamptz.
• Recovering over production data_dir - Destroys current cluster. Fix: Restore to new host; swap after validation.
• Forgetting recovery_target_action - Recovery pauses at target indefinitely. Fix: Set promote or pause deliberately.

Alternatives

FAQs

Related

• archive_mode & archive_command - shipping WAL
• Recovery Target Options - stop conditions
• PITR Drill Runbook - measured restore
• Backup Basics - physical backups

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.