pgsentinel 插件使用手册

shared_preload_libraries = 'pg_stat_statements,pgsentinel'

track_activity_query_size = 2048
pg_stat_statements.track = all

pgsentinel.db_name = 'postgres'
pgsentinel_ash.sampling_period = 1
pgsentinel_ash.max_entries = 1000
pgsentinel_ash.track_idle_trans = off

pgsentinel_pgssh.enable = off
pgsentinel_pgssh.max_entries = 10000

pg_ctl -D $PGDATA restart

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pgsentinel;

CREATE SCHEMA ash;
CREATE EXTENSION pgsentinel SCHEMA ash;

GRANT pg_monitor TO monitor_user;

SELECT pid, backend_type, application_name, datname
FROM pg_stat_activity
WHERE backend_type = 'pgsentinel'
   OR application_name = 'pgsentinel';

SELECT ash_time, pid, usename, datname, application_name, state,
       coalesce(wait_event_type, 'ON CPU') AS wait_type,
       wait_event, substring(query, 1, 80) AS query
FROM pg_active_session_history
WHERE ash_time > now() - interval '10 minutes'
ORDER BY ash_time DESC, pid
LIMIT 50;

SELECT coalesce(wait_event_type, 'ON CPU') AS wait_type,
       coalesce(wait_event, 'ON CPU') AS wait_event,
       count(*) AS samples
FROM pg_active_session_history
WHERE ash_time > now() - interval '30 minutes'
GROUP BY 1, 2
ORDER BY samples DESC
LIMIT 15;

SELECT ash_time,
       pid AS blocked_pid,
       usename AS blocked_user,
       wait_event_type, wait_event,
       blockers, blockerpid, blocker_state,
       substring(query, 1, 80) AS blocked_query
FROM pg_active_session_history
WHERE blockers > 0
  AND ash_time > now() - interval '1 hour'
ORDER BY ash_time DESC
LIMIT 30;

SELECT blockerpid, blocker_state,
       count(*) AS times_blocking,
       count(DISTINCT pid) AS victims
FROM pg_active_session_history
WHERE blockers > 0
GROUP BY 1, 2
ORDER BY times_blocking DESC
LIMIT 10;

SELECT queryid, cmdtype, count(*) AS active_samples,
       count(DISTINCT pid) AS sessions,
       substring(min(query), 1, 80) AS sample_query
FROM pg_active_session_history
WHERE ash_time > now() - interval '30 minutes'
  AND queryid IS NOT NULL AND queryid <> 0
GROUP BY queryid, cmdtype
ORDER BY active_samples DESC
LIMIT 15;

SELECT substring(top_level_query, 1, 80) AS top_sql,
       substring(query, 1, 80) AS inner_sql,
       cmdtype, count(*) AS samples
FROM pg_active_session_history
WHERE top_level_query IS DISTINCT FROM query
  AND ash_time > now() - interval '30 minutes'
GROUP BY 1, 2, 3
ORDER BY samples DESC
LIMIT 20;

pgsentinel_pgssh.enable = on

SELECT ash_time, queryid, calls, total_exec_time, rows, wal_bytes
FROM pg_stat_statements_history
ORDER BY ash_time DESC
LIMIT 20;

DROP EXTENSION pgsentinel;