Database Backup and Recovery: Disaster Recovery Guide

In this tutorial, you'll learn about Database Backup and Recovery: Disaster Recovery Guide. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database backup and recovery is the practice of creating and storing copies of database data to enable restoration after data loss, corruption, or disaster -- governed by Recovery Point Objective and Recovery Time Objective targets.

What You'll Learn

You will understand logical and physical backup strategies, point-in-time recovery with WAL archiving, backup validation, RPO/RTO planning, automated backup scripts, and disaster recovery testing.

Why Backup and Recovery Matters

Data loss is not a question of if but when. Doda Browser stores user bookmarks, history, and preferences. Without proper backups, a storage failure would lose years of user data. 60% of companies that lose data shut down within 6 months.

Backup and Recovery Learning Path

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

Backup Types

PostgreSQL Backup Strategies

Logical Backup with pg_dump

# Full database dump
pg_dump -h localhost -U app_user -d mydb \
  --format=custom \
  --compress=9 \
  --file=/backups/mydb_$(date +%Y%m%d_%H%M%S).dump

# Schema-only (no data)
pg_dump -h localhost -U app_user -d mydb \
  --schema-only \
  --file=/backups/mydb_schema.sql

# Data-only for specific tables
pg_dump -h localhost -U app_user -d mydb \
  --data-only \
  --table=orders \
  --table=order_items \
  --file=/backups/mydb_orders_data.dump

# Restore from custom format
pg_restore -h localhost -U app_user -d mydb \
  --clean \
  --if-exists \
  --jobs=4 \
  /backups/mydb_20260622_120000.dump

Point-in-Time Recovery with WAL Archiving

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backups/wal/%f'
archive_timeout = 60  # Force WAL segment every 60 seconds

#!/bin/bash
# Full base backup script
pg_basebackup -h localhost -U replicator \
  --pgdata=/backups/base/$(date +%Y%m%d) \
  --wal-method=stream \
  --progress \
  --verbose

# Retention: keep last 7 daily backups
find /backups/base -type d -mtime +7 -exec rm -rf {} \;

Point-in-Time Recovery Steps

# 1. Stop the database
systemctl stop postgresql

