--- name: database-backup-restore description: Implement backup and restore strategies for disaster recovery. Use when creating backup plans, testing restore procedures, or setting up automated backups. --- # 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:** ```bash # 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:** ```bash # 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:** ```bash # 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:** ```sql -- 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:** ```bash #!/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:** ```bash # 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:** ```bash # 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:** ```sql -- 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:** ```bash # 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:** ```bash # 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:** ```bash # Restore from custom format pg_restore -h localhost -U postgres -d database_name \ -v backup.dump # Parallel restore (faster) pg_restore -h localhost -U postgres -d database_name \ -j 4 -v backup.dump # Dry run (test restore without committing) pg_restore --list backup.dump > restore_plan.txt ``` **Point-in-Time Recovery (PITR):** ```bash # List available backups and WAL archives ls -lh /archive/ # Restore to specific point in time pg_basebackup -h localhost -D ./recovery_data \ -U replication_user -c fast # Create recovery.conf cat > ./recovery_data/recovery.conf << EOF recovery_target_timeline = 'latest' recovery_target_xid = '1000000' recovery_target_time = '2024-01-15 14:30:00' recovery_target_name = 'before_bad_update' EOF # Start PostgreSQL with recovery pg_ctl -D ./recovery_data start ``` ### MySQL Restore **Restore from SQL Backup:** ```bash # Restore full database mysql -h localhost -u root -p < backup.sql # Restore specific database mysql -h localhost -u root -p database_name < database_backup.sql # Restore with progress pv backup.sql | mysql -h localhost -u root -p database_name ``` **Restore with Binary Logs:** ```bash # Restore from backup then apply binary logs mysql -h localhost -u root -p < backup.sql # Get starting binary log position from backup grep "SET @@GLOBAL.GTID_PURGED=" backup.sql # Apply binary logs after backup mysqlbinlog /var/log/mysql/mysql-bin.000005 \ --start-position=12345 | \ mysql -h localhost -u root -p database_name ``` **Point-in-Time Recovery:** ```bash # Restore base backup mysql -h localhost -u root -p database_name < base_backup.sql # Apply binary logs up to specific time mysqlbinlog /var/log/mysql/mysql-bin.000005 \ --stop-datetime='2024-01-15 14:30:00' | \ mysql -h localhost -u root -p database_name ``` ## Backup Validation **PostgreSQL - Backup Integrity Check:** ```bash # Verify backup file pg_dump --analyze --schema-only database_name > /dev/null && echo "Backup OK" # Test restore procedure createdb test_restore pg_restore -d test_restore backup.dump psql -d test_restore -c "SELECT COUNT(*) FROM information_schema.tables;" dropdb test_restore ``` **MySQL - Backup Integrity:** ```bash # Check backup file syntax mysql -h localhost -u root -p < backup.sql --dry-run # Verify checksum md5sum backup.sql # Save checksum: echo "abc123def456 backup.sql" > backup.sql.md5 md5sum -c backup.sql.md5 ``` ## Automated Backup Schedule **PostgreSQL - Cron Backup:** ```bash #!/bin/bash # backup.sh - Daily backup script BACKUP_DIR="/backups/postgresql" RETENTION_DAYS=30 TIMESTAMP=$(date +%Y%m%d_%H%M%S) # Create backup pg_dump -h localhost -U postgres mydb | gzip > \ $BACKUP_DIR/backup_$TIMESTAMP.sql.gz # Delete old backups find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete # Upload to S3 aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.sql.gz \ s3://backup-bucket/postgresql/ # Log backup echo "$TIMESTAMP: Backup completed" >> /var/log/db_backup.log ``` **Crontab Entry:** ```bash # Daily backup at 2 AM 0 2 * * * /scripts/backup.sh # Hourly backup 0 * * * * /scripts/hourly_backup.sh # Weekly full backup 0 3 0 * * /scripts/weekly_backup.sh ``` ## Backup Retention Policy **PostgreSQL - Retention Strategy:** ```sql -- Create retention tracking CREATE TABLE backup_retention_policy ( backup_id UUID PRIMARY KEY, database_name VARCHAR(255), backup_date TIMESTAMP, backup_type VARCHAR(20), -- 'full', 'incremental', 'wal' retention_days INT, expires_at TIMESTAMP GENERATED ALWAYS AS (backup_date + INTERVAL '1 day' * retention_days) STORED ); -- Example retention periods INSERT INTO backup_retention_policy VALUES ('backup-001', 'production', NOW(), 'full', 30), ('backup-002', 'production', NOW(), 'incremental', 7), ('backup-003', 'staging', NOW(), 'full', 7); -- Query expiring backups SELECT backup_id, expires_at FROM backup_retention_policy WHERE expires_at < NOW(); ``` ## RTO/RPO Planning ``` Recovery Time Objective (RTO): How quickly must the system recover Recovery Point Objective (RPO): How much data loss is acceptable Example: - RTO: 1 hour (system must be recovered within 1 hour) - RPO: 15 minutes (no more than 15 minutes of data loss acceptable) Backup frequency: Every 15 minutes (to meet RPO) Replication lag: < 5 minutes (for RTO) ``` ## Best Practices Checklist ✅ DO test restore procedures regularly ✅ DO implement automated backups ✅ DO monitor backup success ✅ DO encrypt backup files ✅ DO store backups offsite ✅ DO document recovery procedures ✅ DO track backup retention policies ✅ DO monitor backup performance ❌ DON'T rely on untested backups ❌ DON'T skip backup verification ❌ DON'T store backups on same server ❌ DON'T use weak encryption ❌ DON'T forget backup retention limits ## Resources - [PostgreSQL Backup & Restore](https://www.postgresql.org/docs/current/backup.html) - [MySQL Backup & Recovery](https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html) - [Percona Backup for MongoDB](https://www.percona.com/mongodb-backup) - [AWS RDS Automated Backups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html)