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

• 1: How PITR Works
• 2: PITR Architecture
• 3: PITR Tradeoffs
• 4: Declarative Recovery
• 5: PITR Scenarios

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

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

1 - How PITR Works

A database is a state machine. WAL (Write-Ahead Log) records its changes in order. PostgreSQL writes each change to WAL before applying it to data files. Keep a physical snapshot and all later WAL to replay the database to a selected consistent state.

PITR combines three elements: a snapshot (base backup), history (WAL archive), and a target where replay stops.

Snapshot: Base Backup

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

Run pg-backup [full|diff|incr] to start a backup. With no argument, it requests incr. If no valid full backup exists, pgBackRest creates one instead. pg_crontab declares recurring jobs and installs them in the postgres user’s crontab.

Backup frequency affects recovery time. A newer usable backup requires less WAL replay to reach the same target. See PITR Tradeoffs.

WAL History

A snapshot contains only its own state. WAL archiving preserves later changes needed to recover beyond it. Standard Patroni templates enable archiving and send 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: PostgreSQL can force a segment switch after five minutes on a cluster with few writes. A partially filled segment therefore does not wait indefinitely. When WAL is generated, this usually keeps the latest recovery point within minutes. It does not guarantee that every commit is already remote.
• Asynchronous archive: pgBackRest batches transfers with /pg/spool and archive-async=y. Pigsty sets archive-push-queue-max=4GiB. If repository failure causes the queue to exceed this limit, pgBackRest can discard queued WAL to protect local disk space. The resulting archive gap requires a new full backup to establish a recoverable chain.

Expiration is automatic. When the repository policy expires old backups, pgBackRest also expires WAL that no remaining backup needs. Explicit archive-retention settings can override this behavior.

Recovery Window

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

• Left boundary: The start of the oldest usable remaining backup chain. Time-based descriptions usually use the oldest retained full backup’s time.
• Right boundary: The latest WAL successfully archived to a repository that survives the incident.

The window advances as new backups arrive and old chains expire. The local preset keeps two full backups. One successful daily full backup gives roughly one to two days of coverage. The minio preset sets retention_full_type: time and retention_full: 14. With weekly full backups, the oldest retained chain normally gives roughly 14–21 days of steady-state coverage. These are estimates, not SLAs.

Missed backups, archive gaps, archive-retention overrides, and repository loss can change actual coverage. Check pig pb info and perform restore drills to verify the window.

See PITR Tradeoffs and Backup Policy.

Targets: Where Replay Stops

PostgreSQL can identify states within the recovery window in several ways. pg_pitr exposes six target types:

set selects the backup set that pgBackRest uses as the starting snapshot. It does not specify where replay stops.

Boundary Semantics

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

Transactions remain atomic. Recovery retains transactions committed before the effective target and rolls back transactions not yet committed. The resulting state is consistent, without partial transactions.

Timelines

Restoring an earlier state and accepting new writes creates a history branch. PostgreSQL assigns a timeline to each branch. PITR promotion, replica promotion, and failover can create new timelines. New WAL does not overwrite files from old timelines.

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"

Retained history permits another attempt if the first target was wrong. Use timeline to select a timeline. Pigsty defaults to latest.

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

2 - PITR Architecture

The PITR principle is simple, but implementation must address several constraints. WAL archiving must not block production writes. Object storage backups need encryption. Backup jobs must follow the primary after failover, and shared repositories must isolate clusters. Large numbers of small objects can also limit throughput.

Pigsty uses pgBackRest with production defaults that address these constraints. This page describes the engine, repository abstraction, archive path, scheduler, and execution on the current primary.

Backup Engine: pgBackRest

Pigsty uses pgBackRest to create base backups with backup, receive WAL with archive-push, and restore data with restore and archive-get.

Relevant capabilities include:

• Parallelism: Backup, archive, and restore operations can use multiple processes.
• Backup chains: Full, differential, incremental, and block incremental backups reduce repeated transfers and storage.
• Compression and encryption: pgBackRest includes zstd compression and AES-256-CBC repository encryption.
• Repository backends: pgBackRest supports POSIX filesystems, S3-compatible services such as Silo and MinIO, Azure, GCS, and SFTP.
• Bundling: Pack small files into larger repository objects to reduce object storage overhead.

pgBackRest separates cluster histories by stanza. Pigsty uses pg_cluster as the stanza name. Multiple clusters can share storage while retaining separate backup identities:

repository
├── backup/
│   ├── pg-meta/          # base backups for pg-meta
│   └── pg-test/          # base backups for pg-test
└── archive/
    ├── pg-meta/          # archived WAL for pg-meta
    └── pg-test/          # archived WAL for pg-test

