Database Backup and Recovery: Point-in-Time Recovery, WAL Archiving, and Testing Restores

Editorial Team · · 3 min read

Nobody Tests Their Backups

Here's a scenario I've seen play out three times at different companies: the database goes down, someone says "just restore from the backup," and then everyone discovers the backup either doesn't exist, is corrupt, or hasn't been running for months. The restore process, which nobody has ever actually practiced, takes six hours of panicked improvisation instead of the 30 minutes it should take.

Backups are only as good as your ability to restore from them. If you haven't restored a backup in the last month, you don't have backups. You have hope.

Backup Strategies

There are three main approaches, and a production system should use at least two:

Logical backups (pg_dump/mysqldump) — Exports SQL statements or data files that can recreate the database. Human-readable, portable between versions, and can restore individual tables. But slow for large databases (a 500GB database might take hours to dump and hours to restore) and creates a point-in-time snapshot only.

# Full database dump
pg_dump -Fc -Z6 -j4 mydb > backup_$(date +%Y%m%d_%H%M).dump

# Restore (create a fresh db first)
createdb mydb_restored
pg_restore -j4 -d mydb_restored backup_20260920_0300.dump

# Table-level restore
pg_restore -j4 -d mydb -t orders backup_20260920_0300.dump

Physical backups (pg_basebackup/file system snapshots) — Copies the actual database files. Much faster for large databases. Combined with WAL archiving, enables point-in-time recovery.

# Base backup (PostgreSQL)
pg_basebackup -D /backup/base -Ft -z -P -Xs

# LVM snapshot (filesystem level)
lvcreate -s -n db_snap -L 50G /dev/vg0/db_volume
mount /dev/vg0/db_snap /mnt/backup
cp -a /mnt/backup/pgdata/ /backup/pgdata_snap/
umount /mnt/backup
lvremove /dev/vg0/db_snap

Continuous archiving (WAL archiving + PITR) — Archives every write-ahead log segment, allowing you to restore to any point in time. This is the gold standard for production PostgreSQL.

WAL Archiving and Point-in-Time Recovery

WAL (Write-Ahead Log) archiving continuously copies WAL files to a safe location. Combined with a base backup, you can restore to any moment — not just when the backup was taken, but to the exact second before the disaster.

# postgresql.conf
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
# Or ship to S3:
# archive_command = 'aws s3 cp %p s3://my-wal-bucket/%f'

For PITR recovery, you create a recovery configuration:

# recovery.signal (PostgreSQL 12+)
# postgresql.conf additions for recovery:
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-09-20 14:30:00'
recovery_target_action = 'promote'

This replays WAL files up to the specified timestamp. Someone accidentally dropped a table at 2:35 PM? Restore to 2:30 PM and extract the data.

Tools like pgBackRest and Barman automate this entire workflow — base backup scheduling, WAL archiving, retention management, and restore. If you're managing PostgreSQL in production, use one of them instead of hand-rolling scripts:

# pgBackRest configuration
[mydb]
pg1-path=/var/lib/postgresql/16/main

[global]
repo1-type=s3
repo1-s3-bucket=my-backups
repo1-s3-region=us-east-1
repo1-retention-full=4
repo1-retention-diff=14

# Create a backup
pgbackrest --stanza=mydb --type=full backup

# Restore to a point in time
pgbackrest --stanza=mydb --type=time   --target="2026-09-20 14:30:00" restore

Testing Restores

Schedule automated restore tests. Weekly at minimum, daily for critical databases. The test should:

  1. Take the latest backup
  2. Restore it to a separate server or container
  3. Run validation queries (row counts, checksum of key tables, application smoke test)
  4. Record the restore duration and success/failure
  5. Alert if it fails
#!/bin/bash
# weekly_restore_test.sh
set -e
BACKUP=$(ls -t /backup/full/*.dump | head -1)
echo "Testing restore of $BACKUP..."
dropdb --if-exists restore_test
createdb restore_test
START=$(date +%s)
pg_restore -j4 -d restore_test "$BACKUP"
END=$(date +%s)
DURATION=$((END - START))

# Validate
ROWS=$(psql -tA restore_test -c "SELECT count(*) FROM orders")
if [ "$ROWS" -lt 1000 ]; then
    echo "ALERT: restore_test has only $ROWS orders, expected > 1000"
    exit 1
fi
echo "Restore OK: ${DURATION}s, ${ROWS} orders verified"

Retention and Storage

Keep enough backups to recover from slow-developing problems (data corruption noticed a week later, gradual data loss from a bug). A reasonable retention policy:

  • Daily backups: keep 14 days
  • Weekly backups: keep 8 weeks
  • Monthly backups: keep 12 months
  • WAL archives: keep 14 days (enough for PITR within recent window)

Store backups in at least two locations. An S3 bucket with cross-region replication is the easiest way to ensure your backups survive a regional outage.