WAL Archiving and Point-in-Time Recovery

The one thing to understand first

Replication protects against hardware failure, but it faithfully copies mistakes — a DROP TABLE or bad UPDATE replicates instantly to every standby. Point-in-Time Recovery (PITR) protects against logical errors by letting you restore the database to any moment in the past: the instant before the mistake. It combines a base backup with the continuous archive of WAL.

PITR is just "restore the base backup, then replay the saved WAL up to a chosen instant." A backup without its WAL archive can only return you to backup time; the WAL stream is what lets you stop one second before the disaster.

The two ingredients

• Base backup — a physical snapshot of the data directory taken while the database runs (pg_basebackup or a snapshot tool). It is the starting point.
• Archived WAL — every WAL segment, copied off to safe storage as it fills. Replaying these on top of the base backup rolls the database forward, record by record, to any chosen point.

Continuous archiving

Enable WAL archiving so each completed segment is shipped to durable storage:

wal_level = replica            # (or higher)
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# %p = path to the segment, %f = its filename.
# In production use a tool like pgBackRest or WAL-G, not cp.

The archive_command must return success only when the segment is safely stored; if it fails, PostgreSQL retains the segment and retries, so a broken archive command eventually fills the WAL disk — monitor it.

Taking a base backup

pg_basebackup -h primary -U backup -D /backups/base -Ft -z -P
# Produces a consistent base backup; combined with archived WAL
# it can be restored and rolled forward.

Layer 3 — Watch it happen on your own database

To recover: restore the base backup, then tell PostgreSQL where to fetch archived WAL and how far to replay. Recovery targets let you stop precisely:

# In postgresql.conf / postgresql.auto.conf of the restored instance
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-05-20 14:29:00+00'   # stop just before the mistake
# alternatives:
#   recovery_target_lsn  = '0/3000000'
#   recovery_target_xid  = '123456'
#   recovery_target_name = 'before_migration'      # set by pg_create_restore_point()
recovery_target_action = 'promote'   # become primary when target reached

Create a recovery.signal file and start the server; it replays archived WAL up to the target, then stops. You can inspect the result and, if you overshot or undershot, restore again with a different target. Also confirm archiving is actually keeping up before you ever need this:

-- Is the archiver healthy and current?
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM   pg_stat_archiver;

A climbing failed_count means your archive_command is broken and WAL is piling up on the primary's disk — the recovery you're counting on is silently degrading.

Layer 4 — The levers this hands you

Recovery targets let you stop replay at exactly the right moment:

• recovery_target_time — most intuitive: "the state as of 14:29".
• recovery_target_lsn — exact WAL position, for precision.
• recovery_target_xid — stop at a specific transaction.
• recovery_target_name — a named restore point you set earlier with SELECT pg_create_restore_point('before_migration').
• recovery_target_inclusive — whether to include or stop just before the target.

Timelines are the safety net for redo: when recovery reaches its target and promotes, PostgreSQL starts a new timeline. This prevents confusion between the original history and the recovered branch — WAL from the new timeline is tagged differently, so you can recover again from the same base backup to a different point without the streams colliding. Timelines are why you can iterate on a PITR until you hit exactly the right moment.

Operational essentials:

• Test restores regularly — an untested backup is not a backup.
• Use a purpose-built tool (pgBackRest, WAL-G, Barman) for retention, compression, encryption, and parallelism rather than hand-rolled cp.
• Monitor that archiving keeps up (pg_stat_archiver); a stalled archiver risks WAL disk fill.
• Keep base backups frequent enough that replay time to any target is acceptable.

Layer 5 — What an Oracle DBA should expect vs what they get

PITR is the Postgres equivalent of RMAN-based recovery, and an Oracle DBA will recognise the shape but not the tooling:

• Base backup + archived WAL ≈ RMAN backup + archived redo logs. WAL is the redo stream; archive_command is the archive-log destination.
• **recovery_target_time ≈ RMAN's UNTIL TIME,** and recovery_target_scn's analogue is recovery_target_lsn / recovery_target_xid — the same "recover until just before the mistake" workflow.
• No built-in catalog or retention manager. RMAN gives you a recovery catalog, retention policies, and automatic obsolete-backup deletion out of the box; native Postgres gives you only the primitives — you need pgBackRest/WAL-G/Barman to get RMAN-class management.
• Timelines ≈ RMAN incarnations, letting you branch history after an incomplete recovery and try a different target without the streams colliding.
• No Flashback. There is no FLASHBACK DATABASE equivalent — undoing a logical mistake means a full PITR restore to a new instance, not an in-place rewind.

Key takeaway

PITR restores a base backup and replays archived WAL up to a chosen target, letting you rewind to the instant before a logical mistake that replication would otherwise have copied everywhere. The two failure modes to guard against are a broken archive_command (watch pg_stat_archiver and the WAL disk) and an untested restore — and for real retention/management, use pgBackRest, WAL-G, or Barman rather than hand-rolled cp.