PostgreSQL Backup and Point-in-Time Recovery

Logical Backups with pg_dump

# Backup single database
pg_dump -U postgres mydb > mydb.sql

# Compressed backup
pg_dump -U postgres mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

# Custom format (most flexible for restore)
pg_dump -U postgres -Fc mydb > mydb.dump

# All databases
pg_dumpall -U postgres > all_databases.sql

Restore

# From SQL file
psql -U postgres mydb < mydb.sql

# From custom format
pg_restore -U postgres -d mydb mydb.dump

# Create database and restore
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored mydb.dump

Point-in-Time Recovery (PITR)

PITR lets you restore to any moment, not just when a backup was taken. Requires WAL archiving.

Enable WAL Archiving

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

Take a Base Backup

pg_basebackup -U postgres -D /backup/base -Ft -z -P

Restore to Specific Time

# In recovery.conf or postgresql.auto.conf
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-02-25 14:30:00'