Debian 上 PostgreSQL 性能监控实操指南
一 监控体系与采集路径
- 指标采集：优先使用 PostgreSQL 内置视图（如 pg_stat_activity、pg_stat_statements、pg_stat_database、pg_stat_bgwriter、pg_stat_replication）获取会话、语句、数据库、后台写入与复制状态；结合 pg_top 实时查看消耗最高的查询与连接。
- 日志采集：打开 logging_collector，设置 log_min_duration_statement 记录慢查询，用 pgBadger 生成 HTML 报告做趋势与问题定位。
- 系统层采集：监控 CPU、内存、I/O、磁盘空间、网络，关注与数据库相关的进程（如 postmaster、autovacuum）与 WAL/数据目录所在磁盘。
- 可视化与告警：用 Prometheus + PostgreSQL Exporter + Grafana 搭建指标大盘与阈值告警；或用 Zabbix/Nagios 做传统监控与短信/邮件告警。
二 快速上手步骤
- 启用关键扩展与参数
- 在 postgresql.conf 中加载扩展并调整规模：
- shared_preload_libraries = ‘pg_stat_statements’
- pg_stat_statements.max = 10000
- pg_stat_statements.track = all
- 重启数据库后，在目标库执行：
- CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
- 打开慢查询日志
- postgresql.conf 建议：
- logging_collector = on
- log_directory = ‘pg_log’
- log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’
- log_min_duration_statement = 1000（单位毫秒，可按需调整）
- 常用即时查询
- 当前连接数：
- SELECT count(*) AS now_conn_cnt FROM pg_stat_activity;
- 运行超过 5 分钟的活跃查询：
- SELECT * FROM pg_stat_activity WHERE state = ‘active’ AND now() - query_start > INTERVAL ‘5 minutes’;
- 最耗时的 SQL（需已启用 pg_stat_statements）：
- SELECT query, total_time, calls, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
- 生成慢查询报告
- 使用 pgBadger 分析日志目录，输出 HTML 报告，用于定位 TOP SQL、高峰时段与异常错误。
三 常用工具与适用场景
| 工具 |
作用 |
典型场景 |
| pg_stat_activity |
会话与查询实时状态 |
查阻塞、长事务、活跃/空闲会话 |
| pg_stat_statements |
标准化 SQL 统计（总耗时、调用次数、均值） |
找出最耗资源的 SQL、SQL 回归分析 |
| pg_stat_bgwriter |
检查点、刷脏、后台写入统计 |
评估 I/O 压力与 checkpoint 调优 |
| pg_stat_replication |
复制延迟与状态 |
主从/流复制健康与延迟监控 |
| pg_top |
类 top 的实时数据库视角 |
快速定位占用高的查询/连接 |
| pgBadger |
日志解析与可视化报告 |
慢查询、错误、高峰时段分析 |
| Prometheus + PostgreSQL Exporter + Grafana |
指标采集、存储与可视化 |
长期趋势、阈值告警、容量规划 |
| Zabbix / Nagios |
传统监控与告警 |
企业级监控集成与通知 |
四 关键告警与阈值建议
- 连接数接近 max_connections 的 80%，或活跃会话异常攀升。
- 复制延迟持续增长或 state 异常（复制中断风险）。
- 慢查询阈值触发率升高（例如 log_min_duration_statement=1000ms 时，告警频率异常）。
- checkpoint 频繁或写入抖动（结合 pg_stat_bgwriter 观察 spikes）。
- 表/索引膨胀、vacuum 不及时（seq_scan 高、n_dead_tup 增长快）。
- 磁盘空间剩余低于 20% 或 WAL/数据目录所在磁盘 I/O 长时间饱和。
- 建议用 Prometheus/Grafana 或 Zabbix 配置上述阈值与通知渠道（邮件、短信、企业微信/钉钉）。
五 进阶与排障
- 用 EXPLAIN (ANALYZE, BUFFERS) 分析慢 SQL 的执行计划与实际耗时，配合索引与查询改写验证效果。
- 用 BPFtrace 跟踪 VACUUM/autovacuum 的执行时长与频率，定位维护任务瓶颈。
- 定期执行 VACUUM / ANALYZE，对大表考虑分区与合理索引策略，减少扫描与膨胀。
- 配置与硬件协同：结合负载调整 shared_buffers、work_mem、effective_cache_size 等参数，优先使用 SSD、保证充足内存与合理 I/O 调度策略。