Backup Strategies That Actually Work When You Need Them

1
2
3
4
5
6
7
8

# Full database
pg_dump -Fc mydb > backup.dump

# Specific tables
pg_dump -Fc -t users -t orders mydb > partial.dump

# Schema only
pg_dump -Fc --schema-only mydb > schema.dump

1

pg_basebackup -D /backup/base -Fp -Xs -P

1
2
3

# postgresql.conf
archive_mode = on
archive_command = 'aws s3 cp %p s3://backups/wal/%f'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

#!/bin/bash
set -euo pipefail

DB_NAME="production"
BACKUP_DIR="/backups"
S3_BUCKET="s3://company-backups/postgres"
RETENTION_DAYS=30

# Create timestamped backup
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"

pg_dump -Fc "$DB_NAME" > "$BACKUP_FILE"

# Compress and upload
gzip "$BACKUP_FILE"
aws s3 cp "${BACKUP_FILE}.gz" "${S3_BUCKET}/"

# Verify upload
if aws s3 ls "${S3_BUCKET}/${DB_NAME}_${TIMESTAMP}.dump.gz"; then
    echo "Backup verified in S3"
else
    echo "BACKUP VERIFICATION FAILED" >&2
    exit 1
fi

# Clean old local backups
find "$BACKUP_DIR" -name "*.dump.gz" -mtime +7 -delete

# Clean old S3 backups (lifecycle policy is better, but this works)
aws s3 ls "$S3_BUCKET/" | while read -r line; do
    file_date=$(echo "$line" | awk '{print $1}')
    if [[ $(date -d "$file_date" +%s) -lt $(date -d "-${RETENTION_DAYS} days" +%s) ]]; then
        file_name=$(echo "$line" | awk '{print $4}')
        aws s3 rm "${S3_BUCKET}/${file_name}"
    fi
done

echo "Backup complete: ${BACKUP_FILE}.gz"

1
2
3
4
5
6
7

# Logical backup
mysqldump --single-transaction --routines --triggers \
  -u root -p mydb > backup.sql

# Binary backup with Percona XtraBackup
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full

1
2
3
4
5

# Logical backup
mongodump --uri="mongodb://localhost:27017" --out=/backup

# Continuous backup with oplog
mongodump --uri="mongodb://localhost:27017" --oplog --out=/backup

1
2
3
4
5

# rsync to backup server
rsync -avz --delete /var/www/uploads/ backup-server:/backups/uploads/

# To S3
aws s3 sync /var/www/uploads/ s3://backups/uploads/ --delete

1
2
3
4
5
6

# Git is your backup
cd /etc
git init
git add -A
git commit -m "Configuration backup $(date)"
git push origin main

1
2
3

# Backup secrets encrypted
vault kv get -format=json secret/app | \
  age -r $BACKUP_KEY > secrets_backup.age

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

# Install Velero
velero install \
  --provider aws \
  --bucket velero-backups \
  --secret-file ./credentials

# Backup entire cluster
velero backup create full-backup

# Backup specific namespace
velero backup create app-backup --include-namespaces production

# Schedule daily backups
velero schedule create daily --schedule="0 2 * * *"

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

# Velero backup spec
apiVersion: velero.io/v1
kind: Backup
metadata:
  name: production-backup
spec:
  includedNamespaces:
    - production
    - monitoring
  includedResources:
    - deployments
    - services
    - configmaps
    - secrets
    - persistentvolumeclaims
  excludedResources:
    - events
    - pods
  storageLocation: default
  ttl: 720h  # 30 days

1
2
3
4
5
6
7
8
9

# Backup etcd
ETCDCTL_API=3 etcdctl snapshot save /backup/etcd-snapshot.db \
  --endpoints=https://127.0.0.1:2379 \
  --cacert=/etc/kubernetes/pki/etcd/ca.crt \
  --cert=/etc/kubernetes/pki/etcd/server.crt \
  --key=/etc/kubernetes/pki/etcd/server.key

# Verify
ETCDCTL_API=3 etcdctl snapshot status /backup/etcd-snapshot.db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Create manual snapshot
aws rds create-db-snapshot \
  --db-instance-identifier mydb \
  --db-snapshot-identifier mydb-$(date +%Y%m%d)

# Enable automated backups
aws rds modify-db-instance \
  --db-instance-identifier mydb \
  --backup-retention-period 30 \
  --preferred-backup-window "03:00-04:00"

1
2
3
4
5
6
7

# Enable versioning
aws s3api put-bucket-versioning \
  --bucket my-bucket \
  --versioning-configuration Status=Enabled

# List versions
aws s3api list-object-versions --bucket my-bucket --prefix important-file

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

# Terraform
resource "aws_s3_bucket_replication_configuration" "backup" {
  bucket = aws_s3_bucket.primary.id
  role   = aws_iam_role.replication.arn

  rule {
    status = "Enabled"
    destination {
      bucket        = aws_s3_bucket.backup.arn
      storage_class = "STANDARD_IA"
    }
  }
}

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

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

echo "Starting monthly restore test..."

# Create test environment
createdb restore_test

# Restore latest backup
LATEST=$(aws s3 ls s3://backups/postgres/ | sort | tail -1 | awk '{print $4}')
aws s3 cp "s3://backups/postgres/${LATEST}" /tmp/
gunzip /tmp/"${LATEST}"
pg_restore -d restore_test /tmp/"${LATEST%.gz}"

# Run verification queries
psql restore_test -c "SELECT COUNT(*) FROM users;" > /tmp/restore_counts
psql production -c "SELECT COUNT(*) FROM users;" >> /tmp/restore_counts

# Compare (should be very close)
echo "User counts:"
cat /tmp/restore_counts

# Cleanup
dropdb restore_test

echo "Restore test complete. Review counts above."

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# GitHub Actions
name: Backup Verification
on:
  schedule:
    - cron: '0 6 * * 0'  # Weekly

jobs:
  verify:
    runs-on: ubuntu-latest
    steps:
      - name: Download latest backup
        run: |
          aws s3 cp s3://backups/postgres/latest.dump.gz .
          gunzip latest.dump.gz
          
      - name: Restore to test database
        run: |
          createdb test_restore
          pg_restore -d test_restore latest.dump
          
      - name: Verify data integrity
        run: |
          psql test_restore -c "SELECT COUNT(*) FROM users;"
          psql test_restore -c "SELECT MAX(created_at) FROM orders;"
          
      - name: Alert on failure
        if: failure()
        run: |
          curl -X POST $SLACK_WEBHOOK -d '{"text":"⚠️ Backup verification failed!"}'

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

## Database Recovery Procedure

### Prerequisites
- Access to AWS console or CLI
- Database credentials
- Latest backup location

### Steps

1. **Assess the damage**
   - What data is affected?
   - What's the recovery point objective (RPO)?
   - What's the recovery time objective (RTO)?

2. **Stop writes to prevent further damage**
   ```bash
   # Scale down application
   kubectl scale deployment app --replicas=0