Backup Strategies That Actually Work When You Need Them

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# PostgreSQL - logical backup
pg_dump -Fc dbname > backup.dump

# PostgreSQL - base backup for PITR
pg_basebackup -D /backup/base -Fp -Xs -P

# MySQL
mysqldump --single-transaction --routines dbname > backup.sql

# MongoDB
mongodump --archive=backup.archive --gzip

1
2
3
4
5

# What to capture
/etc/                    # System config
/home/*/                 # User data
/var/lib/docker/volumes/ # Container data
/opt/app/uploads/        # User uploads

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# Terraform state
terraform state pull > terraform.tfstate.backup

# Or better: use remote state with versioning
terraform {
  backend "s3" {
    bucket         = "terraform-state"
    key            = "prod/terraform.tfstate"
    region         = "us-east-1"
    encrypt        = true
    dynamodb_table = "terraform-locks"
  }
}

1
2
3
4

# Example cron schedule
0 * * * *   /scripts/backup-hourly.sh   # User uploads
0 3 * * *   /scripts/backup-daily.sh    # Full database
0 4 * * 0   /scripts/backup-weekly.sh   # Everything

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

#!/bin/bash
# verify-backup.sh

BACKUP_FILE=$1

# Check file exists and has size
if [[ ! -s "$BACKUP_FILE" ]]; then
    echo "ERROR: Backup file missing or empty"
    exit 1
fi

# Verify archive integrity
if [[ "$BACKUP_FILE" == *.gz ]]; then
    gzip -t "$BACKUP_FILE" || exit 1
fi

# For pg_dump custom format
if [[ "$BACKUP_FILE" == *.dump ]]; then
    pg_restore --list "$BACKUP_FILE" > /dev/null || exit 1
fi

echo "Backup verified: $BACKUP_FILE"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#!/bin/bash
# test-restore.sh - Run weekly

# Spin up test database
docker run -d --name restore-test \
    -e POSTGRES_PASSWORD=test \
    postgres:15

sleep 10

# Restore latest backup
LATEST=$(ls -t /backups/*.dump | head -1)
pg_restore -h localhost -U postgres -d postgres "$LATEST"

# Run validation queries
psql -h localhost -U postgres -c "SELECT COUNT(*) FROM users;"
psql -h localhost -U postgres -c "SELECT COUNT(*) FROM orders;"

# Compare counts to production
# Alert if significantly different

# Cleanup
docker rm -f restore-test

1
2
3
4
5

# retention.conf
hourly_keep=24      # Keep 24 hourly backups
daily_keep=7        # Keep 7 daily backups
weekly_keep=4       # Keep 4 weekly backups
monthly_keep=12     # Keep 12 monthly backups

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

#!/bin/bash
# cleanup-old-backups.sh

BACKUP_DIR=/backups

# Remove hourly backups older than 24 hours
find "$BACKUP_DIR/hourly" -mtime +1 -delete

# Remove daily backups older than 7 days
find "$BACKUP_DIR/daily" -mtime +7 -delete

# Remove weekly backups older than 28 days
find "$BACKUP_DIR/weekly" -mtime +28 -delete

# Remove monthly backups older than 365 days
find "$BACKUP_DIR/monthly" -mtime +365 -delete

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Encrypt with GPG
pg_dump dbname | gzip | gpg --encrypt -r backup@company.com > backup.sql.gz.gpg

# Encrypt with OpenSSL (symmetric)
pg_dump dbname | gzip | openssl enc -aes-256-cbc -salt -pbkdf2 \
    -pass file:/etc/backup-key > backup.sql.gz.enc

# Decrypt
openssl enc -d -aes-256-cbc -pbkdf2 \
    -pass file:/etc/backup-key < backup.sql.gz.enc | gunzip > backup.sql

1
2
3
4

# Upload with server-side encryption
aws s3 cp backup.tar.gz s3://backups/daily/ \
    --sse AES256 \
    --storage-class STANDARD_IA

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

{
  "Rules": [
    {
      "ID": "TransitionToGlacier",
      "Status": "Enabled",
      "Transitions": [
        {
          "Days": 30,
          "StorageClass": "GLACIER"
        }
      ],
      "Expiration": {
        "Days": 365
      }
    }
  ]
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# Terraform - replicate to another region
resource "aws_s3_bucket_replication_configuration" "backup" {
  bucket = aws_s3_bucket.backup.id
  role   = aws_iam_role.replication.arn

  rule {
    id     = "replicate-all"
    status = "Enabled"

    destination {
      bucket        = aws_s3_bucket.backup_replica.arn
      storage_class = "STANDARD_IA"
    }
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# Enable WAL archiving in postgresql.conf
archive_mode = on
archive_command = 'aws s3 cp %p s3://wal-archive/%f'

# Recovery: restore base backup + replay WAL
pg_basebackup -D /var/lib/postgresql/data -Fp -Xs -P

# recovery.signal triggers recovery mode
touch /var/lib/postgresql/data/recovery.signal

# postgresql.auto.conf
restore_command = 'aws s3 cp s3://wal-archive/%f %p'
recovery_target_time = '2026-03-05 10:30:00'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# Enable binary logging in my.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800

# Backup binlogs
mysqlbinlog --read-from-remote-server \
    --host=db.example.com \
    --raw \
    --result-file=/backups/binlog/ \
    mysql-bin.000001

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

#!/bin/bash
# check-backup-freshness.sh

MAX_AGE_HOURS=25
BACKUP_DIR=/backups/daily

LATEST=$(ls -t "$BACKUP_DIR"/*.dump 2>/dev/null | head -1)

if [[ -z "$LATEST" ]]; then
    echo "CRITICAL: No backups found"
    exit 2
fi

AGE_HOURS=$(( ($(date +%s) - $(stat -c %Y "$LATEST")) / 3600 ))

if [[ $AGE_HOURS -gt $MAX_AGE_HOURS ]]; then
    echo "WARNING: Latest backup is ${AGE_HOURS}h old"
    exit 1
fi

echo "OK: Latest backup is ${AGE_HOURS}h old"
exit 0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Database Restore Runbook

## Prerequisites
- Access to S3 backup bucket
- Database admin credentials
- Target server with PostgreSQL installed

## Steps

1. Identify latest good backup