Repository Abstraction

pgbackrest_method selects a repository name. pgbackrest_repo contains a dictionary of candidate definitions. Pigsty v4.5.0 renders only the selected pgbackrest_repo[pgbackrest_method] entry as repo1. Listing local and minio does not activate both repositories.

pgbackrest_method: local          # local, minio, or a custom key below
pgbackrest_repo:
  local:
    path: /pg/backup
    retention_full_type: count
    retention_full: 2             # retain two full backups; a third may exist before expiration
  minio:
    type: s3
    s3_endpoint: sss.pigsty
    s3_region: us-east-1
    s3_bucket: pgsql
    s3_key: pgbackrest
    s3_key_secret: S3User.Backup
    s3_uri_style: path
    path: /pgbackrest
    storage_port: 9000
    storage_ca_file: /etc/pki/ca.crt
    block: y
    bundle: y
    bundle_limit: 20MiB
    bundle_size: 128MiB
    cipher_type: aes-256-cbc
    cipher_pass: pgBackRest       # replace this default secret in production
    retention_full_type: time
    retention_full: 14

The local preset provides simple backups and fast local restores. It has no encryption or bundling and retains backups by full-backup count. The minio preset uses remote Silo or compatible S3 storage. It enables encryption, bundling, block incremental backup, and time-based retention.

Pigsty converts underscores in repository keys to hyphens. It adds a repo1- prefix and writes the keys to /etc/pgbackrest/pgbackrest.conf. A custom cloud repository can therefore use pgBackRest options directly:

pgbackrest_method: s3
pgbackrest_repo:
  s3:
    type: s3                         # repo1-type=s3
    s3_endpoint: s3.us-west-1.amazonaws.com
    s3_region: us-west-1
    s3_bucket: <your_bucket>
    s3_key: <your_access_key>
    s3_key_secret: <your_secret>
    s3_uri_style: host
    path: /pgbackrest
    cipher_type: aes-256-cbc
    cipher_pass: <your_password>
    retention_full_type: time
    retention_full: 90

See Backup Repository for Silo, external S3-compatible storage, versioning, object locking, TLS, and credential details.

Archiving and Scheduling

When pgbackrest_enabled is true, the Patroni templates set the following configuration. This option defaults to true.

archive_mode: 'on'
archive_timeout: 300
archive_command: 'pgbackrest --stanza=<cluster> archive-push %p'

Base backups enter the system in two ways:

• Initial backup: After bootstrapping a top-level primary, Pigsty attempts a backup if pgbackrest_init_backup is true. The task ignores backup failures. It writes /etc/pgbackrest/initial.done only after success, so the marker confirms completion.
• Scheduled backup: pg_crontab installs jobs in the database superuser’s crontab. The role default is an empty list. Standard examples usually add a daily full backup at 01:00.

pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]

pg-backup [full|diff|incr] wraps pgbackrest backup. Without an argument, it requests an incremental backup. If no usable full backup exists, pgBackRest makes a full backup instead.

Backups Follow the Primary

Every PostgreSQL node receives pgBackRest and the same scheduled job. However, pg-backup checks /pg/bin/pg-role and proceeds only on the current primary. Replicas exit immediately without creating competing backups.

The backup schedule therefore operates independently of HA topology:

• All members receive the same repository configuration and crontab.
• After failover, the new primary can run subsequent backups and archive WAL. The schedule needs no change.
• The current primary alone controls authoritative writes to a stanza.

For a non-local repository, Pigsty adds pgBackRest after basebackup in Patroni’s create_replica_methods. Patroni tries basebackup first. If it fails, Patroni can restore the replica with pgbackrest --delta restore. This moves the copy load from the primary to the repository.

Performance Defaults

The pgBackRest template limits overhead during normal operation and prioritizes restore throughput:

The 4 GiB queue limit has a tradeoff. If the repository remains unavailable and the queue exceeds this limit, pgBackRest can discard queued archive files. PostgreSQL continues running, but the WAL archive has a gap. Create a new full backup to establish a new recovery chain. See How PITR Works.

Observability

When backup and exporter settings are enabled, pgbackrest_exporter runs on each PostgreSQL node. It exposes metrics on port 9854. Monitoring uses these metrics to show backup age, type, size, duration, and errors.

Useful diagnostic entry points include:

See Backup Administration for operational checks, then PITR Tradeoffs for policy design.

3 - PITR Tradeoffs

