Eve library

Database Disaster Recovery

Runbook

Revision 1

On this page

Purpose: Detailed procedures for database failures, corruption, and complete data center loss scenarios

Database Architecture Overview#

Production Database Setup#

text
Primary Cluster:
├── postgres-primary-0 (Read/Write leader)
├── postgres-replica-1 (Read replica)
├── postgres-replica-2 (Read replica)
└── postgres-backup (Dedicated backup instance)

Backup Strategy:
├── Continuous WAL shipping to S3
├── Daily full backups to multiple regions
├── Point-in-time recovery capability
└── Cross-region backup replication

Database Dependencies#

  • Storage: Persistent volumes with replicated storage
  • Network: Service mesh connectivity and DNS
  • Secrets: Database credentials in Vault
  • Monitoring: Prometheus metrics and alerting
  • Backup Storage: S3-compatible object storage

Database Failure Scenarios#

Scenario 1: Primary Database Pod Failure#

Symptoms#

text
- Primary database pod in CrashLoopBackOff state
- Database connections failing with "connection refused"
- Replica promotion not occurring automatically
- Application services showing database connection errors

Quick Assessment#

bash
# Check pod status
kubectl get pods -n database -l role=primary

# Check pod logs
kubectl logs postgres-primary-0 -n database --tail=50

# Check storage status
kubectl get pv,pvc -n database

# Check recent events
kubectl get events -n database --sort-by='.lastTimestamp'

Recovery Steps#

bash
# 1. Check if automatic failover occurred
kubectl get pods -n database -l role=replica
kubectl logs postgres-replica-1 -n database | grep -i "promotion\|election"

# 2. If no automatic failover, manually promote replica
kubectl exec -it postgres-replica-1 -n database -- pg_promote

# 3. Update service endpoints to point to new primary
kubectl patch service postgres-primary -n database \
  --patch='{"spec":{"selector":{"role":"replica","instance":"1"}}}'

# 4. Restart primary pod if storage is healthy
kubectl delete pod postgres-primary-0 -n database

# 5. Monitor recovery
kubectl wait --for=condition=Ready pod postgres-primary-0 -n database --timeout=300s

Scenario 2: Database Corruption Detected#

Symptoms#

text
- PANIC: corrupted page detected
- Data consistency check failures
- Unexpected query results or errors
- Index corruption messages in logs

Immediate Response#

bash
# 1. Stop all write operations immediately
kubectl scale deployment --replicas=0 -n lilith-bff
kubectl scale deployment --replicas=0 -n lilith-conversation

# 2. Isolate corrupted database
kubectl patch service postgres-primary -n database \
  --patch='{"spec":{"selector":{"role":"maintenance"}}}'

# 3. Run corruption assessment
kubectl exec -it postgres-primary-0 -n database -- \
  psql -c "SELECT datname, datcorrupted FROM pg_database;"

# 4. Create snapshot of corrupted state for analysis
kubectl exec -it postgres-primary-0 -n database -- \
  pg_dump --schema-only lilith > /tmp/corrupted-schema.sql

Recovery Process#

bash
# 1. Identify last known good backup
aws s3 ls s3://lilith-db-backups/ | grep -E "$(date -d '1 day ago' +%Y-%m-%d)"

# 2. Create new database instance for restoration
kubectl apply -f - <<EOF
apiVersion: v1
kind: Pod
metadata:
  name: postgres-recovery
  namespace: database
spec:
  containers:
  - name: postgres
    image: postgres:15
    env:
    - name: POSTGRES_PASSWORD
      valueFrom:
        secretKeyRef:
          name: postgres-secret
          key: password
    volumeMounts:
    - name: recovery-data
      mountPath: /var/lib/postgresql/data
  volumes:
  - name: recovery-data
    emptyDir: {}
EOF

# 3. Restore from backup
kubectl exec -it postgres-recovery -n database -- \
  pg_basebackup --host=backup-host --port=5432 \
  --username=postgres --pgdata=/var/lib/postgresql/recovery

# 4. Perform point-in-time recovery if needed
kubectl exec -it postgres-recovery -n database -- \
  pg_wal_replay --target-time="2024-09-15 20:00:00"

