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
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
