Point-in-Time Recovery

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

pgbackrest_enabled: true          # enable pgBackRest on pgsql host?
pgbackrest_clean: true            # remove pg backup data during init?
pgbackrest_log_dir: /pg/log/pgbackrest # pgbackrest log dir, `/pg/log/pgbackrest` by default
pgbackrest_method: local          # pgbackrest repo method: local, minio, [user-defined...]
pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
  local:                          # default pgbackrest repo with local posix fs
    path: /pg/backup              # local backup directory, `/pg/backup` by default
    retention_full_type: count    # retention full backup by count
    retention_full: 2             # keep at most 3 full backup, at least 2, when using local fs repo
  minio:                          # optional minio repo for pgbackrest
    type: s3                      # minio is s3-compatible, so use s3
    s3_endpoint: sss.pigsty       # minio endpoint domain name, `sss.pigsty` by default
    s3_region: us-east-1          # minio region, us-east-1 by default, not used for minio
    s3_bucket: pgsql              # minio bucket name, `pgsql` by default
    s3_key: pgbackrest            # minio user access key for pgbackrest
    s3_key_secret: S3User.Backup  # minio user secret key for pgbackrest
    s3_uri_style: path            # use path style uri for minio rather than host style
    path: /pgbackrest             # minio backup path, `/pgbackrest` by default
    storage_port: 9000            # minio port, 9000 by default
    storage_ca_file: /etc/pki/ca.crt  # minio ca file path, `/etc/pki/ca.crt` by default
    bundle: y                     # bundle small files into a single file
    cipher_type: aes-256-cbc      # enable AES encryption for remote backup repo
    cipher_pass: pgBackRest       # AES encryption password, default is 'pgBackRest'
    retention_full_type: time     # retention full backup by time on minio repo
    retention_full: 14            # keep full backup for last 14 days
  # You can also add other optional backup repos, such as S3, for geo-redundant disaster recovery

s3:    # ------> /etc/pgbackrest/pgbackrest.conf
  repo1-type: s3                                   # ----> repo1-type=s3
  repo1-s3-region: us-west-1                       # ----> repo1-s3-region=us-west-1
  repo1-s3-endpoint: s3-us-west-1.amazonaws.com    # ----> repo1-s3-endpoint=s3-us-west-1.amazonaws.com
  repo1-s3-key: '<your_access_key>'                # ----> repo1-s3-key=<your_access_key>
  repo1-s3-key-secret: '<your_secret_key>'         # ----> repo1-s3-key-secret=<your_secret_key>
  repo1-s3-bucket: pgsql                           # ----> repo1-s3-bucket=pgsql
  repo1-s3-uri-style: host                         # ----> repo1-s3-uri-style=host
  repo1-path: /pgbackrest                          # ----> repo1-path=/pgbackrest
  repo1-bundle: y                                  # ----> repo1-bundle=y
  repo1-cipher-type: aes-256-cbc                   # ----> repo1-cipher-type=aes-256-cbc
  repo1-cipher-pass: pgBackRest                    # ----> repo1-cipher-pass=pgBackRest
  repo1-retention-full-type: time                  # ----> repo1-retention-full-type=time
  repo1-retention-full: 90                         # ----> repo1-retention-full=90

pg-pitr                                 # Restore to the end of WAL archive stream (e.g., for entire datacenter failure)
pg-pitr -i                              # Restore to the most recent backup completion time (rarely used)
pg-pitr --time="2022-12-30 14:44:44+08" # Restore to a specified point in time (for database or table drops)
pg-pitr --name="my-restore-point"       # Restore to a named restore point created with pg_create_restore_point
pg-pitr --lsn="0/7C82CB8" -X            # Restore to immediately before the LSN
pg-pitr --xid="1234567" -X -P           # Restore to immediately before the specified transaction ID, then promote cluster to primary
pg-pitr --backup=latest                 # Restore to the latest backup set
pg-pitr --backup=20221108-105325        # Restore to a specific backup set, backup sets can be listed with pgbackrest info

