Point-in-Time Recovery, Recovery Targets, Timelines, and Promotion

Learning outcomes

A deployment at 14:03 corrupts ServiceHub business state, but the error is discovered at 14:17 after many legitimate writes. Restoring the 02:00 base backup loses twelve hours. Replaying every archived WAL segment recreates the bad deployment. Point-in-Time Recovery (PITR) combines a physical base backup with archived WAL and stops replay at a chosen recovery target.

1. Create a deterministic business restore point

A named restore point is easier to reason about than guessing a transaction ID. On the source archival lab, create baseline state, then a restore point, then deliberately write “bad” state.

CREATE TABLE IF NOT EXISTS app.ch13_pitr_demo (  id bigint PRIMARY KEY,  state text NOT NULL,  changed_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch13_pitr_demo VALUES (1,'good',clock_timestamp())ON CONFLICT (id) DO UPDATE SET state='good', changed_at=clock_timestamp();SELECT pg_create_restore_point('before_bad_deploy');SELECT pg_switch_wal();UPDATE app.ch13_pitr_demo SET state='bad', changed_at=clock_timestamp() WHERE id=1;SELECT pg_switch_wal();

A restore point only becomes useful if the WAL containing it and the target changes reaches the archive. Verify archive progress before declaring the drill ready.

2. Restore the base backup to a new data directory

Stop any process using the recovery target directory. Copy/extract the verified base backup there, restore tablespaces to mapped locations, and make sure file ownership/permissions match the PostgreSQL operating-system account. Do not copy postmaster.pid from a running source.

restore_command = 'cp /ABSOLUTE/ch13_wal_archive/%f %p'recovery_target_name = 'before_bad_deploy'recovery_target_action = 'pause'recovery_target_timeline = 'latest'port = 55435

touch /ABSOLUTE/ch13_pitr_restore/recovery.signal

Start the restored cluster and watch the server log. Recovery replays WAL from the base backup toward the target. With action pause, PostgreSQL stops replay when it reaches the target and remains in recovery so you can inspect the state before promotion.

3. Verify the target before promoting

SELECT pg_is_in_recovery(),       pg_last_wal_replay_lsn(),       pg_get_wal_replay_pause_state();SELECT * FROM app.ch13_pitr_demo;

The acceptance criterion for this drill is business state state='good', not merely that PostgreSQL started. Also inspect related constraints, row counts, and any business invariants that define “good” for the actual application.

SELECT pg_promote(wait => true, wait_seconds => 60);SELECT pg_is_in_recovery();

Promotion ends recovery and starts a writable primary. That is an operational role change. In a real incident, client routing, fencing of the old primary, secrets, and monitoring must change consistently; promotion by itself is not a complete failover procedure.

4. Promotion creates a new timeline

When archive recovery finishes and the server starts accepting new writes, PostgreSQL creates a new timeline. The new WAL history branches from the old history rather than overwriting it. Timeline IDs are encoded in WAL filenames, and small .history files record where branches occurred.

pg_controldata /ABSOLUTE/ch13_pitr_restore | grep -E 'TimeLineID|Latest checkpoint|REDO'

This is why “the recovered server continues the old primary” is conceptually wrong. It is a new branch. Retaining timeline history lets you later recover into either earlier history or a chosen descendant timeline, subject to the base backup's position.

5. Time/LSN/XID targets need evidence

PostgreSQL supports several target forms: timestamp, transaction ID, LSN, and named restore point. A timestamp is easy to understand but depends on accurate event timing; an LSN is precise only if you captured the correct location; transaction IDs are hard to map safely to business events; named restore points are explicit but must have been created in advance. Choose the target from incident evidence, not convenience.

recovery_target_time = '2026-08-18 14:02:59+03:30'# orrecovery_target_lsn = '0/5A000000'# orrecovery_target_xid = '123456789'# choose one target family, not all of them

6. Recovery-target precision and failure semantics

For time, LSN, and transaction-ID targets, recovery_target_inclusive controls whether recovery stops just after or just before the target record/transaction. The default is inclusive. This matters when the incident boundary is exactly the transaction you are trying to exclude. A named restore point has simpler semantics because recovery stops at that explicit marker.

The target must be later than the end of the base backup. A base backup cannot be rewound to a moment while that same backup was still being taken; use an earlier base backup if you need an earlier point. In addition, if a recovery target is configured but PostgreSQL reaches the end of available WAL before the target, recovery fails rather than silently pretending the requested point was reached.

Missing and corrupted WAL

restore_command is expected to return nonzero for files not present, and recovery may normally request a history file that does not exist at the edge of a simple timeline. But a required WAL gap is fatal to reaching a later target. If WAL itself is corrupted, PostgreSQL can stop replay; the safe response is to repair the archive or choose a target before the corruption, not to invent missing bytes.

Timeline choice during re-recovery

recovery_target_timeline='latest' follows the newest descendant timeline available in the archive and is the default. current stays on the timeline that was current when the base backup was taken. Explicit timeline IDs matter when you are recovering from a previous recovery attempt and need a specific historical branch. Keep timeline history files indefinitely; they are tiny and are essential for interpreting branch ancestry.

Authoritative references

Backup and recovery behavior is version-, topology-, privilege-, and storage-sensitive. These primary PostgreSQL sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Continuous Archiving and PITR
• PostgreSQL 18 — Recovery Target Settings
• PostgreSQL 18 — Recovery Control Functions
• PostgreSQL 18 — pg_controldata