# 2. Restore base backup
rm -rf /var/lib/postgresql/16/main/*
cp -r /backups/base/20260622/* /var/lib/postgresql/16/main/

# 3. Create recovery.conf or recovery.signal
touch /var/lib/postgresql/16/main/recovery.signal

# 4. Configure recovery target (optional)
echo "restore_command = 'cp /backups/wal/%f %p'" >> /etc/postgresql/16/main/postgresql.conf
echo "recovery_target_time = '2026-06-22 14:30:00 UTC'" >> /etc/postgresql/16/main/postgresql.conf

# 5. Start PostgreSQL (it recovers automatically)
systemctl start postgresql

# 6. Verify recovery completed
psql -c "SELECT pg_is_in_recovery();"  # Should return 'f' when done

MySQL Backup Strategies

Logical Backup with mysqldump

# Full database dump
mysqldump -h localhost -u app_user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --databases mydb \
  --result-file=/backups/mydb_$(date +%Y%m%d).sql

# Compress the dump
gzip /backups/mydb_20260622.sql

# Restore
mysql -h localhost -u app_user -p mydb < /backups/mydb_20260622.sql

# Or from compressed file
gunzip < /backups/mydb_20260622.sql.gz | mysql -h localhost -u app_user -p mydb

MySQL Physical Backup with Percona XtraBackup

# Full backup
xtrabackup --backup \
  --user=backup_user \
  --password=secret \
  --target-dir=/backups/xtra/$(date +%Y%m%d)

# Prepare (apply logs)
xtrabackup --prepare \
  --target-dir=/backups/xtra/20260622

# Restore
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back \
  --target-dir=/backups/xtra/20260622
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Backup Validation

A backup that cannot be restored is worthless. Validate every backup.

#!/bin/bash
# Backup validation script
BACKUP_FILE=$1
TEST_DB="restore_test_$(date +%s)"

# Create temporary database
createdb $TEST_DB

# Restore into test database
pg_restore -d $TEST_DB $BACKUP_FILE 2>&1

if [ $? -eq 0 ]; then
    echo "VALID: Backup $BACKUP_FILE restored successfully"
    # Check row counts match known values
    psql -d $TEST_DB -c "SELECT count(*) FROM orders;"
else
    echo "INVALID: Backup $BACKUP_FILE failed to restore"
    exit 1
fi

# Clean up
dropdb $TEST_DB

Validation schedule:

RPO and RTO Planning

Recovery Point Objective (RPO): Maximum acceptable data loss in time. Recovery Time Objective (RTO): Maximum acceptable downtime.

Disaster Recovery Plan Template

# disaster-recovery-plan.yaml
service_name: mydb_production
contact: db-team@example.com

backup:
  type: physical + WAL
  schedule: daily full at 0200 UTC
  retention: 30 days
  location: s3://dodatech-backups/mydb/

rpo: 5 minutes
rto: 30 minutes

recovery_steps:
  - step: 1
    action: "Identify failure type"
    commands: ["check pg_isready", "check disk space", "check system logs"]
  - step: 2
    action: "Restore latest base backup"
    commands: ["pg_basebackup restore", "apply WAL"]
  - step: 3
    action: "Verify data integrity"
    commands: ["psql -c 'SELECT count(*) FROM orders'"]
  - step: 4
    action: "Redirect traffic"
    commands: ["update DNS", "notify users"]

testing:
  frequency: quarterly
  last_test: 2026-03-15
  next_test: 2026-09-15

Common Backup and Recovery Errors

1. Never Testing Restores

A backup that has never been restored is a hope, not a plan. Test restores monthly at minimum.

2. Keeping Backups on the Same Server

If the server dies, backups on the same disk die with it. Always store backups off-site (S3, separate server, different region).

3. Not Monitoring Backup Success

Set up alerts that fire if a backup fails. Many teams discover backups have been failing for weeks only when they need to restore.

4. Ignoring WAL Archiving for PITR

Without WAL archiving, you can only restore to the last full backup. Enable archive_mode and archive_command in PostgreSQL.

5. Forgetting to Backup Non-Database Data

Configuration files (PostgreSQL.conf, pg_hba.conf), functions, stored procedures, and cron job definitions are not backed up by pg_dump.

6. Wrong Backup Permissions

Backup files must be readable by the restore Process but not by unauthorized users. Use 600 permissions and encrypt off-site backups.

7. No Retention Policy

Without a retention policy, storage fills up and old backups cannot be restored because the associated WAL segments were deleted.

Practice Questions

1. What is the difference between logical and physical backup?

Logical backup (pg_dump, mysqldump) exports SQL statements. Physical backup copies database files directly. Physical backup is faster and supports PITR.

2. How does point-in-time recovery work in PostgreSQL?

WAL archiving records every change. During recovery, PostgreSQL replays WAL segments from the base backup up to the target time or Transaction ID.

3. What is RPO and how do you achieve 5-minute RPO?

RPO is the maximum acceptable data loss in time. Achieve 5-minute RPO by taking hourly base backups with continuous WAL archiving, or use streaming Replication.

4. How do you validate a backup?

Restore the backup to a test database and run integrity checks: row counts match, foreign keys are valid, application smoke tests pass.

5. Challenge: Design a backup Strategy for an e-commerce platform.

Requirements: 1-minute RPO, 15-minute RTO, 200GB database, 100M rows in orders table. Answer: Use PostgreSQL with synchronous streaming Replication to a standby, plus hourly pg_basebackup with continuous WAL archiving to S3. For restore: provision a new server, restore base backup, apply WAL up to failure time, promote to primary, redirect traffic. Test the full procedure quarterly.

FAQ

Try It Yourself

Set up a backup workflow:

1. Configure PostgreSQL WAL archiving to a local directory
2. Take a base backup with pg_basebackup
3. Insert test data and note the timestamp
4. Simulate data loss with DROP TABLE
5. Restore from base backup + WAL replay to a point before the DROP
6. Verify the restored data matches expectations

What's Next

You have learned backup types, PITR, validation, and RPO/RTO planning. Start by running a restore test on your most recent backup -- if you have never tested it, today is the day.