Database Backup & Restore

Overview

Implement comprehensive backup and disaster recovery strategies. Covers backup types, retention policies, restore testing, and recovery time objectives (RTO/RPO).

When to Use

• Backup automation setup
• Disaster recovery planning
• Recovery testing procedures
• Backup retention policies
• Point-in-time recovery (PITR)
• Cross-region backup replication
• Compliance and audit requirements

PostgreSQL Backup Strategies

Full Database Backup

pg_dump - Text Format:

# Simple full backup
pg_dump -h localhost -U postgres -F p database_name > backup.sql

# With compression
pg_dump -h localhost -U postgres -F p database_name | gzip > backup.sql.gz

# Backup with verbose output
pg_dump -h localhost -U postgres -F p -v database_name > backup.sql 2>&1

# Exclude specific tables
pg_dump -h localhost -U postgres database_name \
  --exclude-table=temp_* --exclude-table=logs > backup.sql

pg_dump - Custom Binary Format:

# Custom binary format (better for large databases)
pg_dump -h localhost -U postgres -F c database_name > backup.dump

# Parallel jobs for faster backup (PostgreSQL 9.3+)
pg_dump -h localhost -U postgres -F c -j 4 \
  --load-via-partition-root database_name > backup.dump

# Backup specific schema
pg_dump -h localhost -U postgres -n public database_name > backup.dump

# Get backup info
pg_dump_all -h localhost -U postgres > all_databases.sql

pg_basebackup - Physical Backup:

# Take base backup for streaming replication
pg_basebackup -h localhost -D ./backup_data -U replication_user -v -P

# Label backup for archival
pg_basebackup -h localhost -D ./backup_data \
  -U replication_user -l "backup_$(date +%Y%m%d)" -v -P

# Tar format with compression
pg_basebackup -h localhost -D - -U replication_user \
  -Ft -z -l "backup_$(date +%s)" | tar -xz -C ./backups/

Incremental & Differential Backups

WAL Archiving Setup:

-- postgresql.conf configuration
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
-- archive_timeout = 300

-- Monitor WAL archiving
SELECT
  name,
  setting
FROM pg_settings
WHERE name LIKE 'archive%';

-- Check WAL directory
-- ls -lh $PGDATA/pg_wal/

-- List archived WALs
-- ls -lh /archive/

Continuous WAL Backup:

#!/bin/bash
# Backup script with WAL archiving

BACKUP_DIR="/backups"
DB_NAME="production"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Create base backup
pg_basebackup -h localhost -D $BACKUP_DIR/base_$TIMESTAMP \
  -U backup_user -v

# Archive WAL files
WAL_DIR=$BACKUP_DIR/wal_$TIMESTAMP
mkdir -p $WAL_DIR
cp /var/lib/postgresql/14/main/pg_wal/* $WAL_DIR/

# Compress backup
tar -czf $BACKUP_DIR/backup_$TIMESTAMP.tar.gz \
  $BACKUP_DIR/base_$TIMESTAMP $BACKUP_DIR/wal_$TIMESTAMP

# Verify backup
pg_basebackup -h localhost -U backup_user --analyze

# Upload to S3
aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.tar.gz \
  s3://backup-bucket/postgres/

MySQL Backup Strategies

Full Database Backup

mysqldump - Text Format:

# Simple full backup
mysqldump -h localhost -u root -p database_name > backup.sql

# All databases
mysqldump -h localhost -u root -p --all-databases > all_databases.sql

# With flush privileges and triggers
mysqldump -h localhost -u root -p \
  --flush-privileges --triggers --routines \
  database_name > backup.sql

# Parallel backup (MySQL 5.7.11+)
mydumper -h localhost -u root -p password \
  -o ./backup_dir --threads 4 --compress

Backup Specific Tables:

# Backup specific tables
mysqldump -h localhost -u root -p database_name table1 table2 > tables.sql

# Exclude tables
mysqldump -h localhost -u root -p database_name \
  --ignore-table=database_name.temp_table \
  --ignore-table=database_name.logs > backup.sql

Binary Log Backups

Enable Binary Logging:

-- Check binary logging status
SHOW VARIABLES LIKE 'log_bin%';

-- Configure in my.cnf
-- [mysqld]
-- log-bin = mysql-bin
-- binlog_format = ROW

-- View binary logs
SHOW BINARY LOGS;

-- Get current position
SHOW MASTER STATUS;

Binary Log Backup:

# Backup binary logs
MYSQL_PWD="password" mysqldump -h localhost -u root \
  --single-transaction --flush-logs --all-databases > backup.sql

# Copy binary logs
cp /var/log/mysql/mysql-bin.* /backup/binlogs/

# Backup incremental changes
mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_backup.sql

Restore Procedures

PostgreSQL Restore

Restore from Text Backup:

# Drop and recreate database
psql -h localhost -U postgres -c "DROP DATABASE IF EXISTS database_name;"
psql -h localhost -U postgres -c "CREATE DATABASE database_name;"

# Restore from text backup
psql -h localhost -U postgres database_name < backup.sql

# Restore with verbose output
psql -h localhost -U postgres -1 database_name < backup.sql 2>&1 | tee restore.log

Restore from Binary Backup:

# Restore from custom form

...