Automating PostgreSQL Disaster Recovery: Continuous WAL Archiving to S3 and Point-in-Time Recovery (PITR)

Most engineering teams believe they have a robust backup strategy because a cron job executes pg_dump every night at 02:00 AM. But when a catastrophic failure occurs—a corrupted NVMe drive, an accidental DROP TABLE in production, or a compromised migration at 04:30 PM—that nightly logical dump results in over 14 hours of permanently lost business transactions.

Furthermore, running pg_dump against a multi-terabyte production database creates heavy table read locks, consumes immense CPU, and requires painful hours or days to restore row-by-row.

True enterprise database resilience requires Physical Base Backups paired with Continuous Write-Ahead Log (WAL) Archiving. With this architecture, every single transaction committed to your database is immediately streamed to secure cloud object storage (Amazon S3 / Cloudflare R2), unlocking Point-in-Time Recovery (PITR) down to the exact second before a disaster occurred.

In this guide, we walk through configuring continuous WAL archiving with pgBackRest, pushing compressed encrypted segments to S3, and performing a live Point-in-Time Recovery drill.

Logical Dumps (pg_dump) vs. Physical Continuous Archiving

1. The Mechanics: Base Backups + WAL Streaming

PostgreSQL uses a Write-Ahead Log (WAL): before any table or index page on disk is modified, the change is written sequentially to a 16MB WAL file in pg_wal/.

When continuous archiving is active:

1. A Full Base Backup copies all raw data files while the database is actively serving queries.
2. PostgreSQL triggers archive_command whenever a 16MB WAL segment fills, immediately compressing and uploading it to S3.
3. During recovery, PostgreSQL downloads the latest base backup, enters Recovery Mode, and replays WAL transaction segments in sequential order until it reaches your specified target timestamp.

2. Installing & Configuring pgBackRest with Amazon S3 / Cloudflare R2

pgBackRest is the gold-standard backup tool for PostgreSQL: featuring parallel multi-threaded compression (LZ4/Zstandard), async WAL pushing, and direct S3 object storage integration.

Step 1: Install pgBackRest on Ubuntu/Debian

sudo apt update && sudo apt install -y pgbackrest

Step 2: Configure /etc/pgbackrest/pgbackrest.conf

[global]
# Encrypted Cloud Storage Destination
repo1-type=s3
repo1-s3-endpoint=s3.us-east-1.amazonaws.com
repo1-s3-bucket=sejan-postgres-disaster-recovery
repo1-s3-region=us-east-1
repo1-s3-key=AKIAIOSFODNN7EXAMPLE
repo1-s3-key-secret=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
repo1-path=/backups/production-db
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=YourSuperSecretMasterCipherEncryptionPassword#2026

# Retention Policy (Keep 2 full backups, 7 days of incremental)
repo1-retention-full=2
repo1-retention-diff=7

# Parallel compression with Zstandard (Fast & high ratio)
compress-type=zst
compress-level=3
process-max=4
log-level-console=info
log-level-file=detail

[main]
pg1-path=/var/lib/postgresql/16/main
pg1-user=postgres

3. Configuring PostgreSQL for Continuous Archiving (postgresql.conf)

Edit your PostgreSQL configuration file (/etc/postgresql/16/main/postgresql.conf):

# Enable WAL Archiving
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 300 # Force WAL push every 5 minutes during idle traffic periods

# Maximum background recovery processes
max_wal_senders = 10
checkpoint_completion_target = 0.9

# Restart PostgreSQL to activate archive_mode
sudo systemctl restart postgresql

# Create the backup stanza in S3 and perform initial validation
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

4. Performing Base Backups (Full & Incremental)

# 1. Create a Full Base Backup (Automate weekly on Sunday via systemd timer)
sudo -u postgres pgbackrest --stanza=main --type=full backup

# 2. Create an Incremental Backup (Automate nightly)
sudo -u postgres pgbackrest --stanza=main --type=incr backup

# 3. View backup history and WAL timeline in S3
sudo -u postgres pgbackrest --stanza=main info

5. The Disaster Drill: Executing Point-in-Time Recovery (PITR)

Imagine a developer accidentally runs DELETE FROM users WHERE 1=1; at 2026-09-07 14:32:05 UTC. We can restore the entire database down to 14:32:00 UTC—5 seconds before the catastrophic query!

Recovery Runbook:

# 1. Stop PostgreSQL service
sudo systemctl stop postgresql

# 2. Clear current broken data directory (Keep backup if investigating)
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*

# 3. Execute Point-in-Time Recovery targeting the exact second before disaster
sudo -u postgres pgbackrest --stanza=main \
    --type=time \
    --target="2026-09-07 14:32:00" \
    --target-action=promote \
    restore

# 4. Start PostgreSQL (It replays WAL segments from S3 up to the target second and promotes automatically)
sudo systemctl start postgresql

# 5. Verify database integrity
sudo -u postgres psql -c "SELECT count(*) FROM users;"

Production Disaster Recovery Checklist

Conclusion

Moving from manual logical exports to continuous Write-Ahead Log archiving with pgBackRest elevates your data infrastructure to enterprise tier. With sub-minute RPO, zero-locking physical snapshots, and millisecond-level Point-in-Time Recovery, your production databases remain fully insulated from human error, hardware failures, and data corruption.