PostgreSQL 监控：SQL、指标与日志

建立 PostgreSQL SQL、指标和日志三层可观测性，覆盖 pg_stat_statements、JSON 日志、postgres_exporter、Prometheus、Grafana 与 pgBadger

PostgreSQL 可观测性至少需要三层证据：SQL 统计解释资源花在哪里，指标说明系统何时偏离基线，日志保留错误与事件上下文。只装一个 dashboard 不能替代这三层。

1. 用 pg_stat_statements 找工作负载热点

pg_stat_statements 是 PostgreSQL 官方扩展。它需要加入 shared_preload_libraries，通常要重启实例，然后在需要统计的 database 中创建扩展：

shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  left(query, 160) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

按总耗时、平均耗时、调用次数、返回行数和 I/O 分别看排行；单次最慢与累计消耗最大不是同一个问题。统计会被重置，部署、故障和参数变更时应记录采样窗口。字段定义以 pg_stat_statements 官方文档为准。

查询文本可能包含敏感信息

扩展会规范化常量，但日志、DDL、动态 SQL 和应用注释仍可能泄露标识符或业务数据。限制统计视图与日志读取权限，并为采集、保留和脱敏设定规则。

2. 使用 JSON 日志保留事件上下文

jsonlog 便于可靠解析时间、SQLSTATE、backend、database、用户、application name 和错误上下文：

logging_collector = on
log_destination = 'jsonlog'
log_min_duration_statement = '500ms'  # 示例值，应按负载基线调整
log_lock_waits = on
deadlock_timeout = '1s'

不要把示例阈值直接复制到所有环境。过低会产生大量 I/O 与敏感查询文本，过高会漏掉高频中等耗时 SQL。优先用 pg_stat_statements 发现累计热点，用日志解释错误、锁等待、检查点、autovacuum 和特定慢请求。参数语义见 Error Reporting and Logging。

pgBadger 可以分析 PostgreSQL 原生日志与 jsonlog，生成查询、连接、错误、锁、检查点和 autovacuum 报告。先确保日志格式稳定、轮转可靠、时区一致，再把它加入离线分析流程。

3. 指标、Prometheus 与 Grafana

postgres_exporter 适合已有 Prometheus/Grafana 的团队。采集角色优先使用 pg_monitor 或必要的只读统计权限，不要给 exporter 超级用户：

CREATE ROLE metrics LOGIN;
GRANT pg_monitor TO metrics;

在目标版本上核对 collector 和权限。其 multi-target 模式仍被上游标为 Beta，自定义 extend.query-path 已 deprecated；新采集需求应优先使用内置 collector 或单独的通用 SQL exporter，而不是积累不可维护的查询文件。

最小信号集

领域	信号	需要一起看的上下文
连接	使用量、等待、连接池队列	pool mode、应用实例数、保留连接
查询	latency、calls、rows、I/O	deploy、plan 变化、参数分布
事务	长事务、idle in transaction、冲突	owner、重试能力、vacuum 影响
锁	等待时长、阻塞链、deadlock	DDL、批任务、业务事务
WAL/复制	生成速率、archive failure、lag、slot retention	RPO、网络、磁盘余量
维护	dead tuples、freeze age、vacuum/analyze 进度	表写入率、autovacuum 参数
存储	数据/WAL/临时文件增长、I/O latency	容量预测、checkpoint、查询 spill
恢复	最近成功备份、可恢复时间、实测 RTO	repository、密钥、恢复演练

阈值应来自正常时段和峰值时段的基线，告警应指向可执行的诊断路径。复制 lag 的字节数、时间和 replay 状态含义不同，不能只设一个全局阈值。

工具采用顺序

所有生产实例先启用并治理 pg_stat_statements。
输出可解析日志，并把 SQLSTATE、锁等待、归档失败和 autovacuum 纳入采集。
已有 Prometheus 时接入 postgres_exporter 与 PostgreSQL 专用 dashboard。
需要日志趋势报告时加入 pgBadger；管理多实例可评估 pgwatch。
深度 workload 分析可评估 PoWA；临时排障可使用 pg_activity。

工具越多不等于盲区越少。先统一 instance、database、role、application、query ID、时间窗口和变更事件这些关联维度。