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?

Stored Procedures & Triggers Cheat Sheet

Connection Pooling Cheat Sheet

Explore Topics

#DatabaseBackupRecovery#DatabaseBackupRecoveryCheatSheet#Database#Intermediate#PostgreSQLBackupRestore#MySQLBackupTools#BackupTypes#RTORPO#Databases#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services