spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Database Troubleshooting — Diagnostic Patterns78## Contents9- Stage 1: connections10- Stage 2: locks and blockers11- Stage 3: slow queries12- Stage 4: disk, IO, bloat13- Stage 5: replication lag14- Safe kill procedure15- Evidence snapshot script16- Gotchas1718## Stage 1: connections1920```sql21SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY 2 DESC;2223-- Worst 'idle in transaction' offenders (these hold locks and block vacuum):24SELECT pid, usename, application_name,25       now() - xact_start AS xact_age, left(query, 80) AS last_query26FROM pg_stat_activity27WHERE state = 'idle in transaction'28ORDER BY xact_age DESC LIMIT 10;29```3031## Stage 2: locks and blockers3233```sql34-- Who blocks whom (root blockers have blocked_by = {}):35SELECT a.pid,36       a.pid AS blocked_pid,37       pg_blocking_pids(a.pid) AS blocked_by,38       a.wait_event_type,39       now() - a.query_start AS waiting_for,40       left(a.query, 80) AS query41FROM pg_stat_activity a42WHERE cardinality(pg_blocking_pids(a.pid)) > 043ORDER BY waiting_for DESC;4445-- Detail on the root blocker:46SELECT pid, state, now() - xact_start AS xact_age, left(query, 120) AS query47FROM pg_stat_activity WHERE pid = <root_pid>;48```4950Kill the ROOT of the tree, not the waiters — they resolve on their own.5152## Stage 3: slow queries5354```sql55-- Top by cumulative time:56SELECT round(total_exec_time) AS total_ms, calls,57       round(mean_exec_time, 1) AS mean_ms, rows,58       left(query, 100) AS query59FROM pg_stat_statements60ORDER BY total_exec_time DESC LIMIT 10;6162-- Reset AFTER capturing, to watch fresh accumulation during the incident:63SELECT pg_stat_statements_reset();6465-- Currently running long queries:66SELECT pid, now() - query_start AS runtime, state, left(query, 100)67FROM pg_stat_activity68WHERE state = 'active' AND now() - query_start > interval '30 seconds'69ORDER BY runtime DESC;70```7172## Stage 4: disk, IO, bloat7374```bash75df -h /var/lib/postgresql        # data volume76du -sh /var/lib/postgresql/16/main/pg_wal   # runaway WAL?77```7879```sql80-- Dead tuples (vacuum debt):81SELECT relname, n_dead_tup, n_live_tup, last_autovacuum82FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;8384-- Biggest relations:85SELECT relname, pg_size_pretty(pg_total_relation_size(relid))86FROM pg_statio_user_tables87ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;88```8990Out of disk: free space OUTSIDE the data dir first (old logs, temp dumps).91Runaway `pg_wal` usually means a dead replication slot:9293```sql94SELECT slot_name, active, pg_size_pretty(95  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained96FROM pg_replication_slots;97-- Inactive slot retaining GBs → confirm the consumer is truly gone, then:98SELECT pg_drop_replication_slot('dead_slot');99```100101## Stage 5: replication lag102103```sql104-- On the primary:105SELECT client_addr, state, sent_lsn, replay_lsn,106       write_lag, flush_lag, replay_lag107FROM pg_stat_replication;108109-- On the replica:110SELECT now() - pg_last_xact_replay_timestamp() AS lag;111```112113## Safe kill procedure114115```sql116SELECT pg_cancel_backend(<pid>);      -- 1) cancel the query117-- wait 5-10s; if still there:118SELECT pg_terminate_backend(<pid>);   -- 2) drop the connection119```120121Never `kill -9` a backend: Postgres treats it as a crash and restarts with122full recovery, turning one bad query into a full outage.123124## Evidence snapshot script125126```bash127#!/bin/sh128# Author: Simon-Pierre Boucher129# Contact: contact@spboucher.ai130# snapshot.sh — capture incident evidence before intervening.131TS=$(date +%Y%m%dT%H%M%S)132OUT="incident-$TS.txt"133for Q in \134  "SELECT now()" \135  "SELECT state, count(*) FROM pg_stat_activity GROUP BY state" \136  "SELECT pid, state, now()-query_start rt, wait_event_type, left(query,120) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY rt DESC" \137  "SELECT a.pid, pg_blocking_pids(a.pid), left(a.query,100) FROM pg_stat_activity a WHERE cardinality(pg_blocking_pids(a.pid)) > 0" \138  "SELECT round(total_exec_time) ms, calls, left(query,100) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15"139do140  echo "== $Q" >> "$OUT"; psql -X -c "$Q" >> "$OUT" 2>&1141done142echo "evidence saved to $OUT"143```144145## Gotchas146147- `pg_stat_activity.query` shows the LAST query for idle sessions — an148  `idle in transaction` session's displayed query already finished; the149  transaction is what's still open.150- `pg_blocking_pids()` is Postgres ≥ 9.6; on older versions use the classic151  pg_locks self-join.152- `pg_stat_statements` normalizes literals (`WHERE id = $1`) — you cannot153  recover the exact parameter values from it; check application logs for those.154- Lock waits don't consume CPU — "server looks idle but everything hangs" is155  the lock-stage signature, not a reason to skip to stage 4.156- On replicas, long SELECTs conflict with WAL replay; lag with an idle-looking157  replica often traces to one analytics query.158- After ANY intervention, re-run the stage check — a killed blocker often159  reveals a second blocker behind it.160