Scenario 3: Complete Database Cluster Failure#

Symptoms#

text
- All database pods unresponsive
- Storage system failures
- Network connectivity issues
- Cluster-wide resource exhaustion

Assessment and Triage#

bash
# 1. Check infrastructure health
kubectl get nodes -l node-role=database
kubectl describe nodes | grep -A5 "Conditions"

# 2. Check storage system
kubectl get sc,pv,pvc -n database
kubectl describe pv | grep -i "failure\|error"

# 3. Check network connectivity
kubectl get services,endpoints -n database
kubectl run network-test --image=busybox --rm -it -- nslookup postgres-primary.database.svc.cluster.local

# 4. Assess scope of failure
kubectl get pods --all-namespaces | grep -v Running

Full Cluster Recovery#

bash
# 1. If infrastructure is healthy, restart database cluster
kubectl delete pods --all -n database
kubectl wait --for=condition=Ready pod -l app=postgres -n database --timeout=600s

# 2. If storage is corrupted, restore from backup region
kubectl apply -f manifests/database-dr-region.yaml

# 3. Update DNS and service discovery
kubectl patch service postgres-primary -n database \
  --patch='{"spec":{"externalName":"postgres-dr.us-west-2.rds.amazonaws.com","type":"ExternalName"}}'

# 4. Restore application connectivity
kubectl rollout restart deployment --all -n lilith-bff
kubectl rollout restart deployment --all -n lilith-conversation

Advanced Recovery Scenarios#

Cross-Region Database Recovery#

Scenario: Primary Region Complete Loss#

bash
# 1. Activate cross-region read replica
aws rds promote-read-replica \
  --db-instance-identifier lilith-db-replica-west \
  --region us-west-2

# 2. Update Kubernetes service to point to new region
kubectl apply -f - <<EOF
apiVersion: v1
kind: Service
metadata:
  name: postgres-primary
  namespace: database
spec:
  type: ExternalName
  externalName: lilith-db-primary.us-west-2.rds.amazonaws.com
  ports:
  - port: 5432
EOF

# 3. Update connection strings and secrets
kubectl create secret generic postgres-secret-dr \
  --from-literal=host=lilith-db-primary.us-west-2.rds.amazonaws.com \
  --from-literal=password=${NEW_DB_PASSWORD} \
  -n database

# 4. Rolling restart all applications to pick up new connection
for ns in lilith-bff lilith-auth lilith-conversation; do
  kubectl rollout restart deployment --all -n $ns
done

Point-in-Time Recovery (PITR)#

When PITR is Needed#

  • Data corruption discovered hours after occurrence
  • Accidental data deletion or modification
  • Need to recover to specific transaction point
  • Compliance requirement for data restoration

PITR Process#

bash
# 1. Identify target recovery time
TARGET_TIME="2024-09-15 14:30:00"
echo "Recovering to: $TARGET_TIME"

# 2. Find appropriate base backup
BASE_BACKUP=$(aws s3 ls s3://lilith-db-backups/ | \
  awk -v target="$TARGET_TIME" '$1 " " $2 < target {latest=$4} END {print latest}')

# 3. Create recovery instance
kubectl apply -f postgres-pitr-recovery.yaml

# 4. Restore base backup
kubectl exec -it postgres-pitr-recovery -n database -- \
  aws s3 cp s3://lilith-db-backups/$BASE_BACKUP /var/lib/postgresql/backup.tar.gz

kubectl exec -it postgres-pitr-recovery -n database -- \
  tar -xzf /var/lib/postgresql/backup.tar.gz -C /var/lib/postgresql/data

# 5. Configure recovery target
kubectl exec -it postgres-pitr-recovery -n database -- \
  echo "recovery_target_time = '$TARGET_TIME'" >> /var/lib/postgresql/data/postgresql.conf

kubectl exec -it postgres-pitr-recovery -n database -- \
  echo "recovery_target_action = 'promote'" >> /var/lib/postgresql/data/postgresql.conf

