PostgreSQL 运维与监控

先搞清楚看什么

PostgreSQL 自带的统计视图已经覆盖了大部分运维问题，不需要一上来就上监控平台。日常排查的顺序通常是：

1. 现在有没有事：pg_stat_activity 看连接、活跃查询、长事务。
2. 哪条 SQL 在拖后腿：pg_stat_statements 按总耗时/平均耗时排序。
3. 慢在哪一步：慢查询日志 + EXPLAIN (ANALYZE, BUFFERS)。
4. 趋势和告警：postgres_exporter 把指标送进 Prometheus，配 Grafana 面板和告警规则。

本篇按这个顺序讲。统计信息的采集有成本，尤其 pg_stat_statements 会归一化 SQL 文本，生产开启前先评估内存开销。

pg_stat_activity：连接与长事务

这是最常用的视图，一行一个连接（包括后台进程）：

SELECT
    pid,
    usename,
    datname,
    client_addr,
    application_name,
    state,
    wait_event_type,
    wait_event,
    now() - xact_start  AS xact_age,
    now() - query_start AS query_age,
    left(query, 80)     AS query_snippet
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY xact_start NULLS LAST;

关键列的含义：

列	说明
state	active 正在执行、idle 空闲、idle in transaction 事务开着没干活、idle in transaction (aborted) 事务出错未回滚
wait_event_type / wait_event	等待类型：Lock 等锁、IO 等 IO、Client 等客户端、LWLock 轻量锁
xact_start	事务开始时间，判断长事务看这个，不是 query_start
query_start	当前语句开始时间
state_change	状态最近一次变化时间
backend_type	client backend、autovacuum worker、checkpointer 等

几个高频查询：

-- 1. 按状态统计连接数
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

-- 2. 找超过 5 分钟的事务（长事务会拖住 VACUUM）
SELECT pid, usename, datname, state,
       now() - xact_start AS xact_age,
       left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

-- 3. 找 idle in transaction 的连接（最常见的「事务忘了提交」）
SELECT pid, usename, application_name,
       now() - state_change AS idle_age,
       left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

处理手段：

-- 温和取消当前查询，连接保留
SELECT pg_cancel_backend(12345);

-- 强制断开连接（会回滚事务）
SELECT pg_terminate_backend(12345);

-- 按条件批量清理空闲事务（谨慎）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '30 minutes';

别在生产随手 terminate

pg_terminate_backend 会回滚事务，如果对方正在做大批量写入，回滚和后续重试可能带来二次冲击。优先 pg_cancel_backend，确认对方是僵尸连接再 terminate。批量清理前先把 PID 列表存下来，方便事后追查是哪个应用。

连接数快满了怎么办

SELECT count(*) FROM pg_stat_activity 接近 max_connections 时，先看是谁占的：按 application_name、client_addr 分组，通常能定位到某个应用没配连接池或者连接泄漏。短期可以调大 max_connections（需要重启），长期必须上 PgBouncer，第 6 篇细讲。

pg_stat_statements：慢 SQL 排序

pg_stat_activity 只能看到「此刻正在跑什么」，看不到「过去一小时谁最耗资源」。pg_stat_statements 把 SQL 归一化后累计统计，是找慢 SQL 的主力工具。

开启

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000          # 保留多少条归一化语句
pg_stat_statements.track = top          # top 只跟踪顶层语句，all 连嵌套函数也跟踪
pg_stat_statements.track_utility = on   # 是否跟踪 DDL 等工具命令

shared_preload_libraries 必须重启才生效，然后建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 确认加载成功
SHOW shared_preload_libraries;
SELECT * FROM pg_stat_statements LIMIT 1;

常用排序

-- 按总耗时排序：找出「拖垮数据库」的语句
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
    rows,
    left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 按平均耗时排序：找「单条就很慢」的语句
