Automated PostgreSQL Point-in-Time Recovery

Understanding Point-in-Time Recovery

PostgreSQL PITR allows you to restore your database to any specific moment, not just to when a backup was taken. This is achieved through continuous archiving of Write-Ahead Log (WAL) files combined with base backups. PITR is essential for recovering from accidental data deletion, corruption, or failed migrations.

How WAL Archiving Works

PostgreSQL writes all changes to WAL files before applying them to data files. By archiving these WAL files continuously, you create a complete record of every change. Combined with a base backup, you can replay WAL files up to any point to reconstruct the database state.

Configuring WAL Archiving

# Edit postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300
max_wal_senders = 3
wal_keep_size = 1GB

Create Archive Directory

sudo mkdir -p /var/lib/postgresql/wal_archive
sudo chown postgres:postgres /var/lib/postgresql/wal_archive
sudo chmod 700 /var/lib/postgresql/wal_archive
sudo systemctl restart postgresql

Taking Base Backups

#!/bin/bash
# /usr/local/bin/pg-base-backup.sh
BACKUP_DIR="/var/lib/postgresql/backups"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="$BACKUP_DIR/base_$DATE"

mkdir -p "$BACKUP_DIR"
sudo -u postgres pg_basebackup \
    -D "$BACKUP_PATH" -Ft -z -P \
    --checkpoint=fast --wal-method=stream \
    --label="automated_backup_$DATE"

echo "Base backup completed: $BACKUP_PATH"
find "$BACKUP_DIR" -name "base_*" -type d -mtime +7 -exec rm -rf {} +

PITR Recovery Script

#!/bin/bash
# /usr/local/bin/pg-pitr-restore.sh
# Usage: pg-pitr-restore.sh "2026-03-15 14:30:00"
set -euo pipefail

TARGET_TIME="${1:?Usage: $0 'YYYY-MM-DD HH:MM:SS'}"
PG_DATA="/var/lib/postgresql/16/main"
BACKUP_DIR="/var/lib/postgresql/backups"
WAL_ARCHIVE="/var/lib/postgresql/wal_archive"

echo "=== PostgreSQL PITR Recovery ==="
echo "Target time: $TARGET_TIME"

LATEST_BACKUP=$(ls -1t "$BACKUP_DIR" | head -1)
echo "Using base backup: $LATEST_BACKUP"

sudo systemctl stop postgresql
sudo -u postgres mv "$PG_DATA" "${PG_DATA}.old.$(date +%s)"
sudo -u postgres mkdir -p "$PG_DATA"
sudo -u postgres tar xzf "$BACKUP_DIR/$LATEST_BACKUP/base.tar.gz" -C "$PG_DATA"

# Configure recovery target
sudo -u postgres tee "$PG_DATA/postgresql.auto.conf" > /dev/null <<CONF
restore_command = 'cp $WAL_ARCHIVE/%f %p'
recovery_target_time = '$TARGET_TIME'
recovery_target_action = 'promote'
CONF

sudo -u postgres touch "$PG_DATA/recovery.signal"
sudo chown -R postgres:postgres "$PG_DATA"
sudo chmod 700 "$PG_DATA"
sudo systemctl start postgresql

echo "Monitoring recovery..."
while sudo -u postgres psql -c "SELECT pg_is_in_recovery();" 2>/dev/null | grep -q "t"; do
    sleep 5
done
echo "=== Recovery complete! ==="

pgBackRest for Enterprise PITR

sudo apt install -y pgbackrest

cat > /etc/pgbackrest/pgbackrest.conf << EOF
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=your-encryption-key
process-max=4
compress-type=zst

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

sudo -u postgres pgbackrest --stanza=mydb stanza-create
sudo -u postgres pgbackrest --stanza=mydb backup --type=full

# PITR restore
sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=mydb \
    --type=time --target="2026-03-15 14:30:00" \
    --target-action=promote restore
sudo systemctl start postgresql

Scheduling

# Full weekly, differential daily
0 1 * * 0 pgbackrest --stanza=mydb backup --type=full
0 1 * * 1-6 pgbackrest --stanza=mydb backup --type=diff

Best Practices

• Take base backups at least weekly; daily for high-change databases
• Store WAL archives on separate physical storage
• Monitor WAL archive lag to ensure PITR windows remain adequate
• Test PITR recovery quarterly on a separate server
• Document recovery procedures and keep them accessible offline
• Use pgBackRest for production with parallel backup, compression, and encryption