backup-restore

Skill: Backup & Restore

Expertise: pgBackRest full/WAL/incremental backup, PITR, S3/MinIO storage, CronJob scheduling, CloudNativePG backup CRDs.

When to load

When configuring backup infrastructure, running a restore, verifying backup integrity, or recovering from data loss.

pgBackRest Configuration

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-path=/postgres-backups
repo1-s3-bucket=mycompany-db-backups
repo1-s3-endpoint=s3.eu-west-1.amazonaws.com
repo1-s3-region=eu-west-1
repo1-s3-key=<AWS_ACCESS_KEY>
repo1-s3-key-secret=<AWS_SECRET_KEY>
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<STRONG_PASSPHRASE>    # rotate annually
repo1-retention-full=4                  # keep 4 full backups
repo1-retention-diff=14                 # keep 14 differential backups

[global:archive-push]
compress-level=3

[production-db]
pg1-path=/var/lib/postgresql/data
pg1-host=postgres-primary
pg1-host-user=postgres

PostgreSQL: Enable WAL Archiving

-- postgresql.conf (or via ALTER SYSTEM)
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=production-db archive-push %p';
ALTER SYSTEM SET archive_timeout = '300';   -- archive segment at least every 5 min
SELECT pg_reload_conf();

Backup Commands

# Initialize stanza (run once)
pgbackrest --stanza=production-db stanza-create

# Full backup (schedule: weekly)
pgbackrest --stanza=production-db --type=full backup

# Differential backup (changes since last full — schedule: daily)
pgbackrest --stanza=production-db --type=diff backup

# Incremental backup (changes since last backup — schedule: hourly)
pgbackrest --stanza=production-db --type=incr backup

# Check backup catalog
pgbackrest --stanza=production-db info

# Verify backup integrity (reads and checksums)
pgbackrest --stanza=production-db --set=<backup-label> check

PITR Restore (Point-in-Time Recovery)

# PITR to specific timestamp (e.g., 1 minute before accidental DELETE)
# Step 1: Stop PostgreSQL
systemctl stop postgresql

# Step 2: Restore
pgbackrest --stanza=production-db \
  --delta \
  --target="2024-11-15 03:40:00+00" \
  --target-action=promote \
  --target-timeline=current \
  restore

# Step 3: Start PostgreSQL in recovery mode
systemctl start postgresql

# Step 4: Monitor recovery progress
tail -f /var/log/postgresql/postgresql.log
# Watch for: "recovery stopping before commit of transaction"
# Then:      "database system is ready to accept connections"

# Step 5: Verify data integrity
psql -c "SELECT count(*) FROM orders WHERE created_at > '2024-11-15 03:39:00';"