SELECT calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2)  AS max_ms,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 按共享块读取排序：找「大量扫表/缓存不命中」的语句
SELECT calls,
       shared_blks_hit,
       shared_blks_read,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

读结果时注意：

• 总耗时高 + 调用次数多：单条不慢但架不住量大，优先优化索引或减少调用。
• 总耗时高 + 调用次数少：单条慢，通常是缺索引、统计信息过期或 SQL 写得复杂。
• shared_blks_read 高：缓存命中差，可能是冷数据、shared_buffers 不足或全表扫。
• rows 与实际返回差很多：优化器估算偏差，需要 ANALYZE 或调统计目标。

重置统计

SELECT pg_stat_statements_reset();

调整参数、加完索引后重置一次，再观察一段时间，避免旧数据干扰判断。这个函数默认只有超级用户能执行，生产上一般由 DBA 操作。

慢查询日志

pg_stat_statements 适合「回头看」，慢查询日志适合「抓现场」。开启方式：

# postgresql.conf
log_min_duration_statement = 1000    # 超过 1 秒记录；0 记录全部（仅调试用）；-1 关闭
log_line_prefix = '%m [%p] %u@%d app=%a client=%h '
log_lock_waits = on                  # 锁等待超过 deadlock_timeout 记录
log_temp_files = 0                   # 记录所有临时文件，排查排序落盘
log_autovacuum_min_duration = 1000   # 记录超过 1 秒的 autovacuum
log_checkpoints = on

日志长这样：

2024-06-01 15:30:12.345 CST [12345] app_user@app_db app=order-svc client=10.0.0.21 LOG:  duration: 1523.456 ms  statement: SELECT ...

log_line_prefix 里的占位符值得记：%m 时间戳、%p PID、%u 用户、%d 数据库、%a 应用名、%h 客户端地址、%r 客户端地址加端口。排查时能直接定位到来源应用。

几个实践经验：

• 阈值别设太低。log_min_duration_statement = 100 在高峰期会产生海量日志，磁盘很快写满。先设 1 秒，观察一周再决定是否收紧。
• 日志要切割。Debian/Ubuntu 用 logrotate 管理 /var/log/postgresql/，确认 daily、rotate 14 这类配置生效。
• 别只盯 duration。log_lock_waits 和 log_temp_files 能解释「为什么慢」：等锁还是排序落盘。
• 日志分析工具。pgBadger 可以解析日志生成报告，按 SQL、用户、时间分布统计；量不大时 grep + awk 也够用。

auto_explain 抓执行计划

只知道哪条 SQL 慢还不够，想看它当时的执行计划，可以加载 auto_explain：

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1000
auto_explain.log_analyze = on
auto_explain.log_buffers = on

超过阈值的语句会自动把执行计划写进日志，适合抓偶发的慢查询。auto_explain.log_analyze 有性能开销，别长期开着。

postgres_exporter 接 Prometheus

自带的视图适合临时排查，长期趋势和告警要靠监控系统。postgres_exporter 把 pg_stat_* 转成 Prometheus 指标，端口默认 9187。

建监控账号

不要用超级用户跑 exporter，给它一个只读的监控角色：

CREATE ROLE exporter WITH LOGIN PASSWORD 'Exporter_2024';
GRANT pg_monitor TO exporter;   -- PG 10+ 内置角色，可读大部分统计视图

启动 exporter

docker run -d \
  --name postgres-exporter \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://exporter:Exporter_2024@10.0.0.11:5432/postgres?sslmode=disable" \
  quay.io/prometheuscommunity/postgres-exporter:v0.15.0

验证能出指标：

curl -s http://10.0.0.11:9187/metrics | grep '^pg_up'
# pg_up 1

加进 Prometheus

scrape_configs:
  - job_name: "postgresql"
    scrape_interval: 15s
    static_configs:
      - targets: ["10.0.0.11:9187"]
        labels:
          instance: "pg-primary"
          env: "prod"

