Database Backup Strategy
Designs reliable database backup and disaster recovery strategies for production systems. Covers backup types (full, incremental, differential), scheduling and retention policies, point-in-time recovery (PITR), WAL archiving, cross-region replication, backup encryption, automated verification with restore tests, and achieving specific RTO (Recovery Time Objective) and RPO (Recovery Point Objective) targets.
Usage
Describe your database engine, data size, growth rate, acceptable data loss window (RPO), and maximum downtime tolerance (RTO). Specify your infrastructure (self-hosted, RDS, Cloud SQL) and any compliance requirements for data retention. The skill designs a complete backup strategy with scripts, schedules, and recovery procedures.
Examples
- "Design a backup strategy for a 500GB PostgreSQL database with RPO of 1 hour and RTO of 30 minutes"
- "Create a pg_basebackup script with WAL archiving for point-in-time recovery to any moment in the last 7 days"
- "Build a mysqldump-based backup system for a 50GB MySQL database with daily fulls and hourly binlog backups"
- "Design cross-region backup replication for disaster recovery with automated failover testing"
Guidelines
- Combine full backups (weekly) with incremental/WAL backups (continuous) for optimal RPO with manageable storage
- Use pg_basebackup + WAL archiving for PostgreSQL PITR, or pg_dump for logical backups of individual databases
- Encrypt backups at rest (AES-256) and in transit (TLS) — backup files are a high-value target for attackers
- Store backups in at least 2 locations: local for fast restore, remote (S3, GCS) for disaster recovery
- Automate restore testing weekly: spin up a test instance, restore the latest backup, run validation queries
- Define retention tiers: 7 daily, 4 weekly, 12 monthly backups balances recovery options with storage costs
- Monitor backup job success/failure with alerts — a backup system that fails silently is worse than no backups
- Document the exact recovery procedure as a runbook with step-by-step commands tested by the on-call team