Backups require storage, network traffic, and operational work. They determine how much data you can recover and how quickly service returns. More history usually requires more capacity. A shorter RTO usually requires newer backups and tested procedures.

A backup policy must define repository location, history retention, and backup frequency.

Where: Choose the Failure Domain

Repository location is the most important choice. It determines which disasters the backup can survive.

A local repository (pgbackrest_method: local) uses the primary’s local filesystem. It is simple and fast, with no remote service dependency. Data and backups usually share one failure domain. Machine, disk, or filesystem loss can destroy both. Local backup protects against logical errors. For protection against total host loss, /pg/backup must use independent storage.

An object storage repository (pgbackrest_method: minio or a custom S3 definition) sends backups to Silo or S3. For an independent disaster recovery copy, deploy it outside the database host or site failure domain. The minio preset enables AES-256-CBC repository encryption, bundling, and block incremental backup. Recovery throughput depends on the network and storage service. Operating that service adds responsibility.

The repository contains sensitive business data. Change default access keys and cipher_pass. Restrict access and protect credentials separately from database hosts. Verify any object-lock policy. See Backup Repository.

How Long: Capacity and Recovery Window

Longer history generally requires more storage. Actual use depends on compression, deduplication, block incremental backup, database change rate, and the full/differential/incremental backup mix. Measure backup and WAL growth instead of using a fixed multiplier.

For an illustrative 100 GB database changing by 10 GB per day, before compression:

• Daily full, retain two (local preset): Full backups use about 200 GB, plus WAL. Successful daily jobs commonly provide roughly one to two days of recovery history.
• Weekly full, daily incremental, retain 14 days (minio preset): The oldest surviving weekly chain commonly provides roughly 14–21 days of coverage. Allow capacity for multiple full backups, their incrementals, archived WAL, and temporary retention-plus-one behavior during expiration.

The retention setting alone does not define the recovery window. Coverage starts at the oldest usable chain and ends at the latest WAL in the surviving repository. Time retention removes an old full backup only when another qualifying full backup satisfies the period. Related incremental backups and WAL follow the retained full chains. Check pig pb info, monitor archive health, and perform a restore to prove coverage.

Retain enough history to cover the delay between an error and its detection. A dropped table may be noticed within minutes. Slow corruption or month-end reconciliation failure can take weeks to detect.

How Often: Backup Frequency and RTO

Recovery first restores a backup chain, then replays WAL to the target. Backup size and storage throughput control restore time. The distance from backup to target controls replay time.

WAL replay is mostly serial. Restoring a weekly full backup just before the next full can require nearly a week of replay on a write-heavy database. Daily incremental backups shorten replay distance and transfer only changes since the preceding backup. They still require a valid chain. Monitor and test the entire chain, not only the latest file.

Choose a backup frequency that meets the measured RTO within the available backup window and production load budget.

Pigsty Presets

Pigsty provides two candidate repository definitions. pgbackrest_method selects one for the generated repo1 configuration.

Standard policy: local repository and daily full backup. This policy is simple and restores through local I/O. Use it for development or environments with separate protection against host loss:

pgbackrest_method: local
pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]
# Local preset retains two full backups; actual coverage depends on successful jobs and WAL continuity.

Production policy: remote Silo/S3 repository, weekly full, daily incremental. Place the repository in a separate failure domain and use the encrypted minio preset:

pgbackrest_method: minio
pg_crontab:
  - '00 01 * * 1 /pg/bin/pg-backup full'
  - '00 01 * * 2,3,4,5,6,7 /pg/bin/pg-backup'
# The preset retains full history by 14 days; weekly fulls commonly yield about 14–21 days.

local and minio entries in pgbackrest_repo are alternatives, not two active repositories. The template renders only pgbackrest_repo[pgbackrest_method] as repo1. Multiple active pgBackRest repositories require explicit advanced configuration. They also require independently tested backup, expiration, and restore procedures. The two presets alone do not provide this setup.

Use Backup Policy for capacity modelling and schedule details.

A Backup Is Proven by Restore

Monitor backup job success, and add clone recovery drills to routine operations. Use them to answer:

1. Is the chain usable? Restore it end to end and validate data.
2. What is the measured RTO? Database size and WAL volume change over time.
3. Can the on-call operator execute the runbook? The first full exercise should not happen during an incident.

Clone recovery leaves the source online and overwrites the selected destination. Verify the exact destination and use disposable infrastructure. See Declarative Recovery for the recovery interface.

4 - Declarative Recovery

