Stage 3 · Build

PostgreSQL Operations

Backup Strategies

pg_dump, pg_basebackup, WAL archiving, PITR, and backup verification.

8 min readDatabases for SREBuild

Backup Types

PostgreSQL offers two backup approaches: logical (pg_dump) and physical (pg_basebackup, pgBackRest). Logical backups export data as SQL or custom format. Physical backups copy the raw data files. Each has different use cases for recovery.

Type	Tool	Recovery	Best For
Logical	pg_dump	Restore to same or different version	Small databases, migration
Physical	pg_basebackup	Exact binary copy	Large databases, fast recovery
Physical + WAL	pgBackRest	Point-in-time recovery	Production, compliance

Logical Backups with pg_dump

pg_dump patterns


# Dump entire database as SQL
pg_dump -h localhost -U postgres -d mydb > backup.sql

# Dump in custom format (compressed, compressible)
pg_dump -h localhost -U postgres -d mydb -Fc > backup.dump

# Dump specific tables
pg_dump -h localhost -U postgres -d mydb -t orders -t users > tables.sql

# Parallel dump for large databases
pg_dump -h localhost -U postgres -d mydb -Fd -j 4 -f /backup/mydb

# Restore from custom format
pg_restore -h localhost -U postgres -d mydb -Fc backup.dump

# Restore with parallelism
pg_restore -h localhost -U postgres -d mydb -j 4 /backup/mydb

The custom format (-Fc) is compressed and supports selective restore. The directory format (-Fd) enables parallel dump and restore, which can be 4-8x faster for large databases.

Use pg_dumpall for global objects

pg_dump does not export roles, tablespaces, or other global objects. Run pg_dumpall --globals-only separately to capture these. Without it, restored databases may lack required roles.

Physical Backups with pg_basebackup

pg_basebackup usage


# Full physical backup
pg_basebackup -h localhost -U replicator -D /backup/base \
  --checkpoint=fast --wal-method=stream -P

# Backup as tar archive
pg_basebackup -h localhost -U replicator -D /backup/base \
  --checkpoint=fast --wal-method=stream -Ft -z -P

# Create a replica from this backup
pg_basebackup -h primary.example.com -U replicator \
  -D /var/lib/postgresql/data \
  --checkpoint=fast --wal-method=stream -P -R

The -R flag automatically creates a standby.signal file and primary_conninfo in postgresql.conf, making the backup ready to use as a replica. --wal-method=stream ensures WAL is included in the backup.

WAL Archiving

WAL archiving copies completed WAL segments to external storage. Combined with a base backup, it enables point-in-time recovery to any moment in the archive.

Configure WAL archiving


-- Enable WAL archiving
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'cp %p /archive/wal/%f';
ALTER SYSTEM SET archive_timeout = '300';  -- force archive every 5 min

-- Restart PostgreSQL for these changes to take effect

-- Verify archiving is active
SELECT * FROM pg_stat_archiver;

archive_timeout forces a WAL segment switch even if it is not full. This limits potential data loss to at most archive_timeout seconds. Set it based on your RPO (Recovery Point Objective).

Point-in-Time Recovery

PITR recovery steps


# 1. Restore base backup
pg_basebackup -h localhost -U replicator -D /recovery/data \
  --checkpoint=fast --wal-method=stream -P

# 2. Create recovery signal file
touch /recovery/data/recovery.signal

# 3. Configure recovery target in postgresql.conf
cat >> /recovery/data/postgresql.auto.conf << EOF
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2024-06-15 14:30:00+00'
recovery_target_action = 'promote'
EOF

# 4. Start PostgreSQL — it will replay WAL to the target time
pg_ctl -D /recovery/data start

# 5. Verify recovery
SELECT now(), pg_is_in_recovery();

PostgreSQL replays WAL segments until reaching the recovery target. recovery_target_action = promote means the server becomes writable after recovery. Use pause to inspect before promoting.

Test your backups regularly

An untested backup is not a backup. Schedule regular restore tests to verify: 1) backup completes successfully, 2) restore completes without errors, 3) data is consistent and queryable. Automate this in your CI/CD pipeline.

Backup Verification

Automated backup verification


#!/usr/bin/env bash
# backup-verify.sh — Restore backup to a temporary instance and run checks

set -euo pipefail

BACKUP_FILE="$1"
VERIFY_DB="verify_$(date +%s)"
VERIFY_PORT=5433

# Restore backup
createdb -p $VERIFY_PORT $VERIFY_DB
pg_restore -p $VERIFY_PORT -d $VERIFY_DB $BACKUP_FILE

# Run consistency checks
psql -p $VERIFY_PORT -d $VERIFY_DB -c "
  SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
  FROM pg_tables
  WHERE schemaname = 'public'
  ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
  LIMIT 10;
"

# Check row counts are reasonable
psql -p $VERIFY_PORT -d $VERIFY_DB -c "
  SELECT relname, n_live_tup
  FROM pg_stat_user_tables
  WHERE n_live_tup > 0
  ORDER BY n_live_tup DESC;
"

# Cleanup
dropdb -p $VERIFY_PORT $VERIFY_DB
echo "Backup verification passed"

This script restores a backup to a separate PostgreSQL instance, checks table sizes and row counts, and cleans up. Run it nightly as a cron job to catch backup corruption early.

3-2-1 backup rule

Keep 3 copies of your data, on 2 different storage types, with 1 offsite. For PostgreSQL: the live database, a local backup (pg_basebackup + WAL), and a remote copy (S3, GCS). This protects against hardware failure, accidental deletion, and site-wide disasters.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.