pg-pitr                                 # pgbackrest --stanza=pg-meta restore
pg-pitr -i                              # pgbackrest --stanza=pg-meta --type=immediate restore
pg-pitr -t "2022-12-30 14:44:44+08"     # pgbackrest --stanza=pg-meta --type=time --target="2022-12-30 14:44:44+08" restore
pg-pitr -n "my-restore-point"           # pgbackrest --stanza=pg-meta --type=name --target=my-restore-point restore
pg-pitr -b 20221108-105325F             # pgbackrest --stanza=pg-meta --type=name --set=20221230-120101F restore
pg-pitr -l "0/7C82CB8" -X               # pgbackrest --stanza=pg-meta --type=lsn --target="0/7C82CB8" --target-exclusive restore
pg-pitr -x 1234567 -X -P                # pgbackrest --stanza=pg-meta --type=xid --target="0/7C82CB8" --target-exclusive --target-action=promote restore

1 - How PITR Works

The core principle of PITR is: base backup + WAL archiving = recover to any point in time. In Pigsty, this is implemented by pgBackRest, running scheduled backups + WAL archiving automatically.

Three Elements

Base Backup

Base backup is a physical snapshot at a point in time, the starting point of PITR. Pigsty uses pgBackRest and provides pg-backup wrapper for common ops.

Backup Types

Pigsty Defaults

• pg-backup defaults to incremental, and auto-runs a full if none exists.
• Backup jobs are configured via pg_crontab and written to postgres crontab.
• Script detects role; only primary runs, replicas exit.

Higher backup frequency means less WAL to replay and faster recovery. See Backup Mechanism and Backup Policy.

WAL Archiving

WAL (Write-Ahead Log) records every database change. PITR relies on continuous WAL archiving to replay to the target time.

Pigsty Archiving Pipeline

Pigsty enables WAL archiving by default, using pgBackRest:

• archive_mode = on
• archive_command = pgbackrest --stanza=<cluster> archive-push %p

pgBackRest continuously receives WAL segments and cleans expired archives per retention policy. During recovery, pgBackRest uses archive-get to pull needed WAL.

Key Impacts

• Archive delay shortens the right boundary of recovery window.
• Repo unavailability interrupts archiving, directly impacting PITR.

See Backup Mechanism and Backup Repository.

Recovery Targets and Transaction Boundaries

PITR targets are defined by PostgreSQL recovery_target_* parameters, wrapped by pg_pitr / pg-pitr in Pigsty.

Target Types

Inclusive vs Exclusive

Recovery targets are inclusive by default. To roll back before the target, set exclusive: true in pg_pitr, mapping to recovery_target_inclusive = false.

Transaction Boundaries

PITR keeps committed transactions before the target, and rolls back uncommitted ones.

gantt
    title Transaction Boundaries and Recovery Target
    dateFormat X
    axisFormat %s
    section Transaction A
    BEGIN → COMMIT (committed) :done, a1, 0, 2
    section Transaction B
    BEGIN → uncommitted :active, b1, 1, 4
    section Recovery
    Recovery target :milestone, m1, 2, 0

See Restore Operations.

Recovery Window

The recovery window is defined by two boundaries:

• Left boundary: earliest available base backup
• Right boundary: latest archived WAL

Window length depends on backup frequency, backup retention, and WAL retention:

• local repo keeps 2 full backups by default, window is 24–48 hours.
• minio repo keeps 14 days by time, window is 1–2 weeks.

See Backup Policy and Backup Repository.

Timeline

Timeline distinguishes historical branches. New timelines are created by:

1. PITR restore
2. Replica promote
3. Failover

gitGraph
    commit id: "Initial"
    commit id: "Write data"
    commit id: "More writes"
    branch Timeline-2
    checkout Timeline-2
    commit id: "PITR point 1"
    commit id: "New writes"
    branch Timeline-3
    checkout Timeline-3
    commit id: "PITR point 2"
    commit id: "Continue"
    checkout main
    commit id: "Original continues"

When multiple timelines exist, you can specify timeline; Pigsty defaults to latest. See Restore Operations.

2 - PITR Architecture

Pigsty uses pgBackRest as the PostgreSQL backup and recovery engine, providing out-of-the-box Point-in-Time Recovery (PITR).

This page explains the architecture: who runs backups, where data flows, how repositories are organized, and how continuity is kept after failover.

Overview

PITR architecture has three main pipelines: backup execution, WAL archiving, restore execution.

See Backup Mechanism and Restore Operations for details.

Components and Responsibilities

Data Flow

