PostgreSQL 可观测性插件套件使用手册

shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache,pg_wait_sampling,pgsentinel'

pg_stat_statements.track = all
pg_stat_statements.max = 10000

pgsentinel.db_name = 'postgres'

pg_ctl -D $PGDATA restart

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;
CREATE EXTENSION pg_wait_sampling;
CREATE EXTENSION pgsentinel;
CREATE EXTENSION dblink;
CREATE SCHEMA profile;
CREATE EXTENSION pg_profile SCHEMA profile;

SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('pg_stat_statements','pg_stat_kcache','pg_wait_sampling','pgsentinel','dblink','pg_profile')
ORDER BY extname;

SELECT coalesce(event_type, 'ON CPU') AS event_type,
       coalesce(event, 'ON CPU') AS event,
       sum(count) AS samples
FROM pg_wait_sampling_profile
GROUP BY 1, 2
ORDER BY samples DESC
LIMIT 10;

SELECT substring(query, 1, 80) AS query,
       round((exec_user_time + exec_system_time)::numeric, 3) AS cpu_sec,
       pg_size_pretty(exec_reads) AS phys_read,
       pg_size_pretty(exec_writes) AS phys_write
FROM pg_stat_kcache_detail
WHERE top IS TRUE
ORDER BY exec_user_time + exec_system_time DESC
LIMIT 10;

SELECT ash_time, pid, usename, application_name,
       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
LIMIT 20;

SELECT profile.take_sample();
-- 等待一段时间或运行负载
SELECT profile.take_sample();
SELECT * FROM profile.show_samples();

\\o /tmp/pg_profile_report.html
SELECT profile.get_report('local', 1, 2);
\\o

SELECT profile.get_diffreport('local', 1, 2, 3, 4);

SELECT profile.create_baseline('local', 'before_upgrade', 1, 2, 90);
SELECT * FROM profile.show_baselines('local');

DROP EXTENSION pg_profile;
DROP EXTENSION pg_stat_kcache;
DROP EXTENSION pg_wait_sampling;
DROP EXTENSION pgsentinel;
DROP EXTENSION pg_stat_statements;
DROP EXTENSION dblink;