#!/bin/bash
# verify-backup.sh
BACKUP_FILE=$1
RESTORE_DB="verify_$(date +%s)"
 
# Restore backup
pg_restore -d $RESTORE_DB $BACKUP_FILE
 
# Verify row counts
SOURCE_COUNT=$(psql -t -c "SELECT count(*) FROM orders" production)
RESTORE_COUNT=$(psql -t -c "SELECT count(*) FROM orders" $RESTORE_DB)
 
if [ "$SOURCE_COUNT" != "$RESTORE_COUNT" ]; then
  echo "ALERT: Row count mismatch! Source: $SOURCE_COUNT, Restored: $RESTORE_COUNT"
  exit 1
fi
 
# Verify checksums
SOURCE_CHECKSUM=$(psql -t -c "SELECT md5(string_agg(row(t)::text, '')) FROM orders t" production)
RESTORE_CHECKSUM=$(psql -t -c "SELECT md5(string_agg(row(t)::text, '')) FROM orders t" $RESTORE_DB)
 
if [ "$SOURCE_CHECKSUM" != "$RESTORE_CHECKSUM" ]; then
  echo "ALERT: Data checksum mismatch!"
  exit 1
fi
 
# Cleanup
dropdb $RESTORE_DB
echo "Backup verification passed"

-- postgresql.conf
wal_level = replica                    -- Enable WAL archiving
archive_mode = on                      -- Turn on archiving
archive_command = 'aws s3 cp %p s3://my-backup-bucket/wal/%f'
max_wal_senders = 3                    -- Allow streaming replication
wal_keep_size = '1GB'                  -- Retain WAL for replication
archive_timeout = 300                  -- Force archive every 5 minutes

# Create a compressed base backup
pg_basebackup -h localhost -D /backup/base -Ft -z -P --checkpoint=fast
 
# The --checkpoint=fast flag forces an immediate checkpoint,
# reducing the time the backup takes
 
# Upload to S3 with server-side encryption
aws s3 cp /backup/base/base.tar.gz \
  s3://my-backup-bucket/base/$(date +%Y%m%d)/base.tar.gz \
  --sse aws:kms --sse-key-id alias/backup-key
 
# Verify the backup was created successfully
pg_verifybackup /backup/base 2>/dev/null && echo "Backup verified" || echo "Backup verification failed"

pg_basebackup -h primary.db.internal -D /backup/standby \
  -Ft -z -P -R --checkpoint=fast

# Step 1: Stop PostgreSQL
systemctl stop postgresql
 
