pg_sqlog

pig build pkg pg_sqlog         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

CREATE EXTENSION pg_sqlog CASCADE;  -- 依赖: file_fdw

-- 当天的日志
SELECT * FROM sqlog.log();

-- 指定日期的日志
SELECT * FROM sqlog.log('yesterday');
SELECT * FROM sqlog.log('2024-01-15');

-- 错误摘要
SELECT error_severity, COUNT(*) FROM sqlog.log() GROUP BY 1;

-- 最慢的 3 种查询模式
SELECT
  AVG(sqlog.duration(message)) AS avg_duration,
  COUNT(*) AS count,
  sqlog.preparable_query(message) AS query_pattern
FROM sqlog.log()
WHERE message ~ '^duration'
GROUP BY 3
ORDER BY 1 DESC
LIMIT 3;

-- 带时间信息的查询摘要
SELECT
  log_time::time,
  sqlog.duration(message),
  sqlog.summary(message)
FROM sqlog.log('yesterday')
WHERE message ~ '^duration';

SELECT * FROM sqlog.autovacuum() LIMIT 5;
SELECT * FROM sqlog.autoanalyze() LIMIT 5;