SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY 2 DESC;

-- Worst 'idle in transaction' offenders (these hold locks and block vacuum):
SELECT pid, usename, application_name,
       now() - xact_start AS xact_age, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_age DESC LIMIT 10;

-- Who blocks whom (root blockers have blocked_by = {}):
SELECT a.pid,
       a.pid AS blocked_pid,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.wait_event_type,
       now() - a.query_start AS waiting_for,
       left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

-- Detail on the root blocker:
SELECT pid, state, now() - xact_start AS xact_age, left(query, 120) AS query
FROM pg_stat_activity WHERE pid = <root_pid>;

-- Top by cumulative time:
SELECT round(total_exec_time) AS total_ms, calls,
       round(mean_exec_time, 1) AS mean_ms, rows,
       left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- Reset AFTER capturing, to watch fresh accumulation during the incident:
SELECT pg_stat_statements_reset();

-- Currently running long queries:
SELECT pid, now() - query_start AS runtime, state, left(query, 100)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30 seconds'
ORDER BY runtime DESC;

df -h /var/lib/postgresql        # data volume
du -sh /var/lib/postgresql/16/main/pg_wal   # runaway WAL?

-- Dead tuples (vacuum debt):
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Biggest relations:
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
-- Inactive slot retaining GBs → confirm the consumer is truly gone, then:
SELECT pg_drop_replication_slot('dead_slot');

-- On the primary:
SELECT client_addr, state, sent_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- On the replica:
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

SELECT pg_cancel_backend(<pid>);      -- 1) cancel the query
-- wait 5-10s; if still there:
SELECT pg_terminate_backend(<pid>);   -- 2) drop the connection

#!/bin/sh
# Author: Simon-Pierre Boucher
# Contact: contact@spboucher.ai
# snapshot.sh — capture incident evidence before intervening.
TS=$(date +%Y%m%dT%H%M%S)
OUT="incident-$TS.txt"
for Q in \
  "SELECT now()" \
  "SELECT state, count(*) FROM pg_stat_activity GROUP BY state" \
  "SELECT pid, state, now()-query_start rt, wait_event_type, left(query,120) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY rt DESC" \
  "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" \
  "SELECT round(total_exec_time) ms, calls, left(query,100) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15"
do
  echo "== $Q" >> "$OUT"; psql -X -c "$Q" >> "$OUT" 2>&1
done
echo "evidence saved to $OUT"