SQL postgres_exporter 的 pg_stat_activity 指标采集模板

pg_stat_activity 默认不采集 query 字段，因其含敏感信息、超长语句、高频变更，易引发安全、性能与存储问题；postgres_exporter 使用精简视图，并推荐 pg14+ 的 query_id 替代原始 query。

pg_stat_activity 为什么默认不采集 query 字段

因为 pg_stat_activity.query 包含正在执行的 sql 文本，可能含敏感信息、超长语句（几 MB）、高频变更，直接暴露会拖慢 exporter、撑爆 prometheus 存储、引发安全审计问题。postgres_exporter 默认用 pg_stat_activity 的精简视图（不含 query），只保留 pid、state、backend_start 等低开销字段。

• 若硬要加 query，需手动改 custom_queries，且必须配 max_length 截断（如 200 字符）
• postgresql 14+ 支持 pg_stat_activity.query_id，更轻量，建议优先用它做关联分析，而非原始 query
• 启用 track_activity_query_size 参数（默认 1024）才能让 query 字段有值，否则始终为 NULL

怎么写一个安全可用的 custom_queries 模板

别直接 select * FROM pg_stat_activity；得过滤、截断、重命名指标名，否则 Prometheus 会拒收（非法字符、重复 label）。下面这个模板能跑通、可监控连接状态和慢查询倾向：

custom_queries: - name: pg_stat_activity_safe   help: Safe pg_stat_activity metrics without raw query text   metrics:   - pid:       usage: "GAUGE"       description: "Backend process ID"   - state:       usage: "LABEL"       description: "Current overall state of this backend"   - backend_start:       usage: "countER"       description: "Time when this backend process was started"   - state_change:       usage: "COUNTER"       description: "Time when the current backend state was entered"   - application_name:       usage: "LABEL"       description: "Name of the application that is connected to this backend"   - client_hostname:       usage: "LABEL"       description: "Host name of the connected client"   - backend_type:       usage: "LABEL"       description: "Type of current backend"   # 可选：加 query_id，但需 PG >= 14   # - query_id:   #     usage: "GAUGE"   #     description: "Unique identifier for this query"   query: |     SELECT       pid,       state,       EXTRACT(EPOCH FROM backend_start)::int AS backend_start,       EXTRACT(EPOCH FROM state_change)::int AS state_change,       COALESCE(application_name, '') AS application_name,       COALESCE(client_hostname, '') AS client_hostname,       backend_type,       -- 避免 NULL 导致 label 丢失，统一转空字符串       COALESCE(state, 'unknown') AS state_label     FROM pg_stat_activity     WHERE backend_type IN ('client backend', 'background worker')       AND pid != pg_backend_pid()

• EXTRACT(EPOCH FROM ...) 转成整数秒，避免浮点精度问题和 Prometheus 类型冲突
• 所有 COALESCE 是防 NULL —— Prometheus label 不接受 NULL 值，会丢整行指标
• WHERE 过滤掉自身 exporter 连接（pid != pg_backend_pid()）和非业务 backend，减少噪声
• 如果真要抓 query，加 SUBSTRING(query FROM 1 FOR 200) + COALESCE(..., '')，但务必关掉 auto_discover_database 或限定 database_whitelist，否则每个 DB 都扫一遍 query，性能崩

常见报错：column "query" does not exist 或 invalid type for metric

前者是 PostgreSQL 版本太低（track_activities = off；后者多因字段类型没对齐：比如把 text 字段当 GAUGE 用，或 timestamp 没转成数字。

• 检查 track_activities 是否为 on（SHOW track_activities）
• pg_stat_activity 在不同 PG 版本字段差异大：PG 13 去掉 backend_start 的 timestamp with time zone 直接支持，但 PG 10 需显式 ::timestamptz，否则 exporter 解析失败
• label 字段不能是 NULL，也不能含换行符或控制字符 —— REPLACE(REPLACE(application_name, 'n', ' '), 'r', ' ') 更稳妥
• 如果用 query_id，注意它在 PG 14+ 才稳定，且需 pg_stat_statements 扩展配合才可反查 SQL 内容

为什么加了 custom_queries 却看不到新指标

postgres_exporter 不会自动 reload 配置；改完 custom_queries 必须重启进程，或者发 SIGHUP（前提是启动时加了 --web.enable-lifecycle 并用 http POST /-/reload）。

• 确认日志里有没有 Loaded custom queries from ...，没有就是路径错或 YAML 格式非法（尤其注意缩进和冒号后空格）
• 访问 http://localhost:9187/metrics 搜 pg_stat_activity_safe，看是否出现 —— 不出现基本是配置没生效或查询返回空结果集
• 指标名前缀是 name 字段值（如 pg_stat_activity_safe_pid），不是表名；别在 Prometheus 里搜 pg_stat_activity_pid
• 如果查询本身慢（比如 JOIN 多张系统表），exporter 会超时（默认 10s），导致该次 scrape 全部失败，日志报 timeout exceeded —— 加 statement_timeout = '5s' 到查询开头

最麻烦的其实是 label 组合爆炸：比如 application_name 每个微服务实例都带随机后缀，会导致 series 数量失控。上线前一定用 count by (__name__) ({__name__=~"pg_stat_activity_safe_.+"}) 在 Prometheus 里看 cardinality。