instance 标签建议写成可读的主机名，Prometheus 默认用 目标:端口，出告警时不好认。多实例就多加几个 target。

重点指标

指标	含义	关注点
pg_up	exporter 能否连上数据库	为 0 说明实例不可用或认证失败
pg_stat_activity_count	按状态/用户/库分的连接数	state="active" 持续高位要查
pg_stat_activity_max_tx_duration	最长事务持续时间（秒）	超过 300 秒考虑告警
pg_locks_count	按锁模式分的锁数量	某模式突增说明有锁等待
pg_stat_database_xact_commit	提交事务计数（counter）	用 rate() 看 TPS
pg_stat_database_xact_rollback	回滚计数	回滚率突增要查应用
pg_stat_database_blks_hit / blks_read	缓存命中/磁盘读	算缓存命中率
pg_stat_database_numbackends	各库连接数	接近上限时告警
pg_replication_lag	备库延迟（秒）	大于 30 秒告警
pg_database_size_bytes	库大小	看增长趋势和容量规划
pg_stat_user_tables_n_dead_tup	死元组数量	持续增长说明 VACUUM 跟不上

几条常用的 PromQL：

# 每秒事务数
sum(rate(pg_stat_database_xact_commit[5m]))

# 缓存命中率（低于 95% 要关注）
sum(rate(pg_stat_database_blks_hit[5m]))
  / (sum(rate(pg_stat_database_blks_hit[5m])) + sum(rate(pg_stat_database_blks_read[5m])))

# 活跃连接数
sum(pg_stat_activity_count{state="active"})

# 复制延迟超过 30 秒
pg_replication_lag > 30

# 死元组占比（需要结合表的 live_tup 一起看，这里只做趋势）
sum by (datname) (pg_stat_user_tables_n_dead_tup)

监控体系的分工

postgres_exporter 只负责「翻译指标」，存储、查询、告警、可视化都不在它这儿。指标进入 Prometheus 之后怎么存、怎么用 PromQL 查、告警规则怎么写，见 Prometheus 核心概念与架构。Exporter 仅暴露当前值，历史趋势要靠 Prometheus 的 TSDB。

exporter 的连接也会占坑

postgres_exporter 每次抓取都会短暂占用连接，抓取间隔太短、exporter 频繁重启或目标实例很多时，可能把连接数吃满。给 exporter 留出独立的连接预算，别和业务抢。

监控分层与告警思路

别一上来就配几十条告警，先分三层：

层级	看什么	典型告警
可用性	实例能不能连、主备是否正常	pg_up == 0、复制中断、磁盘满
资源	连接数、CPU、内存、磁盘、IO	连接数 > 80%、磁盘使用率 > 85%
业务质量	慢查询、锁等待、死元组、复制延迟	长事务 > 5 分钟、复制延迟 > 30 秒

告警要有明确的处理动作，否则就是噪音。「连接数 80%」的动作用「上连接池或查泄漏」，「复制延迟 30 秒」的动作用「查网络、查备库 IO、查大事务」。第 6 篇会按故障场景逐条展开。

小结

• pg_stat_activity 看连接状态、长事务、等待事件；判断长事务用 xact_start，处理优先 pg_cancel_backend。
• pg_stat_statements 是找慢 SQL 的主力，按总耗时、平均耗时、共享块读三个维度排序；开启需要重启并建扩展。
• 慢查询日志抓现场，log_min_duration_statement 阈值别太低，配合 log_lock_waits、log_temp_files 定位原因。
• postgres_exporter 把指标转给 Prometheus，监控账号用 pg_monitor 角色，别用超级用户。
• 重点盯 pg_up、连接数、长事务、锁、缓存命中率、复制延迟、死元组，告警要带处理动作。
• 监控分可用性、资源、业务质量三层，先保证底层，再谈业务指标。

最后一篇进入故障现场：连接数打满、锁等待、表膨胀、复制延迟，一个个来。