Automated Backup and Disaster Recovery with Point-in-Time Recovery

[JamesEjembi]

Problem Statement

Database backups are taken manually or via ad-hoc cron jobs with no verification or disaster recovery testing. An automated backup system must be implemented with daily full backups, continuous WAL archiving for point-in-time recovery (PITR), periodic restore testing in a staging environment, and a runbook for disaster recovery procedures.

Technical Bounds

• Backup schedule: full backup daily at 02:00 UTC; WAL archiving every 5 minutes.
• Retention: daily full backups retained for 30 days; weekly for 6 months; monthly for 2 years.
• Storage: encrypted S3 (AES-256) with lifecycle policies.
• PITR: ability to restore to any timestamp within retention window (30 days).
• Restore testing: automated weekly restore to staging environment; run health checks against restored DB.
• RTO: < 1 hour for full recovery; RPO: < 5 minutes (WAL delay).
• Encryption: backups encrypted with KMS-managed key; separate key for restore.

Steps

1. Configure pg_backrest or wal-g for full backups and WAL archiving to S3.
2. Implement backup scheduler: cron job triggering full backup; continuous WAL push via archive_command.
3. Implement retention policy: S3 lifecycle rules for automatic tiering and deletion.
4. Implement restore testing: weekly cron spins up staging DB from latest backup, runs health check suite.
5. Write disaster recovery runbook: step-by-step for full restore, PITR, and cross-region failover.
6. Add monitoring: backup_age metric, backup_size_bytes, restore_test_success/failure.