Database Backup and Recovery Strategies

Backups Are Not Optional

"We'll back up our data later" is a lie you tell yourself. Later arrives as 3am when your database is corrupted and there's no backup.

Or worse: you have a backup, but it's corrupted too. You haven't tested restore, so you don't discover this until disaster strikes.

Database backups are insurance. Insurance is worthless if untested.

Backup Types

Full backup:

• Copy all data at a point in time.
• Size: Large (can be 100GB+ for production databases).
• Restore time: Slow (15-30 minutes for large databases).
• Frequency: Weekly or monthly (expensive to run daily for large databases).
• Best for: Archives, cold storage, compliance.

Incremental backup:

• Copy only changes since the last backup.
• Size: Small (1-10GB daily for most databases).
• Restore time: Slow (need full + all incremental backups).
• Frequency: Daily.
• Best for: Regular operational backups.

Continuous replication:

• Real-time stream of data to standby.
• No data loss (RPO = seconds).
• Restore time: Seconds (just switch traffic).
• Cost: High (standby database running 24/7).
• Best for: Critical databases where zero data loss is essential.

WAL archiving (Write-Ahead Logs):

• PostgreSQL writes changes to WAL before applying them.
• Archive WAL files to S3 continuously.
• Allows point-in-time recovery: "restore to 2:47 PM last Tuesday."
• Overhead: Minimal.
• Best for: Production databases with strict RPO.

Retention Policies

How long do you keep backups?

Regulatory default:

• Keep 7 years for financial data
• Keep 90 days for most operational data
• Keep 30 days as minimum

Cost-optimized:

• Keep 30 days of daily backups (fits in S3 Standard)
• Move backups older than 30 days to Glacier (90% cheaper)
• Delete after 90 days

Data-critical businesses:

• Keep 90 days of daily backups
• Keep 12 monthly backups (1 per month for 1 year)
• Keep 1 backup per quarter for 7 years (compliance)

Use automated lifecycle policies:

Backup created today
  ↓ After 30 days
Move to S3 Glacier (cheap, slow to access)
  ↓ After 90 days
Delete

With Glacier, 90-day retention costs $1-2 per backup. Keeping in S3 Standard costs $5-10. Lifecycle policies save money.

Backup Automation

Never manual backups. Humans forget. Automated backups are reliable.

PostgreSQL with pg_basebackup:

#!/bin/bash
# Daily backup
BACKUP_DIR="/backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
pg_basebackup -D $BACKUP_DIR/backup_$TIMESTAMP -Ft -z
# Upload to S3
aws s3 sync $BACKUP_DIR s3://backups-bucket/pg-backups/
# Cleanup old backups (keep last 30)
find $BACKUP_DIR -mtime +30 -delete

MySQL with mysqldump:

#!/bin/bash
BACKUP_DIR="/backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p$MYSQL_PASSWORD --all-databases | gzip > $BACKUP_DIR/backup_$TIMESTAMP.sql.gz
aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.sql.gz s3://backups-bucket/mysql-backups/
# Cleanup
find $BACKUP_DIR -mtime +7 -delete

Kubernetes (helm snapshots): For stateful services, use Velero to snapshot entire namespaces.

velero backup create production-$(date +%Y%m%d)
velero backup get  # List backups

Testing Backups

This is non-negotiable: test every backup.

Weekly restore test:

1. Restore backup to staging database
2. Run queries: SELECT COUNT(*) on key tables
3. Check data consistency
4. Delete staging database

Automation:

import subprocess, datetime

# Weekly (Sunday 2am)
def test_backups():
  backups = s3_list_backups(prefix='week-old')
  for backup in backups:
    restore_to_staging(backup)
    if not validate_staging():
      alert_team(f"Backup {backup} is corrupted!")
    cleanup_staging()

Real example: Team A never tested backups. After 6 months, they needed to restore. Backup was corrupted (didn't discover). No recent good backup. Lost 2 weeks of data. Customers sued.

Team B tested weekly. Same corruption occurred, discovered immediately, restored from previous week. Zero customer impact.

Point-in-Time Recovery (PITR)

For critical databases, enable PITR so you can restore to any point in time.

PostgreSQL:

wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://backups/wal/%f'

Now you can restore to Tuesday at 2:47 PM by replaying archived WAL files up to that moment.

RDS Multi-AZ: PITR is built-in. Restore to any point in last 35 days.

Cost: PITR increases storage (storing WAL files), but enables granular recovery. Worth it for production.

Checklist

• Automated daily backups
• Backups stored in separate location (different region/provider)
• Retention policy defined (30-90 days minimum)
• Weekly restore tests
• PITR enabled if RPO < 24 hours
• Backup encryption (AES-256)
• Monitoring and alerting if backup fails
• Runbook for restore procedure
• Team trained on backup/restore process