Incident P0 Database Down

🚨 EMERGENCY INCIDENT RESPONSE

You are responding to a P0 CRITICAL incident: PostgreSQL database is down.

Severity: P0 - CRITICAL

• Impact: ALL customers affected
• Response Time: IMMEDIATE
• Resolution Target: <15 minutes

Your Mission

Guide rapid diagnosis and recovery with:

• Systematic troubleshooting steps
• Clear commands for each check
• Fast recovery procedures
• Customer communication templates
• Post-incident documentation

IMMEDIATE ACTIONS (First 60 seconds)

1. Verify the Issue

# Is PostgreSQL running?
sudo systemctl status postgresql

# Can we connect?
sudo -u postgres psql -c "SELECT 1;"

# Check recent logs
sudo tail -100 /var/log/postgresql/postgresql-16-main.log

2. Alert Stakeholders

Post to incident channel IMMEDIATELY:

🚨 P0 INCIDENT - Database Down
Time: [TIMESTAMP]
Server: VPS-XXX
Impact: All customers unable to connect
Status: Investigating
ETA: TBD

DIAGNOSTIC PROTOCOL

Check 1: Service Status

sudo systemctl status postgresql
sudo systemctl status pgbouncer  # If installed

Possible states:

• inactive (dead) → Service stopped
• failed → Service crashed
• active (running) → Service running but not responding

Check 2: Process Status

# Check for PostgreSQL processes
ps aux | grep postgres

# Check listening ports
sudo ss -tlnp | grep 5432
sudo ss -tlnp | grep 6432  # pgBouncer

Check 3: Disk Space

df -h /var/lib/postgresql

⚠️ If disk is full (100%):

• This is likely the cause!
• Jump to "Recovery: Disk Full" section

Check 4: Log Analysis

# Check for errors in PostgreSQL log
sudo grep -i "error\|fatal\|panic" /var/log/postgresql/postgresql-16-main.log | tail -50

# Check system logs
sudo journalctl -u postgresql -n 100 --no-pager

# Check for OOM (Out of Memory) kills
sudo grep -i "killed process" /var/log/syslog | grep postgres

Check 5: Configuration Issues

# Test PostgreSQL config
sudo -u postgres /usr/lib/postgresql/16/bin/postgres --check -D /var/lib/postgresql/16/main

# Check for lock files
ls -la /var/run/postgresql/
ls -la /var/lib/postgresql/16/main/postmaster.pid

RECOVERY PROCEDURES

Recovery 1: Simple Service Restart

If service is stopped but no obvious errors:

# Start PostgreSQL
sudo systemctl start postgresql

# Check status
sudo systemctl status postgresql

# Test connection
sudo -u postgres psql -c "SELECT version();"

# Monitor logs
sudo tail -f /var/log/postgresql/postgresql-16-main.log

✅ If successful: Jump to "Post-Recovery" section

Recovery 2: Remove Stale PID File

If error mentions "postmaster.pid already exists":

# Stop PostgreSQL (if running)
sudo systemctl stop postgresql

# Remove stale PID file
sudo rm /var/lib/postgresql/16/main/postmaster.pid

# Start PostgreSQL
sudo systemctl start postgresql

# Verify
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT 1;"

Recovery 3: Disk Full Emergency

If disk is 100% full:

# Find largest files
sudo du -sh /var/lib/postgresql/16/main/* | sort -rh | head -10

# Option A: Clear old logs
sudo find /var/log/postgresql/ -name "*.log" -mtime +7 -delete

# Option B: Vacuum to reclaim space
sudo -u postgres vacuumdb --all --full

# Option C: Archive/delete old WAL files (DANGER!)
# Only if you have confirmed backups!
sudo -u postgres pg_archivecleanup /var/lib/postgresql/16/main/pg_wal 000000010000000000000010

# Check space
df -h /var/lib/postgresql

# Start PostgreSQL
sudo systemctl start postgresql

Recovery 4: Configuration Fix

If config test fails:

# Restore backup config
sudo cp /etc/postgresql/16/main/postgresql.conf.backup /etc/postgresql/16/main/postgresql.conf
sudo cp /etc/postgresql/16/main/pg_hba.conf.backup /etc/postgresql/16/main/pg_hba.conf

# Start PostgreSQL
sudo systemctl start postgresql

Recovery 5: Database Corruption (WORST CASE)

If logs show corruption errors:

# Stop PostgreSQL
sudo systemctl stop postgresql

# Run filesystem check (if safe to do so)
# sudo fsck /dev/sdX  # Only if unmounted!

# Try single-user mode recovery
sudo -u postgres /usr/lib/postgresql/16/bin/postgres --single -D /var/lib/postgresql/16/main

# If that fails, restore from backup (SOP-204)