Overview
This skill helps you implement comprehensive database backup and disaster recovery strategies. It covers automated backup scheduling, storage management, backup verification, point-in-time recovery, and disaster recovery runbooks for PostgreSQL, MySQL, MongoDB, and Redis.
Instructions
1. Assess Requirements
Determine the backup strategy based on:
- RPO (Recovery Point Objective): How much data loss is acceptable? (minutes → WAL/binlog streaming, hours → periodic dumps)
- RTO (Recovery Time Objective): How fast must recovery complete? (minutes → hot standby, hours → restore from backup)
- Database size: Small (<10GB) → full dumps; Large (>100GB) → incremental/WAL archiving
- Compliance: Retention requirements (30 days, 1 year, 7 years for financial data)
2. Implement Backup Strategy
PostgreSQL
bash
# Full logical backup (small-medium databases)
pg_dump -Fc --no-owner --no-acl -h $DB_HOST -U $DB_USER $DB_NAME | \
gzip | aws s3 cp - s3://backups/pg/$(date +%Y%m%d_%H%M%S).dump.gz
# WAL archiving for point-in-time recovery (large databases)
# postgresql.conf:
# archive_mode = on
# archive_command = 'aws s3 cp %p s3://backups/pg-wal/%f'
# Base backup + WAL for PITR
pg_basebackup -D /backups/base -Ft -z -P -h $DB_HOST -U replication
MySQL
bash
# Full logical backup
mysqldump --single-transaction --routines --triggers --all-databases \
-h $DB_HOST -u $DB_USER -p$DB_PASS | gzip > backup_$(date +%Y%m%d).sql.gz
# Binary log for point-in-time recovery
mysqlbinlog --read-from-remote-server --host=$DB_HOST --raw binlog.000001
MongoDB
bash
# Full backup with oplog for PITR
mongodump --uri="$MONGO_URI" --oplog --gzip --archive | \
aws s3 cp - s3://backups/mongo/$(date +%Y%m%d_%H%M%S).archive.gz
3. Backup Rotation Policy
Implement grandfather-father-son rotation:
- Daily: Keep last 7 days
- Weekly: Keep last 4 Sundays
- Monthly: Keep last 12 first-of-months
- Yearly: Keep indefinitely (or per compliance)
4. Backup Verification
Every backup strategy must include automated verification:
bash
# Download latest backup
# Restore to temporary database
# Run integrity checks (row counts, checksum of critical tables)
# Compare against production counts
# Alert if variance > 0.1%
# Tear down temporary database
5. Disaster Recovery Runbook
Generate a step-by-step runbook covering:
- How to identify which backup to restore
- Exact restore commands with parameters
- How to verify the restored data
- How to redirect application traffic to the restored instance
- Communication template for stakeholders
- Post-incident review checklist
Examples
Example 1: PostgreSQL backup with S3 and verification
User prompt:
Set up automated PostgreSQL backups for my production database (250GB). I need daily full backups stored in S3, WAL archiving for point-in-time recovery, and automated verification that backups are restorable.
Agent output:
bash
#!/bin/bash
# scripts/backup-postgres.sh
set -euo pipefail
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
S3_BUCKET="s3://myapp-backups/postgres"
DB_HOST="${DB_HOST:?required}"
DB_NAME="${DB_NAME:?required}"
echo "[$(date)] Starting backup of ${DB_NAME}..."
# Create base backup with checksums
pg_basebackup \
-h "$DB_HOST" -U replication \
-D /tmp/backup_${TIMESTAMP} \
-Ft -z -P --checkpoint=fast --wal-method=stream
# Upload to S3
aws s3 cp /tmp/backup_${TIMESTAMP}/base.tar.gz \
"${S3_BUCKET}/base/${TIMESTAMP}/base.tar.gz" \
--storage-class STANDARD_IA
# Upload WAL files
aws s3 sync /tmp/backup_${TIMESTAMP}/pg_wal/ \
"${S3_BUCKET}/base/${TIMESTAMP}/pg_wal/"
# Cleanup local
rm -rf /tmp/backup_${TIMESTAMP}
# Record backup metadata
echo "{\"timestamp\":\"${TIMESTAMP}\",\"type\":\"full\",\"size\":\"$(aws s3 ls ${S3_BUCKET}/base/${TIMESTAMP}/ --summarize | tail -1)\"}" >> /var/log/backup-history.jsonl
echo "[$(date)] Backup complete: ${TIMESTAMP}"
Example 2: Disaster recovery runbook
User prompt:
Create a disaster recovery runbook for our PostgreSQL database. Include steps for full restore, point-in-time recovery to a specific timestamp, and failover to a read replica.
Agent produces a detailed runbook with exact commands, estimated time per step, verification queries, and a communication template for notifying the team during an incident.
Guidelines
- Always encrypt backups at rest (S3 SSE-KMS, or gpg before upload)
- Test restores monthly — an untested backup is not a backup
- Store backups in a different region than production
- Use separate IAM credentials for backup operations with minimal permissions
- Monitor backup job completion — alert immediately if a backup fails
- Document the restore process so any team member can execute it under pressure
- For databases over 500GB, prefer incremental backups (pgBackRest, Percona XtraBackup)
- Keep backup credentials in a secrets manager, never in scripts
- Calculate actual RTO by running restore drills — estimated RTO is usually optimistic