# 6. Start recovery process
kubectl exec -it postgres-pitr-recovery -n database -- \
  pg_ctl start -D /var/lib/postgresql/data

# 7. Monitor recovery progress
kubectl exec -it postgres-pitr-recovery -n database -- \
  tail -f /var/lib/postgresql/data/log/postgresql.log

Data Validation and Integrity Checks#

Post-Recovery Validation#

Database Integrity Checks#

bash
# 1. Check database startup and basic connectivity
kubectl exec -it postgres-primary-0 -n database -- \
  psql -c "SELECT version();"

# 2. Validate all databases are accessible
kubectl exec -it postgres-primary-0 -n database -- \
  psql -c "SELECT datname, datallowconn FROM pg_database WHERE datallowconn = true;"

# 3. Check for corrupted indexes
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "REINDEX DATABASE lilith;"

# 4. Validate foreign key constraints
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "SELECT conname, conrelid::regclass FROM pg_constraint WHERE NOT convalidated;"

Application Data Validation#

bash
# 1. Count critical tables
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "
    SELECT
      'users' as table_name, count(*) as row_count FROM users
    UNION ALL
    SELECT
      'conversations' as table_name, count(*) as row_count FROM conversations
    UNION ALL
    SELECT
      'messages' as table_name, count(*) as row_count FROM messages
    UNION ALL
    SELECT
      'content' as table_name, count(*) as row_count FROM content;"

# 2. Validate data consistency
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "
    SELECT
      u.id as user_id,
      u.email,
      count(c.id) as conversation_count,
      count(m.id) as message_count
    FROM users u
    LEFT JOIN conversations c ON u.id = c.user_id
    LEFT JOIN messages m ON c.id = m.conversation_id
    GROUP BY u.id, u.email
    HAVING count(c.id) = 0 OR count(m.id) = 0
    LIMIT 10;"

# 3. Check recent data
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "
    SELECT
      date_trunc('hour', created_at) as hour,
      count(*) as message_count
    FROM messages
    WHERE created_at > NOW() - INTERVAL '24 hours'
    GROUP BY date_trunc('hour', created_at)
    ORDER BY hour DESC;"

Performance Validation#

bash
# 1. Check database performance
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "
    SELECT
      schemaname,
      tablename,
      seq_scan,
      seq_tup_read,
      idx_scan,
      idx_tup_fetch
    FROM pg_stat_user_tables
    WHERE seq_scan > 1000 OR idx_scan > 10000;"

# 2. Check for slow queries
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "
    SELECT
      query,
      mean_time,
      calls,
      total_time
    FROM pg_stat_statements
    WHERE mean_time > 1000
    ORDER BY mean_time DESC
    LIMIT 10;"

# 3. Check connection pool status
kubectl exec -it postgres-primary-0 -n database -- \
  psql lilith -c "
    SELECT
      state,
      count(*) as connection_count
    FROM pg_stat_activity
    GROUP BY state;"

Backup and Recovery Automation#

Automated Backup Validation#

bash
#!/bin/bash
# Daily backup validation script

BACKUP_DATE=$(date +%Y-%m-%d)
BACKUP_PATH="s3://lilith-db-backups/daily/$BACKUP_DATE"

# 1. Verify backup exists
if aws s3 ls "$BACKUP_PATH" > /dev/null 2>&1; then
  echo "✓ Backup exists for $BACKUP_DATE"
else
  echo "✗ Backup missing for $BACKUP_DATE"
  exit 1
fi

# 2. Download and validate backup
aws s3 cp "$BACKUP_PATH/backup.tar.gz" /tmp/validation-backup.tar.gz
if tar -tzf /tmp/validation-backup.tar.gz > /dev/null 2>&1; then
  echo "✓ Backup archive is valid"
else
  echo "✗ Backup archive is corrupted"
  exit 1
fi

# 3. Test restore in isolated environment
kubectl apply -f - <<EOF
apiVersion: batch/v1
kind: Job
metadata:
  name: backup-validation-$(date +%Y%m%d)
  namespace: database
