Automated Backup and Disaster Recovery #167
Description
Story Statement
As a platform operations engineer
I want automated backup and disaster recovery for the knowledge service
So that KB content is protected against data loss and can be rapidly restored after failures
Where: Knowledge service infrastructure — backup and recovery automation
Epic Context
Parent Epic: Platform Hardening & Enterprise Readiness #68
Status: Refined
Priority: P1 (Should-Have)
Status Workflow
- Refined: Story is detailed, estimated, and ready for development
- In Progress: Story is actively being developed
- Done: Story delivered and accepted
Acceptance Criteria
Functional Requirements
-
Given the knowledge service is running in production
When the daily backup cron runs at 02:00 UTC
Then a full PostgreSQL backup is created, compressed, encrypted, and stored in the backup S3 bucket with timestamp naming -
Given continuous WAL archiving is enabled
When any DB transaction commits
Then WAL segments are streamed to the backup S3 bucket, enabling point-in-time recovery within the RPO window (<1 hour) -
Given a disaster occurs and data is lost
When an ops engineer runs the restore procedure
Then the service is restored to a specific point-in-time within RTO (<4 hours): DB restored from backup + WAL replay, S3 packages restored from cross-region replica -
Given automated backup verification runs weekly
When the verification job executes
Then it restores the latest backup to a test environment, runs validation checks (table counts, sample data integrity), and reports pass/fail -
Given a backup job fails
When the failure is detected
Then an alert fires immediately (critical severity); ops engineer is notified via all configured channels -
Given backup retention policy (30 days daily, 12 months monthly)
When the cleanup job runs
Then expired backups are deleted from the backup bucket; active backups are never deleted
Business Rules
- RPO: <1 hour (WAL archiving); RTO: <4 hours (documented restore procedure)
- Backup encryption: uses the same KMS key as production data (from Data Encryption at Rest and in Transit #166)
- Daily full backup + continuous WAL archiving
- S3 cross-region replication for KB packages (separate from DB backup)
- Backup verification: weekly automated restore test to isolated environment
- Retention: 30 daily + 12 monthly backups
- Restore procedure: documented runbook with step-by-step commands
Edge Cases and Error Handling
- Backup storage full: Alert before reaching 90% capacity; auto-cleanup of oldest expired backups
- WAL archiving lag: Alert if lag exceeds 15 minutes; investigate replication slot
- Restore to point-in-time with corrupted WAL: Fall back to nearest daily backup; report data loss window
- Cross-region replication delay: Monitor replication lag; alert if >1 hour
Definition of Done Checklist
Development Completion
- All 6 acceptance criteria implemented and verified
- Daily backup cron job (pg_dump or WAL-G)
- Continuous WAL archiving to S3
- S3 cross-region replication for packages
- Restore procedure script (one-command)
- Backup verification weekly job
- Backup retention cleanup job
- Backup monitoring alerts
- DR runbook documentation
- Integration test: backup → tamper → restore → verify
Quality Assurance
- Backup completes within 1-hour maintenance window
- Restore tested and verified <4 hours RTO
- WAL archiving lag <15 minutes under normal load
- Backup verification catches corruption
Deployment and Release
- Backup S3 bucket configured with encryption and versioning
- Cross-region replication enabled for packages bucket
- Cron jobs scheduled and monitored
- DR runbook reviewed by ops team
Story Sizing and Sprint Readiness
Refined Story Points
Final Story Points: XL(10)
Confidence Level: Medium
Sizing Justification: DB backup automation, WAL archiving, S3 replication, restore scripting, verification automation, monitoring. Significant infrastructure work. Complexity depends on managed vs self-hosted DB.
Sprint Capacity Validation
Sprint Fit Assessment: Tight for single sprint
Total Effort Assessment: Borderline
Story Splitting Recommendations
- Automated Backup and Disaster Recovery #167-A: Daily backup + WAL archiving + restore script + DR runbook (XL(8))
- Automated Backup and Disaster Recovery #167-B: Backup verification + retention + monitoring + S3 replication (M(3))
Dependencies and Coordination
Story Dependencies
Prerequisite Stories: #166 (Encryption — backups must be encrypted), Epic #66 #149 (DB schema established)
Dependent Stories: None
External Dependencies
Infrastructure Requirements: Backup S3 bucket (separate from production), secondary region for replication
Validation and Testing Strategy
Acceptance Testing Approach
Testing Methods: Full DR drill: create data → backup → destroy → restore → verify data; WAL replay test; backup verification automation test
Test Data Requirements: Representative dataset for restore timing verification
Environment Requirements: Isolated restore environment, backup S3 bucket
Notes
Refinement Insights: If using managed DB (RDS), automated backups and WAL archiving are built-in — work focuses on configuration, monitoring, and restore scripting.
Technical Analysis
Implementation Approach
Technical Strategy: Use WAL-G (or native RDS backups for managed) for DB backup + WAL archiving. S3 cross-region replication via bucket policy. Restore script: stop service → restore DB from WAL-G → verify → restart. Verification: weekly cron that restores to test DB and runs integrity checks.
Key Components: Backup cron (WAL-G or pg_dump), WAL archiver, restore script, verification job, retention cleanup, backup monitoring alerts
Data Flow: DB → WAL archiving → S3 (continuous) | Daily cron → full backup → S3 | Weekly → restore to test → validate → report
Technical Requirements
- WAL-G for PostgreSQL backup and WAL archiving (or RDS automated backups)
- Backup bucket:
s3://pair-backups/{org}/{type}/{timestamp}/with SSE-KMS encryption - Cross-region replication: S3 replication rule on packages bucket
- Restore script: bash/TypeScript script that automates: fetch backup → pg_restore → WAL replay → health check
- Verification: cron that runs restore →
SELECT count(*) FROM organizations+ sample checks → report
Technical Risks and Mitigation
| Risk | Impact | Probability | Mitigation Strategy |
|---|---|---|---|
| Restore takes longer than RTO under large data | High | Medium | Regular DR drills; optimize pg_restore parallelism |
| WAL archiving causes DB performance degradation | Medium | Low | Monitor WAL lag; tune checkpoint frequency |
Spike Requirements
Required Spikes: None