Fairdb Emergency Response

You are responding to a critical incident in the FairDB PostgreSQL infrastructure. Follow this structured approach to diagnose, contain, and resolve the issue.

Incident Classification

First, identify the incident type:

• P1 Critical: Complete service outage, data loss risk
• P2 High: Major degradation, affecting multiple customers
• P3 Medium: Single customer impact, performance issues
• P4 Low: Minor issues, cosmetic problems

Initial Assessment (First 5 Minutes)

#!/bin/bash
# FairDB Emergency Response Script

echo "================================================"
echo "    FAIRDB EMERGENCY INCIDENT RESPONSE"
echo "    Started: $(date '+%Y-%m-%d %H:%M:%S')"
echo "================================================"

# Create incident log
INCIDENT_ID="INC-$(date +%Y%m%d-%H%M%S)"
INCIDENT_LOG="/opt/fairdb/incidents/${INCIDENT_ID}.log"
mkdir -p /opt/fairdb/incidents

{
    echo "Incident ID: $INCIDENT_ID"
    echo "Response started: $(date)"
    echo "Responding user: $(whoami)"
    echo "========================================"
…

Step 1: Service Status Check

echo -e "\n[STEP 1] SERVICE STATUS CHECK" | tee -a $INCIDENT_LOG
echo "------------------------------" | tee -a $INCIDENT_LOG

# Check PostgreSQL service
if systemctl is-active --quiet postgresql; then
    echo "✅ PostgreSQL: RUNNING" | tee -a $INCIDENT_LOG
else
    echo "❌ CRITICAL: PostgreSQL is DOWN" | tee -a $INCIDENT_LOG
    echo "Attempting emergency restart..." | tee -a $INCIDENT_LOG

    # Try to start the service
    sudo systemctl start postgresql 2>&1 | tee -a $INCIDENT_LOG

    sleep 5

    if systemctl is-active --quiet postgresql; then
        echo "✅ PostgreSQL restarted successfully" | tee -a $INCIDENT_LOG
    else
…

Step 2: Connection Diagnostics

echo -e "\n[STEP 2] CONNECTION DIAGNOSTICS" | tee -a $INCIDENT_LOG
echo "--------------------------------" | tee -a $INCIDENT_LOG

# Test local connection
echo "Testing local connection..." | tee -a $INCIDENT_LOG
if sudo -u postgres psql -c "SELECT 1;" > /dev/null 2>&1; then
    echo "✅ Local connections: OK" | tee -a $INCIDENT_LOG

    # Get connection stats
    sudo -u postgres psql -t -c "
        SELECT 'Active connections: ' || count(*)
        FROM pg_stat_activity
        WHERE state != 'idle';" | tee -a $INCIDENT_LOG

    # Check for connection exhaustion
    MAX_CONN=$(sudo -u postgres psql -t -c "SHOW max_connections;")
    CURRENT_CONN=$(sudo -u postgres psql -t -c "SELECT count(*) FROM pg_stat_activity;")
…

Step 3: Performance Emergency Response

echo -e "\n[STEP 3] PERFORMANCE TRIAGE" | tee -a $INCIDENT_LOG
echo "----------------------------" | tee -a $INCIDENT_LOG

# Find and kill long-running queries
echo "Checking for blocked/long queries..." | tee -a $INCIDENT_LOG

sudo -u postgres psql << 'EOF' | tee -a $INCIDENT_LOG
-- Queries running longer than 5 minutes
SELECT
    pid,
    now() - query_start as duration,
    state,
    LEFT(query, 100) as query_preview
FROM pg_stat_activity
WHERE state != 'idle'
AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;
…

Step 4: Data Integrity Check

echo -e "\n[STEP 4] DATA INTEGRITY CHECK" | tee -a $INCIDENT_LOG
echo "------------------------------" | tee -a $INCIDENT_LOG

# Check for corruption indicators
echo "Checking for corruption indicators..." | tee -a $INCIDENT_LOG

# Check PostgreSQL data directory
DATA_DIR="/var/lib/postgresql/16/main"
if [ -d "$DATA_DIR" ]; then
    echo "Data directory exists: $DATA_DIR" | tee -a $INCIDENT_LOG

    # Check for recovery in progress
    if [ -f "$DATA_DIR/recovery.signal" ]; then
        echo "⚠️  Recovery in progress!" | tee -a $INCIDENT_LOG
    fi

    # Check WAL status
    WAL_COUNT=$(ls -1 $DATA_DIR/pg_wal/*.partial 2>/dev/null | wc -l)
…

Step 5: Emergency Recovery Actions

echo -e "\n[STEP 5] RECOVERY ACTIONS" | tee -a $INCIDENT_LOG
echo "-------------------------" | tee -a $INCIDENT_LOG

# Determine if recovery is needed
read -p "Do you need to initiate emergency recovery? (yes/no): " NEED_RECOVERY

if [ "$NEED_RECOVERY" = "yes" ]; then
    echo "Starting emergency recovery procedures..." | tee -a $INCIDENT_LOG

    # Option 1: Restart in single-user mode for repairs
    echo "Option 1: Single-user mode repair" | tee -a $INCIDENT_LOG
    echo "Command: sudo -u postgres /usr/lib/postgresql/16/bin/postgres --single -D $DATA_DIR" | tee -a $INCIDENT_LOG

    # Option 2: Restore from backup
    echo "Option 2: Restore from backup" | tee -a $INCIDENT_LOG

    # Check available backups
    if command -v pgbackrest &> /dev/null; then
…

Step 6: Customer Communication

echo -e "\n[STEP 6] CUSTOMER IMPACT ASSESSMENT" | tee -a $INCIDENT_LOG
echo "------------------------------------" | tee -a $INCIDENT_LOG

# Identify affected customers
echo "Affected customer databases:" | tee -a $INCIDENT_LOG

AFFECTED_DBS=$(sudo -u postgres psql -t -c "
    SELECT datname FROM pg_database
    WHERE datname NOT IN ('postgres', 'template0', 'template1')
    ORDER BY datname;")

for DB in $AFFECTED_DBS; do
    # Check if database is accessible
    if sudo -u postgres psql -d $DB -c "SELECT 1;" > /dev/null 2>&1; then
        echo "  ✅ $DB - Operational" | tee -a $INCIDENT_LOG
    else
        echo "  ❌ $DB - IMPACTED" | tee -a $INCIDENT_LOG
    fi
…

Step 7: Post-Incident Checklist

echo -e "\n[STEP 7] STABILIZATION CHECKLIST" | tee -a $INCIDENT_LOG
echo "---------------------------------" | tee -a $INCIDENT_LOG

# Verification checklist
cat << 'EOF' | tee -a $INCIDENT_LOG
Post-Recovery Verification:
[ ] PostgreSQL service running
[ ] All customer databases accessible
[ ] Backup system operational
[ ] Monitoring alerts cleared
[ ] Network connectivity verified
[ ] Disk space adequate (>20% free)
[ ] CPU usage normal (<80%)
[ ] Memory usage normal (<90%)
[ ] No blocking locks
[ ] No long-running queries
[ ] Recent backup available
[ ] Customer access verified
…