spec:
  template:
    spec:
      containers:
      - name: postgres-test
        image: postgres:15
        command: ["/bin/bash"]
        args:
        - -c
        - |
          # Extract backup
          tar -xzf /backup/backup.tar.gz -C /var/lib/postgresql/data

          # Start postgres
          pg_ctl start -D /var/lib/postgresql/data

          # Run basic validation queries
          psql -c "SELECT count(*) FROM users;" lilith
          psql -c "SELECT count(*) FROM conversations;" lilith

          echo "Backup validation completed successfully"
        volumeMounts:
        - name: backup-volume
          mountPath: /backup
        - name: data-volume
          mountPath: /var/lib/postgresql/data
      volumes:
      - name: backup-volume
        emptyDir: {}
      - name: data-volume
        emptyDir: {}
      restartPolicy: Never
EOF

Recovery Automation Scripts#

bash
#!/bin/bash
# Automated database recovery script

RECOVERY_TYPE=${1:-"latest"}  # latest, pitr, or specific backup
RECOVERY_TARGET=${2:-""}      # timestamp for PITR

echo "Starting database recovery: type=$RECOVERY_TYPE, target=$RECOVERY_TARGET"

# 1. Create recovery namespace
kubectl create namespace database-recovery || true

# 2. Deploy recovery instance
envsubst < templates/postgres-recovery-template.yaml | kubectl apply -f -

# 3. Wait for pod to be ready
kubectl wait --for=condition=Ready pod postgres-recovery -n database-recovery --timeout=300s

# 4. Execute recovery based on type
case $RECOVERY_TYPE in
  "latest")
    echo "Restoring from latest backup..."
    kubectl exec -it postgres-recovery -n database-recovery -- \
      /scripts/restore-latest-backup.sh
    ;;
  "pitr")
    echo "Performing point-in-time recovery to $RECOVERY_TARGET..."
    kubectl exec -it postgres-recovery -n database-recovery -- \
      /scripts/restore-pitr.sh "$RECOVERY_TARGET"
    ;;
  *)
    echo "Restoring from specific backup: $RECOVERY_TYPE..."
    kubectl exec -it postgres-recovery -n database-recovery -- \
      /scripts/restore-specific-backup.sh "$RECOVERY_TYPE"
    ;;
esac

# 5. Validate recovery
echo "Validating recovered database..."
kubectl exec -it postgres-recovery -n database-recovery -- \
  /scripts/validate-recovery.sh

if [ $? -eq 0 ]; then
  echo "✓ Database recovery completed successfully"
  echo "Recovery instance available at: postgres-recovery.database-recovery.svc.cluster.local"
else
  echo "✗ Database recovery validation failed"
  exit 1
fi

Database Recovery Testing#

Monthly Recovery Drills#

bash
#!/bin/bash
# Monthly database recovery drill

DRILL_DATE=$(date +%Y-%m-%d)
DRILL_LOG="/tmp/db-recovery-drill-$DRILL_DATE.log"

echo "Starting database recovery drill on $DRILL_DATE" | tee $DRILL_LOG

# 1. Create test namespace
kubectl create namespace db-recovery-drill

# 2. Restore from 7-day-old backup
BACKUP_DATE=$(date -d '7 days ago' +%Y-%m-%d)
echo "Restoring from backup: $BACKUP_DATE" | tee -a $DRILL_LOG

# 3. Time the recovery process
start_time=$(date +%s)
kubectl apply -f manifests/recovery-drill-pod.yaml -n db-recovery-drill
kubectl wait --for=condition=Ready pod postgres-drill -n db-recovery-drill --timeout=600s
end_time=$(date +%s)

recovery_duration=$((end_time - start_time))
echo "Recovery completed in $recovery_duration seconds" | tee -a $DRILL_LOG

# 4. Run validation tests
kubectl exec -it postgres-drill -n db-recovery-drill -- \
  /scripts/comprehensive-validation.sh | tee -a $DRILL_LOG

# 5. Cleanup
kubectl delete namespace db-recovery-drill

# 6. Report results
echo "Database recovery drill completed. Results logged to $DRILL_LOG"

Remember: Database recovery is critical for business continuity. Always validate backups, test recovery procedures regularly, and document every step during actual recovery events.