flowchart TB
    subgraph cluster["PostgreSQL Cluster"]
        direction TB
        primary["Primary<br/>PostgreSQL"]
        pb["pgBackRest"]
        cron["pg-backup / pg_crontab"]
    end
    repo["Backup Repo<br/>local / minio / s3"]
    restore["Restore Target Data Dir"]

    cron --> pb
    primary -->|base backup| pb
    primary -->|WAL archive| pb
    pb -->|backup/archive| repo
    repo -->|restore/archive-get| pb
    pb -->|restore| restore

Key points:

• Backup is triggered by pg-backup, executing pgbackrest backup to write base backups.
• Archiving is triggered by PostgreSQL archive_command, pushing WAL segments to repo.
• Restore reads backup and WAL from repo, rebuilding data dir via pgbackrest restore.

Deployment and Roles

pgBackRest is installed on all PostgreSQL nodes, but only the primary executes backups:

• pg-backup detects node role; replicas exit directly.
• After failover, the new primary takes over backup/archiving automatically.

This decouples backup pipeline from HA topology and avoids interruptions on switchover.

Repository and Isolation

Stanza (Cluster Identity)

pgBackRest uses stanza to isolate cluster backups, mapped to pg_cluster in Pigsty:

backup-repo
├── pg-meta/
│   ├── backup/
│   └── archive/
└── pg-test/
    ├── backup/
    └── archive/

Repository Types

Pigsty selects repo type via pgbackrest_method and config via pgbackrest_repo:

Production should use remote repo (MinIO/S3) to avoid data and backups lost together on host failure. See Backup Repository.

Config Mapping

Pigsty renders pgbackrest_repo into /etc/pgbackrest/pgbackrest.conf. Backup logs are under /pg/log/pgbackrest/, restore generates temporary config and logs.

See Backup Mechanism for details.

Observability

pgbackrest_exporter exports backup status metrics (last backup time, type, size, etc), enabled by default on port 9854. You can control it with pgbackrest_exporter_enabled.

Related Docs

• Backup Mechanism
• Backup Policy
• Backup Repository
• Restore Operations

3 - PITR Tradeoffs

When designing a PITR strategy, the core tradeoffs are: backup repository location, recovery window length, and restore speed vs storage cost.

This page helps you make practical choices across these dimensions.

Local vs Remote

Repository location is the first decision in PITR strategy.

Local Repository

Store backups on primary local disk (pgbackrest_method = local):

Pros

• Simple, out-of-the-box
• Fast restore (local I/O)
• No external dependency

Cons

• No geo-DR; backups may be lost with host
• Limited by local disk capacity
• Same failure domain as production data

Remote Repository

Store backups on MinIO / S3 (pgbackrest_method = minio|s3):

Pros

• Geo-DR, backups independent from DB host
• Near-unlimited capacity, shared by multiple clusters
• Works with encryption, versioning, and other safety controls

Cons

• Restore speed depends on network bandwidth
• Depends on object storage availability
• Higher deployment and ops cost

How to Choose

See Backup Repository for details.

Space vs Window

Longer recovery window means more storage. Window length is defined by backup retention + WAL retention.

Factors

Intuitive Examples

Assume DB is 100GB, daily change 10GB:

Daily full backups (keep 2)

• Full backups: 100GB × 2 ≈ 200GB
• WAL archive: 10GB × 2 ≈ 20GB
• Total: ~2–3x DB size

Weekly full + daily incremental (keep 14 days)

• Full backups: 100GB × 2 ≈ 200GB
• Incremental: ~10GB × 12 ≈ 120GB
• WAL archive: 10GB × 14 ≈ 140GB
• Total: ~4–5x DB size

Space vs window is a hard constraint: you cannot get a longer window with less storage.

Strategy Choices

Daily Full Backup

Simplest and most reliable, also the default for local repo:

• Full backup once per day
• Keep 2 full backups
• Recovery window about 24–48 hours

Suitable when:

• DB size is small to medium (< 500GB)
• Backup window is sufficient
• Storage cost is not a concern

Full + Incremental

Space-optimized strategy, for large DBs or longer windows:

• Weekly full backup
• Incremental on other days
• Keep 14 days

Suitable when:

• Large DB size
• Using object storage
• Need 1–2 week recovery window

flowchart TD
    A{"DB size<br/>< 100GB?"} -->|Yes| B["Daily full backup"]
    A -->|No| C{"DB size<br/>< 500GB?"}
    C -->|No| D["Full + incremental"]
    C -->|Yes| E{"Backup window<br/>sufficient?"}
    E -->|Yes| F["Daily full backup"]
    E -->|No| G["Full + incremental"]

