pgsentinel

pig build pkg pgsentinel;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pgsentinel;		# install via package name, for the active PG version

pig install pgsentinel -v 18;   # install for PG 18
pig install pgsentinel -v 17;   # install for PG 17
pig install pgsentinel -v 16;   # install for PG 16
pig install pgsentinel -v 15;   # install for PG 15
pig install pgsentinel -v 14;   # install for PG 14

shared_preload_libraries = 'pgsentinel';

CREATE EXTENSION pgsentinel;

SELECT ash_time, datname, usename, pid, state,
       wait_event_type, wait_event, query, queryid
FROM pg_active_session_history
ORDER BY ash_time DESC;

SELECT ash_time, queryid, calls, total_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements_history
ORDER BY ash_time DESC;

-- Top wait events in the last hour
SELECT wait_event_type, wait_event, count(*)
FROM pg_active_session_history
WHERE ash_time > now() - interval '1 hour'
  AND wait_event IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

-- Blocking analysis
SELECT blockerpid, blocker_state, count(*)
FROM pg_active_session_history
WHERE blockers > 0
GROUP BY 1, 2
ORDER BY 3 DESC;