Runbook: Postgres

Service overview

Single Postgres 18 + pgvector instance per environment, managed by Coolify. PgBouncer (transaction mode, port 6432) sits in front for connection pooling. Migrations run via Drizzle (db:migrate) directly on port 5432 (session mode).

Symptoms → Diagnosis → Fix

Connection pool exhausted (too many clients)

Diagnosis

1. Check PgBouncer stats: psql -h <host> -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;".
2. Check pg_stat_activity for long-running transactions holding connections.

Fix

• Increase PGBOUNCER_MAX_CLIENT_CONN (default 200) or PGBOUNCER_DEFAULT_POOL_SIZE (default 20).
• Kill blocking long-running transactions: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < now() - interval '5 minutes';.

Disk full / low disk warning

Diagnosis

• Check Coolify host metrics for volume usage.
• Common cause: WAL accumulation, dead tuple bloat, pg_dump files left on disk (should not happen with R2 uploads).

Fix

• Run VACUUM FULL on bloated tables (causes table lock — do during low-traffic window).
• Increase Coolify volume size (operator action on Hetzner VPS — contact operator).
• Verify pg_dump script cleans up /tmp on success.

Slow queries

Diagnosis

1. Enable pg_stat_statements (already in init scripts if configured).
2. Query: SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;.

Fix

• Add missing indexes via a new Drizzle migration.
• For immediate relief: ANALYZE <table> to refresh planner statistics.

Migration fails / stuck

Diagnosis

• Check for a stuck migration lock: SELECT * FROM drizzle.__drizzle_migrations; and pg_locks.

Fix

• If migration is partially applied: roll back manually, fix the migration, re-run.
• Never run db:migrate against production without a backup (see pg-dump runbook).

Backup verification

Daily pg_dump runs at 03:00 UTC. Verify: check kc-media-eu-{env}/pg_dumps/ in R2 for today’s dump. For restore procedure, see the restore-drill runbook.