Recipe: DB backup + restore strategy writer

Ingredients

• Database engine with native dump tool (pg_dump, mysqldump, etc.)
• Object storage bucket with versioning enabled
• Symmetric encryption key stored in secrets manager
• Monitoring alert channel (Slack, email, PagerDuty)
• Orchestration runtime (cron, systemd timer, K8s CronJob)

Steps

1. Define RPO and RTO. Decide maximum acceptable data loss (RPO) and maximum downtime (RTO). These numbers drive schedule frequency and restore architecture.
2. Write the dump script. Use the engine's consistent-snapshot dump tool. Pipe output through gzip and an AEAD encryption step before writing to disk.
3. Schedule with jitter. Avoid running backups at exactly the top of the hour. Add randomized offset to prevent thundering-herd I/O spikes.
4. Upload to object storage. Push the encrypted archive to a versioned bucket. Set a lifecycle rule to transition old backups to cold storage after N days.
5. Retention policy. Keep daily backups for 30 days, weekly for 12 weeks, monthly for 12 months. Automate pruning with a separate cleanup job.
6. Write the restore script. A single command must decrypt, decompress, and load the dump into a fresh instance. No manual steps.
7. Test restore weekly. Spin up an ephemeral instance, run the restore script, and verify row counts match. Alert on any failure.
8. Document the runbook. Store it alongside the scripts. Include contact info for the on-call engineer and the encryption key recovery procedure.

Validation

After implementing, trigger a simulated disaster: drop a table in a staging environment, run the full restore procedure, and confirm the table returns with all rows intact. Record the wall-clock time and compare against your RTO target.