Database Backup & Recovery Cheat Sheet
Covers full, incremental, and continuous backup strategies, point-in-time recovery, and RTO/RPO planning to protect against data loss.
2 PagesIntermediateMar 18, 2026
PostgreSQL Backup & Restore
Logical and physical backup commands.
bash
# Logical backup: single database, portable across versionspg_dump -U postgres -Fc mydb > mydb_backup.dump# Restore a logical backup into a fresh databasepg_restore -U postgres -d mydb_restored --clean mydb_backup.dump# Physical backup for point-in-time recovery (base + WAL)pg_basebackup -U replicator -D /backups/base -Fp -Xs -P# Point-in-time recovery: restore base backup, then replay WAL to a target time# recovery_target_time = '2026-07-08 03:00:00'# recovery_target_action = 'promote'
MySQL Backup Tools
Logical dumps and hot physical backups.
bash
# Logical backup of a single databasemysqldump -u root -p --single-transaction --routines --triggers mydb > mydb.sql# Restoremysql -u root -p mydb < mydb.sql# Physical hot backup (Percona XtraBackup) - no downtime, supports large datasetsxtrabackup --backup --target-dir=/backups/fullxtrabackup --prepare --target-dir=/backups/fullxtrabackup --copy-back --target-dir=/backups/full
Backup Types
Common backup strategies and their trade-offs.
- Full backup- A complete copy of the entire database at a point in time; simplest to restore, largest and slowest to create
- Incremental backup- Captures only changes since the last backup (full or incremental); smaller/faster but restore requires the whole chain
- Differential backup- Captures changes since the last full backup; larger than incremental but restore only needs the full + latest differential
- Continuous archiving (WAL/binlog shipping)- Continuously streams the transaction log to backup storage, enabling point-in-time recovery to any second
- Logical vs physical backup- Logical (pg_dump/mysqldump) exports SQL statements, portable but slower; physical copies raw data files, faster but version/platform specific
RTO & RPO
Metrics that define how much data loss and downtime is acceptable.
- RPO (Recovery Point Objective)- Maximum acceptable data loss measured in time (e.g., RPO of 5 min means you can lose at most 5 minutes of writes)
- RTO (Recovery Time Objective)- Maximum acceptable downtime to restore service after a failure
- 3-2-1 rule- Keep 3 copies of data, on 2 different media types, with 1 copy stored off-site
- Backup testing- Regularly restoring backups to a scratch environment to verify they are actually usable, not just that the job succeeded
- Point-in-time recovery (PITR)- Restoring a database to an exact timestamp by replaying transaction logs on top of a base backup
Postgres PITR Recovery Configuration
Restore a base backup and replay WAL to an exact point before a mistake.
bash
# 1. Stop the server and clear the data directory, then restore the base backuprm -rf /var/lib/postgresql/data/*tar -xzf /backups/base/base.tar.gz -C /var/lib/postgresql/data# 2. Create recovery signal file (PG12+) so the server knows to enter recovery modetouch /var/lib/postgresql/data/recovery.signal# 3. Configure recovery target in postgresql.confcat >> /var/lib/postgresql/data/postgresql.conf <<EOFrestore_command = 'cp /backups/wal_archive/%f %p'recovery_target_time = '2026-07-08 03:00:00'recovery_target_action = 'promote'recovery_target_inclusive = falseEOF# 4. Start Postgres - it replays WAL up to the target time then promotes to primarypg_ctl start -D /var/lib/postgresql/data
MySQL Binlog-Based Point-in-Time Recovery
Recover from the last full backup plus binary logs up to just before a bad DELETE.
bash
# Restore the last full logical backupmysql -u root -p mydb < mydb_full_backup.sql# Find the binlog position of the accidental statementmysqlbinlog --start-datetime='2026-07-08 02:00:00' \ --stop-datetime='2026-07-08 02:59:00' \ /var/log/mysql/binlog.000123 | grep -A2 'DELETE FROM orders'# Replay binlogs from the backup point up to (but excluding) the bad statementmysqlbinlog --start-position=4 --stop-position=889213 \ /var/log/mysql/binlog.000123 | mysql -u root -p mydb# Replay the remainder of the binlog after the bad statement's positionmysqlbinlog --start-position=889350 \ /var/log/mysql/binlog.000123 | mysql -u root -p mydb
Automated Restore Drill Script
A scheduled job that restores into a scratch DB and checksums row counts to catch silent backup corruption.
bash
#!/usr/bin/env bashset -euo pipefailBACKUP_FILE=$(ls -t /backups/*.dump | head -1)SCRATCH_DB="restore_drill_$(date +%Y%m%d)"createdb "$SCRATCH_DB"pg_restore -d "$SCRATCH_DB" --no-owner "$BACKUP_FILE"# Compare row counts against a known-good manifest captured at backup timeACTUAL=$(psql -d "$SCRATCH_DB" -tAc "SELECT count(*) FROM orders")EXPECTED=$(cat /backups/manifests/orders_count.txt)if [ "$ACTUAL" -lt "$((EXPECTED * 95 / 100))" ]; then echo "ALERT: restored row count ($ACTUAL) is >5% below expected ($EXPECTED)" >&2 exit 1fidropdb "$SCRATCH_DB"echo "restore drill passed: $ACTUAL rows verified"
Replication Is Not a Backup
Common misconceptions that turn a small incident into permanent data loss.
- Streaming replication propagates mistakes too- A DROP TABLE or bad UPDATE replicates to standbys in milliseconds; only a delayed/lagged replica or a real point-in-time-recoverable backup protects against logical errors
- Delayed replica- A standby configured with recovery_min_apply_delay gives an operator a window (e.g. 6 hours) to catch and stop a bad statement before it applies
- Snapshot vs backup- A storage-level snapshot (EBS, LVM) is fast but ties you to that storage backend and provider; it is not portable and not a substitute for a tested logical/physical backup
- Backup encryption at rest- Dumps and WAL archives often contain full customer data; encrypt them (e.g. GPG, SSE-KMS on S3) and separately manage key rotation/escrow
- Immutable/WORM backup storage- Object-lock or WORM-tier storage prevents ransomware or a compromised admin credential from deleting your only backup copies
- Cross-region backup replication- A backup stored only in the same region as production doesn't protect against a regional outage or account-level compromise
Common Recovery Scenarios
Matching the failure mode to the right recovery technique.
- Accidental DROP TABLE / DELETE- Requires PITR (WAL/binlog replay) to a timestamp just before the statement, or restoring from a delayed replica; a plain full backup alone loses everything since the last backup
- Single corrupted data file / disk failure- Physical backup restore (pg_basebackup/xtrabackup) is fastest; logical restore works but rebuilds indexes from scratch, which is far slower on large tables
- Ransomware / compromised credentials- Only immutable, air-gapped or offline-copy backups are safe; anything reachable with the compromised credentials must be assumed encrypted or deleted too
- Schema migration gone wrong- Take an explicit pre-migration snapshot/dump before running DDL; PITR to 'just before the migration' is fragile if application traffic continued writing during the bad migration
- Full datacenter/region loss- Requires a cross-region replica or cross-region backup copy plus a documented failover runbook; discovering the plan mid-outage is how RTO blows past its target
- Silent data corruption (bit rot, bad RAID rebuild)- Checksummed backups (pg_dump with --verbose, xtrabackup's built-in checksum) and periodic restore drills are the only reliable detection, since corruption often isn't visible until a query touches the affected page
Pro Tip
An untested backup is not a backup — schedule automated restore drills (e.g., monthly restore-and-checksum jobs) because the most common backup failure mode is discovering a corrupt or incomplete dump only during a real outage.
Was this cheat sheet helpful?
Explore Topics
#DatabaseBackupRecovery#DatabaseBackupRecoveryCheatSheet#Database#Intermediate#PostgreSQLBackupRestore#MySQLBackupTools#BackupTypes#RTORPO#Databases#CheatSheet#SkillVeris
