# Automated backup test script example #!/bin/bash BACKUP_FILE=$1 TEST_DB="backup_test_$(date +%s)" echo "Testing backup: $BACKUP_FILE" # 1. Create test database psql -U postgres -c "CREATE DATABASE $TEST_DB;" # 2. Restore pg_restore -U postgres -d $TEST_DB $BACKUP_FILE RESTORE_STATUS=$? # 3. Run checks if [ $RESTORE_STATUS -eq 0 ]; then # Check row counts match expected USER_COUNT=$(psql -U postgres -d $TEST_DB -t -c "SELECT COUNT(*) FROM users;") echo "Users: $USER_COUNT" # Check for corruption psql -U postgres -d $TEST_DB -c "SELECT pg_catalog.pg_relation_check_pages('users');" # Run test queries psql -U postgres -d $TEST_DB -f /tests/smoke_tests.sql echo "✓ Backup test PASSED" else echo "✗ Backup test FAILED" fi # 4. Cleanup psql -U postgres -c "DROP DATABASE $TEST_DB;"

13-backup-recovery

13 - Backup & Recovery

Why Backups Matter

13-backup-recovery

13 - Backup & Recovery

Why Backups Matter

Types of Backups

1. Physical (Binary) Backups

2. Logical (SQL) Backups

3. Continuous Archiving (WAL Archiving)

Backup Commands

PostgreSQL

MySQL

Restore Procedures

PostgreSQL Restore

MySQL Restore

Backup Strategies

3-2-1 Rule

Backup Schedule Example

Cloud Backup Solutions

AWS

PostgreSQL + S3 with wal-g

Testing Backups

Special Recovery Scenarios

Recovering from DROP TABLE

Recovering from Bad UPDATE

Next: Monitoring & Observability