PostgreSQL backup and restore strategies

Need reliable database backup strategies for PostgreSQL: logical backups, physical backups, and point-in-time recovery.

PostgreSQL backup approaches:

# 1. LOGICAL BACKUP (pg_dump)
pg_dump -h localhost -U postgres mydb > backup.sql
pg_dump -Fc mydb > backup.dump  # Custom format (compressed)
pg_dumpall > all_databases.sql  # All databases

# Specific tables
pg_dump -t users -t orders mydb > tables.sql

# RESTORE
psql mydb < backup.sql
pg_restore -d mydb backup.dump
pg_restore -d mydb -j 4 backup.dump  # Parallel restore

# 2. PHYSICAL BACKUP (pg_basebackup)
pg_basebackup -h localhost -D /backups/base -Ft -z -P

# 3. CONTINUOUS ARCHIVING (Point-in-Time Recovery)
# postgresql.conf:
#   wal_level = replica
#   archive_mode = on
#   archive_command = 'cp %p /backups/wal/%f'

# 4. AUTOMATED BACKUP SCRIPT
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/backups/postgres

pg_dump -Fc -h localhost -U postgres mydb \
  > "$BACKUP_DIR/mydb_$DATE.dump"

# Keep only last 7 days
find "$BACKUP_DIR" -name '*.dump' -mtime +7 -delete

# Verify backup is valid
pg_restore --list "$BACKUP_DIR/mydb_$DATE.dump" > /dev/null 2>&1
if [ $? -eq 0 ]; then
  echo "Backup verified: mydb_$DATE.dump"
else
  echo "ERROR: Backup verification failed!"
  exit 1
fi

Backup strategy:

• Daily: pg_dump (custom format, compressed)
• Continuous: WAL archiving for PITR
• Test restores regularly (untested backups aren't backups)

Backups are your last line of defense against data loss. pg_dump for small-medium DBs, pg_basebackup + WAL archiving for large DBs and point-in-time recovery needs.

Database administration and disaster recovery