How PITR Works

Snapshots, WAL history, recovery windows, recovery targets, and timelines: the five concepts needed to reason accurately about PostgreSQL PITR.

Concept

PGSQL

If a database is a state machine, WAL (Write-Ahead Log) is its ordered change history. PostgreSQL records each modification in WAL before applying it to data files. Save a physical snapshot at one point, preserve all later WAL, and PostgreSQL can replay that history to a selected consistent state.

PITR is therefore the combination of three simple elements: a snapshot (base backup), history (WAL archive), and a target (where replay should stop).

Snapshot: Base Backup

A base backup is a physical snapshot of the whole PostgreSQL cluster and supplies a starting point for recovery. Pigsty uses pgBackRest to create and manage three backup types:

Type	Contents	Recovery characteristics
Full	All database-cluster files	Self-contained, shortest chain, largest backup
Differential	Changes since the latest full backup	Restore uses the full plus the differential
Incremental	Changes since the latest backup of any type	Smallest backup, restore depends on its complete chain

The wrapper pg-backup [full|diff|incr] triggers a backup. With no argument it requests incr; pgBackRest creates a full backup instead when no valid full exists. pg_crontab declares recurring jobs and installs them in the postgres user’s crontab.

Backup frequency affects recovery time: the newer the usable backup, the less WAL must be replayed to reach a given target. See PITR Tradeoffs.

WAL History

A snapshot reaches only its own state. WAL archiving preserves every later change needed to advance beyond it. Pigsty’s standard Patroni templates enable archiving and ask PostgreSQL to hand each completed WAL segment to pgBackRest:

archive_mode: 'on'
archive_command: 'pgbackrest --stanza=pg-meta archive-push %p'
archive_timeout: 300

Two implementation details matter:

archive_timeout: 300: on a low-write cluster, PostgreSQL can force a segment switch after five minutes so a partially filled segment does not wait indefinitely. This normally keeps the right edge of the recovery window within minutes when WAL is being generated; it is not a promise that every commit is already remote.
Asynchronous archive: pgBackRest uses /pg/spool with archive-async=y to batch transfers. Pigsty sets archive-push-queue-max=4GiB; if repository failure lets the queue cross that bound, pgBackRest can drop the queued WAL to protect local disk. That creates an archive gap, so a new full backup is required to establish a fresh recoverable chain.

Expiration is automatic. When old backups expire under the repository policy, pgBackRest also expires archived WAL that no remaining backup needs, unless archive retention is overridden explicitly.

Recovery Window

The backup and its continuous WAL history form a recovery window:

Left boundary: the start of the oldest usable remaining backup chain. In practical time-based descriptions, this is usually summarized by the oldest retained full backup’s time.
Right boundary: the latest WAL successfully archived to a repository that survives the incident.

The window moves forward as new backups arrive and old chains expire. Pigsty’s local preset keeps two full backups; with one successful full per day, coverage is roughly one to two days. The minio preset uses retention_full_type: time with retention_full: 14; with weekly full backups, the oldest retained chain normally yields roughly 14–21 days of steady-state coverage. These are estimates, not SLAs: missed backups, archive gaps, explicit archive-retention overrides, or repository loss change the actual window. Verify it with pig pb info and restore drills.

See PITR Tradeoffs and Backup Policy.

Targets: Where Replay Stops

PostgreSQL supports several ways to locate a state inside the recovery window. Pigsty exposes six target types through pg_pitr:

`pg_pitr` type	Meaning	Typical use
`default`	Replay through all WAL available from the repository	Restore the newest archived state after total loss
`time`	Stop at a timestamp	Recover from accidental DML or DDL
`xid`	Stop at a transaction ID	Exclude a precisely identified bad transaction
`lsn`	Stop at a WAL location	Low-level exact targeting
`name`	Stop at a restore point created with `pg_create_restore_point()`	Planned change checkpoint
`immediate`	Stop as soon as the selected backup becomes consistent	Validate or expose the selected backup state quickly

The set field is different: it chooses which backup set pgBackRest restores as the starting snapshot; it is not itself a replay stop target.

Boundary Semantics

Targets are inclusive by default: the transaction at the target is retained. To stop immediately before a known bad target, set exclusive: true, which maps to recovery_target_inclusive = false.

Transactions remain atomic. Committed transactions before the effective target survive; transactions not committed at that point are rolled back. Recovery produces a consistent database state rather than half of a transaction.

Timelines

Restoring to the past and accepting new writes creates a fork in history. PostgreSQL uses a timeline to distinguish each branch. PITR promotion, replica promotion, and failover can all create a new timeline; new WAL does not overwrite the old timeline’s files.

gitGraph
    commit id: "Full backup"
    commit id: "Normal writes"
    commit id: "Bad change"
    commit id: "More writes"
    branch Timeline-2
    checkout Timeline-2
    commit id: "PITR before bad change"
    commit id: "New writes"

Keeping the old history allows another attempt if the first target was wrong. The timeline field can select a timeline; Pigsty’s recovery declaration defaults to latest.

Continue with PITR Architecture to see how these concepts map to Pigsty components and configuration.