A backup must support recovery when an incident occurs. Traditional PITR requires connected manual steps: pause HA, stop PostgreSQL, prepare recovery settings, restore the backup, and replay WAL. Then validate the target, rebuild metadata, and start the cluster again.

Pigsty uses declarative configuration for recovery. Declare the recovery target. The tools stop the cluster, restore data, replay WAL, and return control to the operator.

Declare a Recovery Target

Set pg_pitr to describe the target. Run pgsql-pitr.yml to restore it. The most common form selects a specific time:

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

Fields in this parameter define the six recovery target types and other recovery behavior:

pg_pitr:                           # Recovery declaration; every field is optional
  cluster: pg-meta                 # Source backup stanza; defaults to this cluster
  type: time                       # default | time | xid | lsn | name | immediate
  time: '2026-07-11 10:00:00+08'   # Mutually exclusive with xid, lsn, and name
  exclusive: false                 # Stop before the target; inclusive by default
  action: promote                  # Explicit promotion; a targeted restore defaults to pause
  timeline: latest                 # Target timeline; latest by default
  set: latest                      # Starting backup set; selected automatically by default
  repo: { ... }                    # Temporary repository definition when not using local config
  backup: false                    # Move the old data directory to /pg/data-backup first
  archive: true                    # Preserve archiving; exploratory recovery can set false
  db_include: [ ... ]              # Restore only selected databases
  data: /pg/data                   # Destination data directory

See Restore Operations for the complete field reference and examples.

What the Playbook Does

pgsql-pitr.yml divides recovery into six stages. Ansible tags let you run these stages separately:

Keep these details in mind:

• Incremental restore: pgBackRest uses delta to rewrite only files that differ from the backup. This can substantially reduce RTO for large databases.
• Verification: The playbook prints checkpoint LSN, timeline, and NextXID from pg_controldata. An operator must still verify the recovered business state.
• Rollback copy: backup: true moves the original data directory to /pg/data-backup before recovery. A later run with this option removes any existing /pg/data-backup. It does not retain multiple snapshot versions.
• Staged execution: Run -t down, -t pitr, and -t up separately for an operator checkpoint between phases. The pitr phase completes when PostgreSQL reaches a consistent recovery state. For time, XID, LSN, or named targets, also confirm that WAL replay reached the target.

action controls behavior at the target. promote opens a new timeline, pause waits for inspection, and shutdown stops PostgreSQL. Targeted recovery defaults to pause if you omit action. Run stages separately to retain a manual checkpoint for pause or shutdown. For a single-run recovery, explicitly select promote. The playbook performs recovery steps but cannot determine whether the business data is correct.

Command-Line Recovery with pig

The pig CLI can perform single-instance PITR directly on a database node. It needs neither an administration node nor Ansible:

pig pitr -t "2026-07-11 10:00:00+08"    # Recover to a point in time
pig pitr --xid 250000 -X                # Stop before transaction 250000
pig pitr -d                             # Replay through the WAL archive
pig pitr -I --no-restart                # Prepare immediate recovery and leave PostgreSQL stopped

pig pitr validates the target, stanza, and available backups. It stops Patroni and PostgreSQL, restores data, optionally starts PostgreSQL, and prints further instructions. For a Patroni-managed directory, Patroni stays stopped afterward. Validate the data, then run pig pt start to resume HA management. This workflow does not clear etcd, rebuild replicas, or automatically rejoin the cluster. Destructive forced shutdown requires explicit --force-stop.

pig pb wraps lower-level pgBackRest commands. pb info lists backups, pb backup creates them, and pb restore runs a raw restore. pig pb restore refuses to run while Patroni manages the instance. Patroni could otherwise restart PostgreSQL during restoration. For Patroni-managed instances, use pig pitr or pgsql-pitr.yml.

In-Place and Clone Recovery

The same mechanism supports two different workflows:

For clone recovery, cluster names the source backup stanza. This example restores historical pg-meta data into pg-test while the source remains online:

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

To recover a deleted table, exporting it from a clone and importing it into production is generally safer than restoring the whole cluster. See Clone a Database Cluster for the complete workflow and cleanup.

After Recovery

After recovery, complete these incident closeout steps:

1. New timeline, new backup: After promotion, run pg-backup full to create a recovery window on the new timeline.
2. Archiving state: If you used archive: false for investigation, restore normal archiving. See Post-Recovery.
3. Clone cleanup: A clone’s cluster identity differs from its source stanza. Recreate the destination stanza before enabling its backups. See Clone a Database Cluster.

Operators must select the target and choose in-place or clone recovery. They must also verify that the recovered data is correct. The tools execute the selected procedure. See PITR Scenarios for guidance on these decisions.

