PostgreSQL PITR with pgBackRest

Point-in-Time Recovery with pgBackRest

pgBackRest is the gold standard for PostgreSQL backup and recovery. It supports full, differential, and incremental backups with parallel compression, encryption, and most importantly — Point-in-Time Recovery (PITR) that lets you restore to any second.

How PITR Works

1. pgBackRest takes periodic full/incremental backups of the data directory
2. Continuous WAL archiving captures every transaction between backups
3. During recovery, pgBackRest restores the nearest backup and replays WAL up to your target timestamp

pgBackRest Configuration

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=your-encryption-key
compress-type=zst
compress-level=3
process-max=4

[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432

Setting Up WAL Archiving

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Backup Schedule

# Full backup weekly (Sunday 2 AM)
0 2 * * 0 pgbackrest --stanza=main --type=full backup

# Differential backup daily (2 AM)
0 2 * * 1-6 pgbackrest --stanza=main --type=diff backup

Performing PITR

# Restore to a specific point in time
pgbackrest --stanza=main --type=time \
  --target="2026-02-13 14:30:00+00" \
  --target-action=promote \
  restore

Backup Verification

Always verify your backups work by restoring to a test instance:

# Verify backup integrity
pgbackrest --stanza=main verify

# Check backup info
pgbackrest --stanza=main info

Eazy SaaS Tip: Set up automated weekly restore tests to a staging database. A backup is only as good as your ability to restore from it. We've seen teams discover corrupted backups only during a real emergency.