Troubleshooting Common Issues

SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) <suspect query>;

SELECT bl.pid AS blocked, ka.query AS blocking_query, kl.mode AS blocking_mode
  FROM pg_locks bl
  JOIN pg_stat_activity ba ON ba.pid = bl.pid
  JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.relation IS NOT DISTINCT FROM bl.relation
                  AND kl.pid <> bl.pid AND kl.granted
  JOIN pg_stat_activity ka ON ka.pid = kl.pid
 WHERE NOT bl.granted;

# Linux: avoid OOM killer killing postgres
vm.overcommit_memory = 2
vm.overcommit_ratio  = 80
# Lower work_mem, max_connections; raise per-cgroup limits

SELECT pg_size_pretty(pg_database_size(d.datname)), d.datname
  FROM pg_database d ORDER BY pg_database_size(d.datname) DESC;
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
  FROM pg_replication_slots;     -- inactive slots pin WAL!

SELECT datname, age(datfrozenxid),
       round(age(datfrozenxid)::numeric / 2000000000, 3) AS pct
  FROM pg_database ORDER BY age(datfrozenxid) DESC;
VACUUM FREEZE VERBOSE;

SET client_min_messages = debug1;
SET plpgsql.extra_errors  = 'all';
SET plpgsql.extra_warnings = 'all';
-- Add RAISE NOTICE in hot paths, or use GET STACKED DIAGNOSTICS

\dp schema.table
SELECT has_table_privilege('alice', 'orders', 'SELECT');
SELECT has_schema_privilege('alice', 'app', 'USAGE');

# postgresql.conf
logging_collector       = on
log_filename            = 'postgresql-%Y-%m-%d.log'
log_line_prefix         = '%m [%p] %u@%d %r %a '
log_min_duration_statement = 250ms
log_checkpoints         = on
log_lock_waits          = on
log_temp_files          = 0
log_autovacuum_min_duration = 0