Managing Database Backup and Recovery
1. Understanding Backup Types
| Type | Description |
|---|---|
| Full | Complete copy of all data |
| Differential | Changes since last full |
| Incremental | Changes since last backup (any) |
| Logical | SQL dump (pg_dump, mysqldump) |
| Physical | Binary files (pg_basebackup, percona xtrabackup) |
| Continuous (WAL) | Stream WAL for PITR |
| Snapshot | Storage-level (EBS, ZFS) — must be crash-consistent |
2. Creating Database Backups
| DB | Command |
|---|---|
| Postgres logical | pg_dump -Fc db > db.dump |
| Postgres physical | pg_basebackup -D /backup -Fp -X stream |
| MySQL logical | mysqldump --single-transaction db > db.sql |
| MySQL physical | xtrabackup --backup --target-dir=/bkp |
| SQL Server | BACKUP DATABASE db TO DISK='...' |
| Oracle | RMAN BACKUP DATABASE PLUS ARCHIVELOG |
3. Scheduling Automated Backups
| Tool | Detail |
|---|---|
| cron | Simple periodic jobs |
| pgBackRest / Barman | Postgres orchestrators with retention |
| AWS Backup / RDS automated | Managed retention + PITR |
| Ansible / Terraform | Infra-as-code for backup policy |
| 3-2-1 rule | 3 copies, 2 media, 1 offsite |
4. Implementing Point-in-Time Recovery
| Requirement | Detail |
|---|---|
| Base backup | Recent full physical backup |
| WAL archive | Continuous archiving (archive_mode=on) |
| Recovery target | recovery_target_time, _xid, or _lsn |
| RPO | Determined by WAL ship frequency |
| RTO | Restore + replay time |
5. Restoring Databases from Backups
Restore Procedure
- Stop application traffic / fence the cluster
- Restore base backup to fresh instance
- Configure recovery target (time/LSN)
- Replay WAL up to target
- Promote to read/write
- Validate data; redirect traffic
| DB | Restore |
|---|---|
| Postgres logical | pg_restore -d db db.dump |
| MySQL logical | mysql db < db.sql |
| SQL Server | RESTORE DATABASE FROM DISK='...' WITH NORECOVERY |
6. Testing Backup and Restore Procedures
| Practice | Detail |
|---|---|
| Automated restore drill | Weekly to disposable env |
| Verify checksums | pg_verifybackup, RMAN VALIDATE |
| Smoke tests post-restore | Row counts, key queries |
| Documented runbook | Step-by-step including auth |
| Game days | Simulate full DR scenarios |
Warning: "A backup you haven't restored isn't a backup." Test recovery on a real schedule.
7. Managing Backup Storage and Retention
| Aspect | Detail |
|---|---|
| Retention tiers | Daily 7d, weekly 4w, monthly 12m |
| Immutable storage | S3 Object Lock — ransomware defense |
| Encryption | At rest with KMS-managed keys |
| Compression | zstd, parallel gzip |
| Geographic redundancy | Cross-region replication |
8. Implementing Hot vs Cold Backups
| Type | Description |
|---|---|
| Hot (online) | DB running during backup; uses MVCC + WAL |
| Warm | Read-only mode during backup |
| Cold | DB stopped (consistent file copy) |
| Recommendation | Hot for OLTP (zero downtime) |
9. Handling Database Corruption Recovery
| Tool | DB |
|---|---|
| pg_amcheck, pg_dump --table | Postgres |
| CHECKSUM TABLE / innochecksum | MySQL |
| DBCC CHECKDB | SQL Server |
| RMAN VALIDATE / Data Recovery Advisor | Oracle |
| Page-level restore | Restore individual corrupted page from backup |
10. Documenting Recovery Procedures
| Doc | Contents |
|---|---|
| Runbook | Exact commands, paths, credentials source |
| RTO/RPO targets | Per database / tier |
| Decision tree | Which backup, which target time |
| Validation steps | Post-restore checks |
| Contacts | On-call, vendor support, escalation |