5 - PITR Scenarios

Decisions can consume much of the available incident response time. Pigsty automates recovery steps, but the operator must decide three things. Select the target, choose in-place or clone recovery, and define how to validate the result.

Read and rehearse this framework before an incident.

Decision Framework

Two principles apply throughout:

• Stop the damage first. Before selecting a target, pause the defective application or remove its write access. The recovery window keeps moving. However, restoring the wrong cluster in haste can cause another incident.
• Prefer a clone while production is usable. A clone leaves the source unchanged. You can try multiple targets and validate before export or cutover. Recovery overwrites the selected destination cluster. Use in-place recovery when the whole cluster is unusable or the business explicitly accepts rolling back every database.

flowchart TD
    A["Data error detected"] --> B["Contain the source of bad writes"]
    B --> C{"Can production still serve?"}
    C -->|Yes| D["Clone recovery<br/>validate and copy back or cut over"]
    C -->|No| E["In-place recovery<br/>or rebuild on new infrastructure"]
    D --> F["Validate, take a new backup, review the incident"]
    E --> F

Accidental DML

Common PITR cases include DELETE without WHERE, an incorrect UPDATE, and defective batch jobs.

Find the error in application logs, PostgreSQL logs, metrics, or audit records. A timestamp is usually sufficient. If you know the exact transaction ID, use xid with exclusive: true to stop immediately before it.

# If the deletion occurred around 10:15, clone the state from 10:14
./pgsql-pitr.yml -l pg-test -e '{"pg_pitr": { "cluster": "pg-meta", "time": "2026-07-11 10:14:00+08", "archive": false, "action": "promote" }}'

# If the deleting transaction was 250000, stop immediately before it
./pgsql-pitr.yml -l pg-test -e '{"pg_pitr": { "cluster": "pg-meta", "xid": "250000", "exclusive": true, "archive": false, "action": "promote" }}'

Validate the recovered rows. Copy only required data back with pg_dump, COPY, or an application-specific reconciliation procedure. A configured delayed cluster may provide faster access if it remains within its delay window.

Dropped Objects

Use the same approach for DROP TABLE, DROP DATABASE, or a migration in the wrong environment. Prefer a clone more strongly in these cases. Whole-cluster rollback to recover one object also discards every valid write after the target.

Restore a separate destination to before the DDL. Validate the object, export it with pg_dump, and import it into production. Before planned high-risk changes, create a restore point with pg_create_restore_point(). A name target avoids timestamp ambiguity.

Defective Release or Batch Corruption

A faulty release can corrupt data for hours. Identify the last correct state and the full impact. Restore a clone to candidate times and compare it with production. Then decide whether to copy corrected rows back or switch to a recovered cluster.

The application owner must validate this decision. Successful PostgreSQL recovery proves consistency at the target. It does not prove that the business state is correct.

Audit and Investigation

Historical questions, such as a month-end balance, require historical data. Restore a separate destination to a time, LSN, XID, or named point. Inspect it without changing the source.

Targeted recovery defaults to action: pause, which holds recovery at the target for inspection. This option does not configure read-only access or create a separate cluster. The inventory limit and cluster source field determine the destination workflow. For an operator checkpoint before promotion, run -t down, -t pitr, and -t up separately. Explicitly enforce read-only access if required for investigation. immediate stops at the first consistent point; it does not select a historical timestamp.

Site Loss

HA cannot help if every database host is destroyed or encrypted. The repository and other control-plane assets must survive outside that failure domain. Possible repositories include Silo/S3, another protected host or filesystem, and other tested pgBackRest backends. A remote object store is recommended. The essential requirement is survival in an independent failure domain.

Rebuild the hosts and restore the inventory, credentials, and PKI. Select the surviving repository, then restore through the latest archived WAL:

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": {"action": "promote"}}'

Inventory and backups alone are insufficient. Preserve installation media or package repositories, repository credentials, encryption passwords, CA material, custom files, and DNS dependencies. Keep an independently accessible runbook. Encrypt secrets and store them separately from database hosts and ordinary source control.

Make Recovery a Routine Drill

Do not run these procedures for the first time during a production incident. Rehearse clone recovery regularly on a disposable destination. Repeat after major architecture changes. Measure these outcomes:

1. Usability: can the backup and complete WAL chain be restored and validated?
2. RTO: how long does the actual restore and replay take now?
3. Operator readiness: can the on-call engineer identify source and destination, select a target, and follow the safety gates?

See Restore Operations and Clone a Database Cluster for the task-level runbooks.