Ubuntu上监控PostgreSQL性能的实用方案
一 内置统计视图快速排查
- 连接与活跃查询：使用pg_stat_activity查看当前连接、状态、正在执行的SQL与等待事件，定位长事务与阻塞会话。
- 查询性能：启用pg_stat_statements获取SQL的调用次数、总/平均耗时、行数、最慢SQL等，用于识别慢查询与重复执行的热点语句。
- 写入与检查点：通过pg_stat_bgwriter观察检查点、后台写入与刷盘行为，判断I/O压力与检查点频率是否过高。
- 库级概览：用pg_stat_database查看每个数据库的提交/回滚、读写次数、冲突与临时文件等，评估负载分布。
- 表与索引：借助pg_stat_user_tables、pg_stat_user_indexes掌握扫描次数、插入/更新/删除、索引命中与维护成本。
- 维护与复制：用pg_stat_progress_vacuum跟踪VACUUM/分析进度；pg_stat_replication监控复制延迟与状态；需要时查看pg_stat_wal_receiver与pg_stat_ssl了解WAL接收与SSL连接情况。
二 系统层面与日志分析
- 资源监控：用top/htop观察postgres进程的CPU/内存；用vmstat查看系统整体CPU、内存与换页；用iostat关注磁盘I/O等待与利用率，判断是否存在存储瓶颈。
- 日志与报表：将PostgreSQL日志解析为可视化报表，推荐pgBadger生成按小时/日/月的性能与错误分析图表，便于定位高峰时段与异常SQL模式。
三 增强扩展与可视化平台
- 增强查询统计：安装pg_stat_monitor（建议PostgreSQL 13+）。在postgresql.conf加入：shared_preload_libraries = ‘pg_stat_monitor’，重启后于目标库执行：CREATE EXTENSION pg_stat_monitor; 可按查询、用户、时间桶查看更细粒度的执行统计，便于定位间歇性慢SQL与峰值问题。
- 可视化与告警：采用Prometheus + Grafana构建长期监控与面板展示，结合Elephant Shed可快速集成PostgreSQL、Grafana、Prometheus与pgBadger，实现一站式监控、日志分析与备份管理。
- 集中式日志分析：使用Elastic Stack（ELK），通过Logstash + JDBC定期拉取PostgreSQL统计信息写入Elasticsearch，在Kibana中做趋势分析与可视化，并与业务事件做关联排查。
四 五分钟上手流程
- 启用扩展与基础视图
- 编辑**/etc/postgresql/*/main/postgresql.conf**，按需添加：shared_preload_libraries = ‘pg_stat_statements,pg_stat_monitor’（若使用pg_stat_monitor），重启数据库：sudo systemctl restart postgresql。
- 在需要监控的数据库执行：CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;
- 配置日志与报表
- 在postgresql.conf中开启日志收集（如log_statement、log_duration等），使用pgBadger定期解析日志生成报表，观察Top SQL、错误与慢查询趋势。
- 建立可视化监控
- 部署Prometheus与Grafana，添加PostgreSQL Exporter采集指标，导入社区或Elephant Shed提供的Grafana面板，配置连接数、缓存命中率、复制延迟等关键告警阈值。