# Step 2: Remove current data directory (DANGEROUS - ensure backups exist!)
rm -rf /var/lib/postgresql/16/main/*
 
# Step 3: Restore base backup
tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/16/main/
 
# Step 4: Create recovery configuration
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'aws s3 cp s3://my-backup-bucket/wal/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
recovery_target_action = 'promote'
recovery_target_timeline = 'latest'
EOF
 
# Step 5: Create recovery signal file
touch /var/lib/postgresql/16/main/recovery.signal
 
# Step 6: Set correct ownership
chown -R postgres:postgres /var/lib/postgresql/16/main/
 
# Step 7: Start PostgreSQL (will enter recovery mode)
systemctl start postgresql
 
# Step 8: Monitor recovery progress
tail -f /var/log/postgresql/postgresql-16-main.log

# my.cnf - Enable binary logging
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW           # ROW format captures actual row changes
binlog_row_image = FULL       # Log complete before/after images
expire_logs_days = 7          # Auto-purge old binlogs
max_binlog_size = 100M        # Rotate binlog at 100MB
sync_binlog = 1               # Sync binlog to disk on every commit

# Full backup with mysqldump (consistent snapshot)
mysqldump --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=ON \
  --result-file=/backup/full_$(date +%Y%m%d).sql
 
# Compress the backup
gzip /backup/full_$(date +%Y%m%d).sql

# Full backup with XtraBackup
xtrabackup --backup \
  --target-dir=/backup/full \
  --user=backup_user \
  --password=secret
 
# Prepare the backup (apply logs for consistency)
xtrabackup --prepare --target-dir=/backup/full
 
# Incremental backup (only changed pages since last backup)
xtrabackup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=backup_user

# Step 1: Restore full backup
mysql < /backup/full_20240115.sql
 
# Step 2: Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2024-01-15 14:30:00" \
  --database=myapp \
  /var/log/mysql/mysql-bin.000001 \
  /var/log/mysql/mysql-bin.000002 | mysql
 
# Step 3: Skip the problematic transaction (if known)
mysqlbinlog --start-position=1234 --stop-position=5678 \
  /var/log/mysql/mysql-bin.000002 | mysql

# Full logical backup
mongodump --uri="mongodb://localhost:27017" \
  --out=/backup/full_$(date +%Y%m%d) \
  --gzip \
  --oplog
 
# The --oplog flag captures operations during the dump,
# ensuring a consistent point-in-time snapshot
 
# Restore with oplog replay
mongorestore --uri="mongodb://localhost:27017" \
  --gzip \
  --oplogReplay \
  --oplogLimit="1705312200:1" \
  /backup/full_20240115

# Step 1: Lock writes on the secondary
mongo --eval "db.fsyncLock()"
 
# Step 2: Create LVM snapshot
lvcreate --size 10G --snapshot --name mongo_snap /dev/vg0/mongo_data
 
# Step 3: Unlock writes
mongo --eval "db.fsyncUnlock()"
 
# Step 4: Copy snapshot to backup storage
dd if=/dev/vg0/mongo_snap bs=4M | gzip > /backup/mongo_$(date +%Y%m%d).img.gz
 
# Step 5: Remove snapshot
lvremove -f /dev/vg0/mongo_snap

// Configure backup policy via Atlas API
const backupPolicy = {
  "policies": [{
    "id": "defaultPolicy",
    "policyItems": [{
      "frequencyInterval": 1,
      "frequencyUnit": "hourly",
      "retentionUnit": "days",
      "retentionValue": 7
    }, {
      "frequencyInterval": 1,
      "frequencyUnit": "daily",
      "retentionUnit": "weeks",
      "retentionValue": 4
    }]
  }],
  "referenceHourOfDay": 2,   // 2 AM
  "referenceMinuteOfHour": 0,
  "restoreWindowDays": 2
};

# redis.conf - Enable both RDB and AOF
save 900 1              # Save if at least 1 key changed in 900 seconds
save 300 10             # Save if at least 10 keys changed in 300 seconds
save 60 10000           # Save if at least 10000 keys changed in 60 seconds
 
appendonly yes
appendfilename "appendonly.aof"
appendfsync everysec    # fsync every second (good balance of safety/speed)
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
 
# Trigger manual backup
redis-cli BGSAVE        # Background RDB save
redis-cli BGREWRITEAOF  # Background AOF rewrite

#!/bin/bash
# redis-backup.sh
REDIS_CLI="redis-cli"
BACKUP_DIR="/backup/redis"
S3_BUCKET="s3://my-backup-bucket/redis"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
 
# Trigger RDB snapshot
$REDIS_CLI BGSAVE
sleep 5  # Wait for background save
 
# Copy RDB file
cp /var/lib/redis/dump.rdb "$BACKUP_DIR/dump_${TIMESTAMP}.rdb"
 
# Upload to S3
aws s3 cp "$BACKUP_DIR/dump_${TIMESTAMP}.rdb" "$S3_BUCKET/"
 
# Cleanup old local backups (keep last 5)
ls -t "$BACKUP_DIR"/dump_*.rdb | tail -n +6 | xargs rm -f
 
echo "Redis backup completed: dump_${TIMESTAMP}.rdb"

#!/bin/bash
# backup.sh - Run via cron: 0 */6 * * * /opt/scripts/backup.sh
 
set -euo pipefail
 
DB_NAME="production"
BACKUP_DIR="/backup"
S3_BUCKET="s3://my-backup-bucket"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
SLACK_WEBHOOK="https://hooks.slack.com/services/xxx/yyy/zzz"
 
notify() {
  local message="$1"
  curl -s -X POST -H 'Content-type: application/json' \
    --data "{\"text\": \"$message\"}" "$SLACK_WEBHOOK"
}
 
# Create full backup
echo "Starting backup of $DB_NAME at $TIMESTAMP"
START_TIME=$(date +%s)
 
pg_dump -Fc -Z9 $DB_NAME > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"
 
BACKUP_SIZE=$(du -sh "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" | cut -f1)
echo "Backup size: $BACKUP_SIZE"
 
# Upload to S3 with server-side encryption
aws s3 cp "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" \
  "$S3_BUCKET/daily/${DB_NAME}_${TIMESTAMP}.dump" \
  --sse aws:kms
 
# Verify backup integrity
echo "Verifying backup..."
pg_restore --list "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" > /dev/null 2>&1
VERIFY_RESULT=$?
 
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
 
if [ $VERIFY_RESULT -eq 0 ]; then
  notify "✅ Backup successful: ${DB_NAME}_${TIMESTAMP}.dump ($BACKUP_SIZE, ${DURATION}s)"
