Database Backup and Recovery Strategies for Beginners

Data loss can occur due to hardware failures, human errors, or security breaches. Having a solid backup strategy is the single most important thing a database administrator can do.

Recovery Time by Backup Type

Recovery Time (minutes)

120

Full Backup

Incremental

Differential

Continuous WAL

Continuous WAL (Write-Ahead Log) archiving gives the fastest recovery point.

PostgreSQL Backup Commands

# Full database backup (SQL format)
pg_dump -U username -d database_name > backup.sql

# Compressed binary format (preferred for large databases)
pg_dump -U username -d database_name -F c -f backup.dump

# Backup a single table
pg_dump -U username -d database_name -t table_name > table.sql

# Dump all databases
pg_dumpall -U username > all_databases.sql

# Automated nightly backup script
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres mydb | gzip > /backups/mydb_${DATE}.sql.gz
find /backups -name "*.gz" -mtime +7 -delete   # retain 7 days

Recovery Procedures

# Restore from SQL file
psql -U username -d database_name < backup.sql

# Restore from compressed dump
pg_restore -U username -d database_name backup.dump

# Restore to a new database (safe — doesn't touch the existing one)
createdb -U username new_database
psql -U username -d new_database < backup.sql

Backup Strategy Checklist

Task	Frequency	Retention
Full backup	Daily	30 days
Incremental backup	Every 6 hours	7 days
Transaction logs (WAL)	Continuous	24 hours
Test restore drill	Weekly	—

Best Practices

3-2-1 rule: 3 copies, 2 different media, 1 off-site (cloud)
Test restore procedures before you ever need them
Document every step of your recovery runbook
Monitor backup job success/failure with alerts
Encrypt backups that contain personal or financial data