Recommended Configs

Dev/Test

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'
pgbackrest_method: local

• Window: 24–48 hours
• Characteristics: simplest and lowest cost

Production Clusters

pg_crontab:
  - '00 01 * * 1 /pg/bin/pg-backup full'
  - '00 01 * * 2,3,4,5,6,7 /pg/bin/pg-backup'
pgbackrest_method: minio

• Window: 7–14 days
• Characteristics: remote DR, production-ready

Critical Business

Dual-repo strategy (local + remote):

pgbackrest_method: local
pgbackrest_repo:
  local: { path: /pg/backup, retention_full: 2 }
  minio: { type: s3, retention_full_type: time, retention_full: 14 }

• Local repo for fast restore
• Remote repo for DR

See Backup Policy and Backup Repository for details.

4 - PITR Scenarios

The value of PITR is not just “rolling back a database”, but turning irreversible human/software mistakes into recoverable problems. It covers cases from “drop one table” to “entire site down”, addressing logical errors and disaster recovery.

Overview

PITR addresses these scenarios:

A Simple Rule of Thumb

• If writes already caused business errors, consider PITR.
• Need online verification or partial recovery → branch restore.
• Need service restored ASAP → in-place restore (accept downtime).

flowchart TD
    A["Issue discovered"] --> B{"Downtime allowed?"}
    B -->|Yes| C["In-place restore<br/>shortest path"]
    B -->|No| D["Branch restore<br/>verify then switch"]
    C --> E["Rebuild backups after restore"]
    D --> F["Verify / export / cut traffic"]

Scenario Details

Accidental DML (Delete/Update)

Typical issues:

• DELETE without WHERE
• Bad UPDATE overwrites key fields
• Batch script bugs spread bad data

Approach:

1. Stop the bleeding: pause related apps or writes.
2. Locate time point: use logs/metrics/business feedback.
3. Choose strategy:
   • Downtime allowed: in-place restore before error
   • No downtime: branch restore, export correct data back

Recommended targets:

• Known transaction: xid + exclusive: true
• Time-based only: time + exclusive: true

pg_pitr: { xid: "250000", exclusive: true }
# or
pg_pitr: { time: "2025-01-15 14:30:00+08", exclusive: true }

DDL Drops (Table/DB)

Typical issues:

• DROP TABLE / DROP DATABASE
• Wrong migration scripts
• Cleanup scripts deleted production objects

Why branch restore:

DDL is irreversible; in-place restore rolls back the whole cluster. Branch restore lets you export only the dropped objects back, minimizing impact.

Recommended flow:

1. Create branch cluster and PITR to before drop
2. Validate schema/data
3. pg_dump target objects
4. Import back to production

sequenceDiagram
    participant O as Original Cluster
    participant B as Branch Cluster
    O->>B: Create branch cluster
    Note over B: PITR to before drop
    B->>O: Dump and import objects
    Note over B: Destroy branch after verification

Batch Errors / Bad Releases

Typical issues:

• Release writes incorrect data
• ETL/batch jobs pollute large datasets
• Fix scripts fail or scope unclear

Principles:

• Prefer branch restore: verify before cutover
• Compare data diff between original and branch

Suggested flow:

1. Determine error window
2. Branch restore to before error
3. Validate key tables
4. Export partial data or cut traffic

This scenario often needs business review, so branch restore is safer and controllable.

Audit / Investigation

Typical issues:

• Need to inspect historical data state
• Compare “correct history” with current data

Recommended: branch restore (read-only)

Benefits:

• No production impact
• Try multiple time points
• Fits audit, verification, forensics

pg_pitr: { time: "2025-01-15 10:00:00+08" }  # create read-only branch

Site Disaster / Total Loss

This is the ultimate PITR fallback. When HA cannot help (primary + replicas down, power outage, ransomware), PITR is the last line of defense.

Key prerequisite:

Remote repo (MinIO/S3) is required.

Local repo is lost together with the host, so recovery is impossible.

Recovery flow:

1. Prepare new hosts or new site
2. Restore cluster config and point to remote repo
3. Run PITR restore (usually latest)
4. Validate data and restore service

./pgsql-pitr.yml -l pg-meta   # restore to end of WAL archive

In-place vs Branch Restore

For most production scenarios, branch restore is the default recommendation. Only choose in-place restore when service must be restored ASAP.

Related Docs

• Restore Operations
• Backup Mechanism
• Backup Policy