Database Backup and Restore Strategies

Database backup and restore is the practice of creating recoverable copies of database data to protect against data loss from hardware failures, software bugs, human errors, and security incidents — using strategies like full, incremental, and differential backups with point-in-time recovery.

What You’ll Learn

You’ll master logical backups (pg_dump, mysqldump), physical backups (WAL archiving), full/incremental/differential strategies, point-in-time recovery (PITR), backup verification, retention policies, and disaster recovery testing.

Why Backup and Restore Matters

Data loss isn’t a matter of if, but when. Durga Antivirus Pro maintains threat signature databases that must be recoverable to any point in the last 30 days. DodaZIP’s user settings and file indexes need daily protection. Without tested backups, a single DROP TABLE or ransomware attack can destroy years of data irrevocably.

Backup and Restore Learning Path

    flowchart LR
  A[Database Design] --> B[Database Migrations]
  B --> C[Replication]
  C --> D[Backup and Restore]
  D:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px
  

Logical vs Physical Backups

Logical Backups

Logical backups export data as SQL statements or delimited files. They’re human-readable and portable across versions.

# PostgreSQL: pg_dump
pg_dump -h localhost -U app_user -F c -f mydb_backup.dump mydb

# MySQL: mysqldump
mysqldump -h localhost -u root -p mydb > mydb_backup.sql

# PostgreSQL: Custom format (compressed, parallel restore)
pg_dump -h localhost -U app_user -F d -j 4 -f mydb_dir/ mydb

Pros: Portable, selective restore (single table), readable. Cons: Slow for large databases, locks tables during dump.

Physical Backups

Physical backups copy database files directly. They’re faster and support point-in-time recovery.

# PostgreSQL: pg_basebackup (physical)
pg_basebackup -h localhost -D /backups/pg_full/ -U replicator -P -v

# MySQL: Raw file copy (requires file system snapshots)
# 1. Use LVM snapshot or XFS freeze
# 2. Copy data directory
cp -r /var/lib/mysql /backups/mysql_raw/

Pros: Fast, supports PITR, minimal locking. Cons: Version-specific, larger, can’t restore single tables easily.

Full, Incremental, and Differential Backups

    flowchart TD
    subgraph Weekly Strategy
        A[Sunday: Full] --> B[Monday: Incremental]
        B --> C[Tuesday: Incremental]
        C --> D[Wednesday: Incremental]
        D --> E[Thursday: Incremental]
        E --> F[Friday: Incremental]
        F --> G[Saturday: Incremental]
    end
  

Full Backup

Every byte of data. Complete restore from a single file.

# Weekly full backup
pg_dump -h localhost -U app_user -F c -f "weekly_$(date +%Y%m%d).dump" mydb

Incremental Backup

Only data changed since the last backup (any type). Fastest backup, slowest restore.

Differential Backup

Only data changed since the last full backup. Faster restore than incremental since you only need the full + latest differential.

# Backup strategy with pgBackRest
pgbackrest --stanza=mydb --type=full backup          # Sunday
pgbackrest --stanza=mydb --type=diff backup           # Monday-Thursday
pgbackrest --stanza=mydb --type=incr backup           # Hourly

WAL Archiving and Point-in-Time Recovery (PITR)

WAL (Write-Ahead Log) archiving enables recovery to any point in time, not just the last backup.

PostgreSQL WAL Archiving Setup

Step 1: Configure postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'
archive_timeout = 60  # Archive every 60 seconds

Step 2: Take a base backup

pg_basebackup -h localhost -D /backups/base/ -U replicator -P -v

Step 3: Recover to a point in time

# 1. Restore base backup
cp -r /backups/base/ /var/lib/postgresql/data/

# 2. Create recovery.conf (PostgreSQL 11 and earlier)
echo "restore_command = 'cp /wal_archive/%f %p'" > recovery.conf
echo "recovery_target_time = '2025-06-15 14:30:00 UTC'" >> recovery.conf

# PostgreSQL 12+: Use recovery.signal + postgresql.conf settings

# 3. Start PostgreSQL — it replays WAL to the target time
pg_ctl start -D /var/lib/postgresql/data/

MySQL Point-in-Time Recovery

# Enable binary logging (my.cnf)
log_bin = /var/log/mysql/mysql-bin.log

# Full backup
mysqldump --all-databases --master-data=2 > full_backup.sql

# Apply binary logs up to a specific time
mysqlbinlog --stop-datetime="2025-06-15 14:30:00" /var/log/mysql/mysql-bin.* | mysql -u root

Backup Verification

An untested backup is not a backup. Verify your backups regularly.

Automated Verification Script

#!/bin/bash
# verify_backup.sh — Run after every backup

DB_NAME="mydb"
BACKUP_FILE=$1

echo "Verifying backup: $BACKUP_FILE"

# Step 1: Restore to a temporary database
createdb "${DB_NAME}_verify"
pg_restore -d "${DB_NAME}_verify" -F c "$BACKUP_FILE" > /dev/null 2>&1

if [ $? -eq 0 ]; then
    echo "✅ Restore succeeded"
else
    echo "❌ Restore failed"
    exit 1
fi

