Database Backup and Recovery

Question

Describe database backup strategies and how you would design a recovery plan for production databases.

Answer

Key backup types: 1) Full backups - complete database copy, resource-intensive. 2) Incremental - only changes since last backup. 3) Point-in-time recovery (PITR) - using transaction logs/WAL. Strategy: daily full backups + continuous WAL archiving for PITR. Store backups in separate region/account. Test restores regularly! Recovery plan: define RTO (Recovery Time Objective) and RPO (Recovery Point Objective), document restore procedures, automate where possible, and practice with chaos engineering.

Why This Matters

Backups are worthless if you can't restore from them. Every organization has horror stories of corrupted backups or untested restore procedures. RTO defines how quickly you must recover, RPO defines maximum acceptable data loss. These requirements drive your backup strategy - if RPO is 5 minutes, you need continuous replication, not daily backups.

Code Examples

PostgreSQL backup strategies

bash

Kubernetes CronJob for backups

yaml

Common Mistakes

Never testing restore procedures until an actual disaster
Storing backups in the same region/account as production
Not encrypting backups containing sensitive data
Ignoring backup retention policies and running out of storage

Follow-up Questions

Interviewers often ask these as follow-up questions

How do you test that backups are actually restorable?
What is the difference between RTO and RPO?
How do you handle backups for databases with terabytes of data?