else
  notify "❌ ALERT: Backup verification FAILED for ${DB_NAME}_${TIMESTAMP}.dump!"
  exit 1
fi
 
# Cleanup old local backups
find $BACKUP_DIR -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
 
# Cleanup old S3 backups (using lifecycle policy is preferred)
# This is handled by S3 Lifecycle Configuration on the bucket
 
echo "Backup completed: ${DB_NAME}_${TIMESTAMP}.dump"

import boto3
 
rds = boto3.client('rds')
 
# Create a manual snapshot
rds.create_db_snapshot(
    DBInstanceIdentifier='production-db',
    DBSnapshotIdentifier=f'production-manual-{datetime.now().strftime("%Y%m%d")}',
    Tags=[
        {'Key': 'Purpose', 'Value': 'Manual backup before migration'},
        {'Key': 'Retention', 'Value': '90 days'}
    ]
)
 
# Restore to a specific point in time
rds.restore_db_instance_to_point_in_time(
    SourceDBInstanceIdentifier='production-db',
    TargetDBInstanceIdentifier='production-db-restored',
    RestoreTime=datetime(2024, 1, 15, 14, 30, 0),
    UseLatestRestorableTime=False,
    DBSubnetGroupName='production-subnet-group',
    MultiAZ=False,
    PubliclyAccessible=False
)
 
# Copy snapshot to another region for disaster recovery
rds.copy_db_snapshot(
    SourceDBSnapshotIdentifier='arn:aws:rds:us-east-1:123456789:snapshot:production-manual-20240115',
    TargetDBSnapshotIdentifier='production-dr-20240115',
    SourceRegion='us-east-1',
    KmsKeyId='alias/rds-backup-key'
)

# Create an on-demand backup
gcloud sql backups create \
  --instance=production-db \
  --description="Pre-migration backup"
 
# List available backups
gcloud sql backups list --instance=production-db
 
# Restore from a specific backup
gcloud sql backups restore BACKUP_ID \
  --restore-instance=production-db-restored \
  --backup-instance=production-db
 
# Configure automated backups
gcloud sql instances patch production-db \
  --backup-start-time=02:00 \
  --enable-bin-log \
  --retained-backups-count=30

# AWS RDS with automated backups
resource "aws_db_instance" "production" {
  identifier           = "production-db"
  engine               = "postgres"
  engine_version       = "16.1"
  instance_class       = "db.r6g.xlarge"
  allocated_storage    = 100
  
  backup_retention_period = 30        # Keep backups for 30 days
  backup_window          = "03:00-04:00"  # UTC
  maintenance_window     = "Mon:04:00-Mon:05:00"
  
  # Enable encryption
  storage_encrypted = true
  kms_key_id       = aws_kms_key.rds.arn
  
  # Cross-region read replica for DR
  replicate_source_db = null
  
  tags = {
    Environment = "production"
    BackupPolicy = "30-day-retention"
  }
}
 
# S3 bucket for backup storage with lifecycle
resource "aws_s3_bucket" "backups" {
  bucket = "my-database-backups"
}
 
resource "aws_s3_bucket_lifecycle_configuration" "backup_lifecycle" {
  bucket = aws_s3_bucket.backups.id
 
  rule {
    id     = "transition-to-glacier"
    status = "Enabled"
 
    transition {
      days          = 30
      storage_class = "GLACIER"
    }
 
    transition {
      days          = 90
      storage_class = "DEEP_ARCHIVE"
    }
 
    expiration {
      days = 365
    }
  }
}

# Database Disaster Recovery Runbook
 
## Scenario: Primary Region Failure
 
### Step 1: Assess the Situation (5 minutes)
- [ ] Confirm primary region is unreachable
- [ ] Check AWS/GCP status page for regional issues
- [ ] Notify incident commander and stakeholders
 
### Step 2: Initiate Failover (10 minutes)
- [ ] Verify DR replica is current (check replication lag)
- [ ] Promote DR replica to primary
- [ ] Update DNS to point to DR region
- [ ] Update application connection strings
 
### Step 3: Validate (15 minutes)
- [ ] Verify application connectivity
- [ ] Run smoke tests
- [ ] Check data integrity
- [ ] Monitor error rates
 
### Step 4: Communicate
- [ ] Update status page
- [ ] Notify customers of any data gaps
- [ ] Document timeline for post-mortem

# PostgreSQL with encrypted backups using pg_dump + GPG
pg_dump -Fc production | \
  gpg --batch --yes --passphrase-file /secure/passphrase.txt \
  --symmetric --cipher-algo AES256 | \
  aws s3 cp - s3://backups/encrypted/production_$(date +%Y%m%d).dump.gpg \
  --sse aws:kms --sse-key-id alias/backup-key
 
