Point-in-Time Recovery — A Time Machine for PostgreSQL

If data, a table, or even a database is deleted accidentally, Point-in-Time Recovery (PITR) can return the cluster to an earlier state.

This capability, once treated as specialist DBA work, is enabled by Pigsty’s standard PostgreSQL configuration.

Replication Is Not Backup

High availability can fail over to another instance when hardware fails. It has a natural blind spot, however: replication is not backup.

Streaming replication faithfully sends every primary change to every replica within milliseconds, including a DELETE without a WHERE clause or a DROP TABLE issued against the wrong database. Failover handles a broken machine; when the data itself is wrong, every replica can contain the same error.

Database disasters therefore fall into two broad classes. Redundancy handles physical service failure through multiple copies and automatic failover. Logical errors require history: a base backup plus continuous WAL archives from which PostgreSQL can reconstruct a state before the mistake.

These mechanisms complement one another: HA restores service quickly, a delayed cluster provides a short undo window, and PITR is the final historical recovery path.

How the Time Machine Works

A database can be viewed as a state machine. A base backup is a complete physical snapshot at one point, while WAL (Write-Ahead Log) records every subsequent state change. With a snapshot and an unbroken WAL history starting from it, PostgreSQL can replay the database to any target covered by that history. The backup determines how far back recovery can start; the latest archived WAL determines how close to the present it can reach.

Base backup + WAL archive = point-in-time recovery

Pigsty orchestrates both inputs. Cluster initialization attempts an initial full backup by default, and the primary continuously sends completed WAL segments to the selected repository. See How PITR Works for the complete model of backups, archives, targets, and timelines.

Available Out of the Box

PITR is enabled in Pigsty’s standard PostgreSQL configuration. Each cluster is prepared with a backup repository, WAL archiving, and recovery tooling powered by pgBackRest. The policy remains declarative and can be customized with a few parameters:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pgbackrest_method: minio       # Silo / S3-compatible storage; local is the default
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]  # daily full backup at 01:00

The default local method stores backups under /pg/backup and retains two full backups. With one successful full backup per day, the resulting window is roughly 24–48 hours. Selecting the remote minio preset places the repository in Silo or compatible S3 storage, enables AES-256-CBC repository encryption, and uses time-based retention. With a 14-day retention setting and weekly full backups, the steady-state recovery window is roughly 14–21 days. Treat both ranges as policy estimates: actual coverage starts at the oldest usable backup and ends at the latest WAL that reached the repository.

Recovery is declarative too: specify a target, then let the playbook stop the cluster, restore files, replay WAL, and rebuild HA. An operator must still verify the recovered business state.

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": { "time": "2026-07-11 10:00:00+08", "action": "promote" }}'

This follows Pigsty’s declarative configuration model: backup policy is part of the cluster definition, and a recovery target is another declared parameter.

Benefits and Costs

PITR materially improves data integrity and availability:

• RPO (maximum data loss) is usually reduced to minutes, bounded by WAL that had not reached a surviving repository.
• RTO (time to restore service) becomes tens of minutes to hours rather than permanent loss, depending on backup size, WAL replay distance, and disk or network throughput.

The costs fall mainly into three areas:

• Confidentiality: backups are another copy of business data and need encryption and access control. Pigsty’s remote preset enables repository encryption, but its default password must be changed.
• Resources: backups consume storage and archiving consumes bandwidth. Compression, bundling, and block incremental backup reduce this cost but do not eliminate capacity planning.
• Operations: backup status must be monitored and recovery must be rehearsed. A green backup job alone is not proof that the data can be restored within the required RTO.

PITR by itself does not replace HA. A production design normally combines HA for physical failures with PITR for logical errors and site-level recovery.

Next Steps

• How PITR Works: snapshots, WAL history, recovery windows, targets, and timelines
• PITR Architecture: pgBackRest, repository selection, archive flow, scheduling, and failover behavior
• PITR Tradeoffs: failure domains, capacity, retention, and backup frequency
• Declarative Recovery: the pg_pitr parameter, pgsql-pitr.yml, and pig pitr
• PITR Scenarios: accidental deletion, bad releases, investigation, and site loss

For the operational runbooks, see PGSQL Backup and Recovery.