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.