# Step 2: Run integrity checks
psql -d "${DB_NAME}_verify" -c "SELECT count(*) FROM information_schema.tables;" > /dev/null
psql -d "${DB_NAME}_verify" -c "SELECT count(*) FROM users;" > /dev/null

# Step 3: Clean up
dropdb "${DB_NAME}_verify"

echo "✅ Backup verification complete"

Verification schedule:

• Daily: Check backup file exists, size is reasonable
• Weekly: Restore to a test database, run integrity checks
• Monthly: Full disaster recovery drill (different server, different region)

Retention Policies

A retention policy defines how long backups are kept and when they’re deleted.

# pgBackRest retention example
[global]
repo1-retention-full=4       # Keep 4 full backups
repo1-retention-diff=4       # Keep 4 differentials
repo1-retention-archive=30   # Keep 30 days of WAL archives

3-2-1 Backup Rule

• 3 copies of your data
• 2 different storage types (local + cloud)
• 1 copy off-site

Disaster Recovery Testing

A disaster recovery (DR) drill proves your backups actually work.

DR Drill Checklist

1. Scenario: Production database server is completely destroyed
2. Objective: Restore service within 4 hours (RTO) with max 15 minutes of data loss (RPO)
3. Steps:

# Step 1: Provision a new server
aws ec2 run-instances --image-id ami-xxx --instance-type db.m5.large

# Step 2: Restore the latest full backup
pg_restore -d mydb -F c /backups/latest_full.dump

# Step 3: Apply WAL archives (PITR) or incremental backups
pgbackrest --stanza=mydb --type=incr restore

# Step 4: Verify data integrity
psql -d mydb -c "SELECT count(*) FROM users;"
psql -d mydb -c "SELECT max(created_at) FROM orders;"

# Step 5: Switch application traffic
# Update DNS, load balancer, or connection string

# Step 6: Measure and record
echo "RTO: $(elapsed_minutes) minutes"
echo "RPO: $(data_loss_minutes) minutes"

KPIs to track:

• RTO (Recovery Time Objective): How long to restore service
• RPO (Recovery Point Objective): How much data you’re willing to lose

Common Backup and Restore Errors

1. Never Testing Restores

The most common and most dangerous mistake. A backup that can’t be restored is useless. Test restores monthly at minimum.

2. Insufficient Retention for Compliance

GDPR, HIPAA, PCI-DSS, and SOC 2 all have specific retention requirements. 30-90 days is common. Check your compliance obligations.

3. Not Using Transactionally Consistent Backups

# BAD: No --single-transaction — inconsistent backup if writes happen during dump
mysqldump -u root mydb > mydb.sql

# GOOD: Single transaction ensures point-in-time consistency
mysqldump -u root --single-transaction mydb > mydb.sql

4. Backing Up to the Same Disk as the Database

If the disk fails, you lose both the database and the backup. Always store backups on a different volume, server, or cloud provider.

5. Ignoring WAL Archive Failures

-- Check if WAL archiving is working
SELECT * FROM pg_stat_archiver;
-- archived_count should be increasing
-- last_failed_wal should be empty

6. Not Encrypting Backups

# Encrypt backup with GPG
pg_dump mydb | gpg --encrypt --recipient backup-team > mydb.sql.gpg

# Or use pg_dump's built-in encryption (PostgreSQL 15+)
pg_dump --encrypt=aes-256-gcm --password-file mydb > mydb.dump

7. Relying Only on Replication

Replication is not backup. A DROP TABLE replicates instantly to all replicas. Always maintain independent backups.

Practice Questions

1. What’s the difference between logical and physical backups?

Logical backups export data as SQL/CSV (portable, slower, selective restore). Physical backups copy database files (faster, version-specific, supports PITR).

2. What is point-in-time recovery and how does it work?

PITR restores a database to any specific moment using a base backup plus continuous WAL archives. It applies archived WAL segments up to the target time.

3. Why is the 3-2-1 rule important?

3 copies, 2 media types, 1 off-site. It protects against hardware failure (different drive), ransomware (off-site copy), and regional disasters (different location).

4. What’s the difference between RTO and RPO?

RTO = how long to restore (downtime tolerance). RPO = how much data loss is acceptable. A trading system might have RTO=1min, RPO=0. A blog might have RTO=4hrs, RPO=1hr.

5. Challenge: Design a backup strategy for a financial application.

Requirement: Max 5 minutes of data loss (RPO=5min), restore within 1 hour (RTO=1hr), retain data for 7 years for compliance. Answer: Hourly incremental backups + continuous WAL archiving (for RPO=5min), daily full backups kept for 30 days, monthly full backups kept for 7 years. Store on S3 with cross-region replication. Automate monthly DR drills.

FAQ

Try It Yourself

Set up a complete backup and restore test:

1. Create a test database with sample data
2. Take a full backup with pg_dump -F c
3. Make changes to the data
4. Drop the database (simulate disaster)
5. Restore from the backup
6. Verify all data is intact
7. Write a verification script that automates steps 2-6

What’s Next

You now have a complete backup and restore strategy: logical and physical backups, incremental schedules, PITR with WAL archiving, automated verification, and monthly DR drills. The most important step is to test a restore today — before you need it.