# Restore encrypted backup
aws s3 cp s3://backups/encrypted/production_20240115.dump.gpg - | \
  gpg --batch --yes --passphrase-file /secure/passphrase.txt --decrypt | \
  pg_restore -d production_restored

import boto3
from cryptography.fernet import Fernet
 
# Generate a data encryption key (DEK)
kms = boto3.client('kms')
response = kms.generate_data_key(
    KeyId='alias/backup-key',
    KeySpec='AES_256'
)
plaintext_key = response['Plaintext']
encrypted_key = response['CiphertextBlob']
 
# Use DEK to encrypt backup
fernet = Fernet(base64.urlsafe_b64encode(plaintext_key[:32]))
encrypted_backup = fernet.encrypt(backup_data)
 
# Store encrypted DEK alongside backup
# Later: decrypt DEK with KMS, then decrypt backup with DEK

{
  "Rules": [
    {
      "ID": "BackupRetention",
      "Status": "Enabled",
      "Filter": {"Prefix": "daily/"},
      "Transitions": [
        {
          "Days": 7,
          "StorageClass": "STANDARD_IA"
        },
        {
          "Days": 30,
          "StorageClass": "GLACIER"
        },
        {
          "Days": 365,
          "StorageClass": "DEEP_ARCHIVE"
        }
      ],
      "Expiration": {
        "Days": 2555
      }
    },
    {
      "ID": "WeeklyRetention",
      "Status": "Enabled",
      "Filter": {"Prefix": "weekly/"},
      "Transitions": [
        {
          "Days": 30,
          "StorageClass": "GLACIER"
        }
      ],
      "Expiration": {
        "Days": 3650
      }
    }
  ]
}

# PostgreSQL parallel restore (4 worker processes)
pg_restore -j 4 -d production /backup/latest.dump
 
# Parallel backup compression with pigz
pg_dump -Fc production | pigz -p 8 > /backup/production.dump.gz
 
# MySQL parallel backup with mydumper
mydumper --host localhost --user backup --password secret \
  --database production --outputdir /backup/parallel \
  --threads 8 --compress --build-empty-files

# Stream PostgreSQL backup directly to S3 (no local storage needed)
pg_dump -Fc production | \
  aws s3 cp - s3://backups/production_$(date +%Y%m%d).dump \
  --expected-size $(pg_dump -Fc --schema-only production | wc -c)
 
# Stream with compression
pg_dump -Fc production | zstd -3 | \
  aws s3 cp - s3://backups/production_$(date +%Y%m%d).dump.zst

#!/bin/bash
# test-backup.sh - Monthly automated backup test
 
set -euo pipefail
 
TEST_CONTAINER="test-restore-$(date +%s)"
BACKUP_FILE="$1"
 
# Spin up test database
docker run -d --name "$TEST_CONTAINER" \
  -e POSTGRES_PASSWORD=test \
  -e POSTGRES_DB=verify \
  -p 15432:5432 \
  postgres:16
 
# Wait for database to be ready
echo "Waiting for database to start..."
for i in $(seq 1 30); do
  if docker exec "$TEST_CONTAINER" pg_isready -U postgres > /dev/null 2>&1; then
    break
  fi
  sleep 1
done
 
# Restore backup
echo "Restoring backup..."
pg_restore -h localhost -p 15432 -U postgres -d verify "$BACKUP_FILE" 2>/dev/null
 
# Run verification queries
TABLES=$(psql -h localhost -p 15432 -U postgres -t -c \
  "SELECT count(*) FROM information_schema.tables WHERE table_schema='public'" verify)
 
ROWS=$(psql -h localhost -p 15432 -U postgres -t -c \
  "SELECT sum(n_live_tup) FROM pg_stat_user_tables" verify)
 
echo "Restored $TABLES tables with $ROWS total rows"
 
if [ "$TABLES" -gt 0 ] && [ "$ROWS" -gt 0 ]; then
  echo "PASS: Backup restoration successful"
  RESULT="PASS"
else
  echo "FAIL: Backup restoration failed"
  RESULT="FAIL"
fi
 
# Cleanup
docker stop "$TEST_CONTAINER" > /dev/null 2>&1
docker rm "$TEST_CONTAINER" > /dev/null 2>&1
 
# Log result
echo "$(date -Iseconds) | $RESULT | $BACKUP_FILE | Tables: $TABLES | Rows: $ROWS" >> /var/log/backup-tests.log
 
[ "$RESULT" = "PASS" ] || exit 1