7. 明察秋毫：使用监控系统洞察性能与问题
本章为 AI 生成的头脑风暴草稿目录，尚未编写，请务必注意。
章节大纲
一、监控基础概念
1.1 为什么需要监控
- 预防性维护：及时发现性能瓶颈，防止系统崩溃
- 资源优化：了解资源使用情况，优化硬件和软件配置
- 业务连续性：确保数据库高性能支持更多并发用户
- 问题诊断：快速定位和解决数据库问题
- 容量规划：基于历史数据预测未来需求
1.2 监控的层次架构
- 基础设施层：CPU、内存、磁盘、网络
- 操作系统层：进程、文件系统、内核参数
- 数据库层：连接、事务、锁、查询
- 应用层：业务指标、用户体验
1.3 黄金监控指标
- 错误率（Errors）：数据库错误和失败率
- 延迟（Latency）：查询响应时间
- 吞吐量（Throughput）：QPS、TPS
- 饱和度（Saturation）：资源使用率
二、PostgreSQL 内置监控体系
2.1 累积统计系统（Cumulative Statistics System）
- pg_stat_database：数据库级统计
- pg_stat_user_tables：表级统计
- pg_stat_user_indexes：索引使用统计
- pg_stat_bgwriter：后台写进程统计
- pg_stat_archiver：归档进程统计
- pg_stat_replication：复制统计
2.2 动态统计视图
- pg_stat_activity：当前活动会话
- pg_locks：锁信息
- pg_stat_progress_*：进度监控视图
2.3 pg_stat_statements 扩展
- 安装和配置
- 查询性能统计
- Top SQL 分析
- 查询优化建议
三、监控工具生态系统
3.1 命令行工具
- pg_top：类似 top 的 PostgreSQL 监控工具
- pg_activity：类似 htop 的活动监控
- pgcenter：综合监控工具
- pgBadger：日志分析工具
3.2 开源监控方案
- Prometheus + Grafana 监控栈
- postgres_exporter 配置
- 预构建仪表板
- 告警规则设置
- pgwatch2：自包含监控解决方案
- pgMonitor：Crunchy Data 监控套件
- Zabbix/Nagios/Icinga 集成
3.3 商业监控产品
- pganalyze：SaaS 监控与调优
- pgDash：综合监控仪表板
- DataDog/New Relic/Dynatrace APM
- 云厂商监控（AWS CloudWatch、Azure Monitor、GCP Monitoring）
3.4 专用监控工具
- pgAudit：审计日志
- pgHero：性能洞察
- PoWA：工作负载分析器
四、关键指标详解
4.1 连接和会话监控
- 活动连接数与最大连接数
- 连接池监控（PgBouncer/Pgpool-II）
- 空闲会话与长事务
- 等待事件分析
4.2 查询性能监控
- 慢查询识别与分析
- 查询计划监控
- 缓存命中率
- 临时文件使用
4.3 存储和I/O监控
- 表膨胀（Bloat）监控
- VACUUM 和 AUTOVACUUM 监控
- WAL 生成速率
- 磁盘I/O性能
4.4 复制和高可用监控
- 复制延迟
- 复制槽状态
- 恢复冲突
- 故障切换就绪状态
五、监控最佳实践
5.1 指标采集策略
- 采集频率设置
- 数据保留策略
- 采样与聚合
5.2 告警设计
- 分级告警策略
- 动态阈值 vs 静态阈值
- 告警降噪与聚合
- 告警路由与升级
5.3 可视化设计
- 仪表板层次结构
- 关键指标展示
- 钻取式分析
- 实时与历史数据对比
六、容器和云原生监控
6.1 Kubernetes 环境监控
- Operator 监控集成
- Service Mesh 观测性
- Pod 和容器指标
6.2 OpenTelemetry 集成
- 分布式追踪
- 指标、日志、追踪统一
- 跨服务关联分析
6.3 云平台集成
- AWS RDS 监控
- Azure Database for PostgreSQL
- Google Cloud SQL
七、高级监控主题
7.1 实时变更数据捕获（CDC）
- Debezium 集成
- 实时数据流监控
- 事件驱动架构
7.2 日志管理与分析
- ELK Stack 集成
- 结构化日志
- 日志聚合与搜索
7.3 性能基线与趋势分析
- 建立性能基线
- 异常检测
- 容量预测
八、动手实验
实验1：搭建 Prometheus + Grafana 监控系统
- 安装配置 postgres_exporter
- 导入仪表板模板
- 配置告警规则
实验2：使用 pg_stat_statements 分析慢查询
- 识别 Top SQL
- 分析执行计划
- 优化建议实施
实验3：配置审计日志
- 安装 pgAudit
- 配置审计策略
- 日志分析与合规报告
实验4：容器环境监控
- Docker 环境监控配置
- Kubernetes Operator 监控
- 分布式追踪实践
参考资料
官方文档
- PostgreSQL: Documentation - Chapter 27. Monitoring Database Activity
- PostgreSQL: Documentation - The Cumulative Statistics System
- PostgreSQL: Documentation - pg_stat_statements
- PostgreSQL: Documentation - VACUUM
- PostgreSQL: Documentation - Routine Vacuuming
- PostgreSQL: Documentation - Automatic Vacuuming
- PostgreSQL Wiki - Monitoring
- PostgreSQL Wiki - Performance Analysis Tools
Vonng.com 博客文章
- pg_stat_statements 宏观查询优化
- PostgreSQL 到底有多强？
- Cloudflare是如何用15个PG集群支持55M QPS
- PostgreSQL规约（PG16）
- PostgreSQL：最成功的数据库
- 为什么PostgreSQL前途无量？
- Pigsty Documentation
- PGSQL x Pigsty: 数据库全能王
监控工具和项目
- prometheus-community/postgres_exporter
- lesovsky/pgscv - Multi-purpose monitoring agent
- pgsty/pg_exporter - Advanced PostgreSQL Metrics Exporter
- CrunchyData/pgmonitor - PostgreSQL Monitoring Resources
- coroot/coroot-pg-agent - Query performance statistics
- cybertec-postgresql/pgwatch2 - PostgreSQL metrics monitor
- dalibo/pg_activity - Top like application for PostgreSQL
- darold/pgbadger - PostgreSQL Log Analyzer
- daamien/PostgreSQL-Dashboard - Real-time monitoring screen
- percona/pg_stat_monitor - Query Performance Monitoring
监控最佳实践
- PostgreSQL Monitoring: Key Metrics, Best Practices & Top Tools - Middleware
- PostgreSQL Monitoring: A Complete Guide - TheDBAdmin
- Top 10 PostgreSQL Monitoring Tools Guide - SigNoz
- PostgreSQL Monitoring Tools - Better Stack
- PostgreSQL monitoring & alerting: Best practices - DrDroid
- How to Improve PostgreSQL Monitoring with PMM - Percona
- 5 Ways to Monitor Your PostgreSQL Database - TigerData
- Collecting metrics with PostgreSQL monitoring tools - Datadog
Prometheus 和 Grafana
- Complete Guide To Monitor PostgreSQL With Prometheus and Grafana - Medium
- PostgreSQL Tutorial: Monitoring with Prometheus and Grafana - Redrock
- Monitoring PostgreSQL With Prometheus And Grafana - Ashnik
- Monitor PostgreSQL with Prometheus and Grafana on Ubuntu - HowtoForge
- Monitor PostgreSQL Server With Prometheus and Grafana - ComputingForGeeks
- Configure PostgreSQL exporter for Prometheus - Grafana Cloud
- Monitoring PostgreSQL on Kubernetes with Prometheus & Grafana - Medium
- PostgreSQL monitoring made easy - Grafana Labs
pg_stat_statements 和性能分析
- pg_stat_monitor Documentation - Percona
- Monitoring PostgreSQL Performance with pg_stat_statements - DataSentinel
- PostgreSQL Performance Tuning with pg_stat_statements - Medium
- Enhancing PostgreSQL Performance Monitoring with pg_stat_statements - Stormatics
审计和安全监控
- PostgreSQL Audit Extension - PGAudit
- Audit for PostgreSQL using pgAudit - Google Cloud
- Auditing PostgreSQL Using PgAudit - ScaleGrid
- What Is Audit Logging in PostgreSQL - TigerData
- Using pgAudit to log database activity - AWS RDS
- 3 Postgres Audit Methods: How to Choose - Satori
- PostgreSQL Insider - Detecting database security threats
VACUUM 和 Autovacuum
- PostgreSQL VACUUM Guide and Best Practices - EDB
- Visualizing & Tuning Postgres Autovacuum - pganalyze
- Vacuuming and analyzing tables automatically - AWS
- PostgreSQL VACUUM, AUTOVACUUM and ANALYZE Processes - MSSQLTips
- Essential Guide to the PostgreSQL VACUUM Command - Percona
- Autovacuum tuning - Azure Database for PostgreSQL
告警和阈值设置
- PostgreSQL Alerts & Rules - ScaleGrid
- Best practices for alerting on metrics - Azure
- Postgres performance monitoring: Best practices - ManageEngine
- Configure alerts - Azure Database for PostgreSQL
- Everything You Need to Know to Start Monitoring Postgres - Last9
- Decoding PostgreSQL Monitoring Guide - SigNoz
系统级监控工具
- Dynamic Monitoring PostgreSQL Using pg_top - Severalnines
- Key Things to Monitor in PostgreSQL - Severalnines
- Performance Tuning PostgreSQL DB on Server - HostMyCode
- Monitoring PostgreSQL Disk I/O Performance - MinervaDB
- pgDash - Comprehensive PostgreSQL Monitoring
其他监控工具
- Monitoring PostgreSQL with Nagios and Checkmk - Highgo
- How to monitor a PostgreSQL replication - Claudia Kuenzler
- Monitoring Plugins - check_pgsql
- Compare Checkmk vs Zabbix - PeerSpot
- Understanding Monitoring Tools - Netdata
连接池监控
- Track health of PostgreSQL connection pooling with PgBouncer - Microsoft
- PostgreSQL Connection Pooling with PgBouncer - pgDash
- PostgreSQL Connection Pooling: PgBouncer Vs. Pgpool-II - ScaleGrid
- PgBouncer Monitoring Metrics - Instaclustr
- PgBouncer command-line usage
- PgBouncer in Azure Database for PostgreSQL - Microsoft
- Connection Pooling - CloudNativePG
- Monitor PgBouncer with Datadog - Aiven
- Connection pooling intro - PgBouncer and pgpool-II - Cybertec
- Improve database performance with connection pooling - Stack Overflow
云平台监控
- Managed PostgreSQL Comparison: AWS vs Google Cloud vs Azure - Hasura
- Amazon RDS for PostgreSQL - AWS
- Comparing AWS RDS to Google Cloud and Azure - N2W
- Monitoring managed cloud databases - pgwatch2
- Comparing Postgres Managed Services - PeerDB
- AWS vs Azure vs GCP vs Supabase - RisingWave
- Migrating PostgreSQL to the Cloud - Severalnines
- Database as a service roundup: AWS vs Azure vs Google Cloud - DevOps
APM 工具集成
- Comparison of Monitoring Features: DataDog, New Relic, Dynatrace - Medium
- Datadog vs. Dynatrace comparison - Better Stack
- Dynatrace vs AppDynamics vs New Relic
- New Connected Infrastructure & APM Experience - New Relic
- Cost Comparison for New Relic, Datadog, and Dynatrace
- New Relic Vs Appdynamics Vs Dynatrace - Coralogix
ELK Stack 集成
- Analyze PostgreSQL Statistics Using Elastic Stack - DigitalOcean
- PostgreSQL monitoring with ELK - GitHub
- Docker-ELK-PostgreSQL - GitHub
- Monitor PostgreSQL with the Elastic Stack - Speaker Deck
- Configuring and authoring Kibana dashboards - AWS
- Real-time monitoring with ELK Stack - Medium
- Local Development and Log Monitoring Using ELK Stack - Medium
- Kibana Dashboard - Elastic
- The Complete Guide to the ELK Stack - Logz.io
OpenTelemetry 和分布式追踪
- Monitor PostgreSQL metrics with OpenTelemetry - SigNoz
- OpenTelemetry PostgreSQL Monitoring - Uptrace
- Monitor PostgreSQL Performance via OpenTelemetry - OpenObserve
- How to Use OpenTelemetry with Postgres - Last9
- Monitor PostgreSQL with OpenTelemetry - Pradumn Saraf
- Monitoring PostgreSQL with OpenTelemetry - ServiceNow
- Monitoring Postgres with OpenTelemetry - Splunk
- Distributed Tracing for Postgres with OpenTelemetry - Coditation
- Performance and errors monitoring - Uptrace
- EDB Postgres Distributed OpenTelemetry integration
Kubernetes 和容器监控
- Setup PostgreSQL Monitoring in Kubernetes - Crunchy Data
- Kubernetes PostgreSQL Operator - Portworx
- CloudNativePG - PostgreSQL Operator for Kubernetes
- Percona Operator for PostgreSQL - Monitor Kubernetes
- Deploy Postgres via Kubernetes PostgreSQL Operator - KubeDB
- CrunchyData/postgres-operator - GitHub
- Choosing a Kubernetes Operator for PostgreSQL - Portworx
- Monitoring PostgreSQL Containers - Medium
- PostgreSQL Operator for Kubernetes and Prometheus monitoring
- Monitoring PostgreSQL Clusters in Kubernetes - Crunchy Data
CDC 和实时数据流
- Enabling CDC with Debezium PostgreSQL Connector - Confluent
- PostgreSQL CDC Source V2 Connector - Confluent
- Debezium connector for PostgreSQL
- Real-time CDC using Postgres, Debezium, and Redpanda
- Real-time CDC using PostgreSQL and Debezium - Medium
- Real Time Data Streaming with Debezium and CDC - Medium
- Monitoring Database Changes with Postgres and Debezium - Medium
- CDC Realtime Streaming with Postgres - Medium
- CDC-MySQL-Debezium-PostgreSQL - GitHub
中文资源
- 监控 PostgreSQL 数据库的 5 种方法 - TimescaleDB
- PostgreSQL 实战篇——监控性能及调优方法 - CSDN
- PostgreSQL 监控 - w3cschool
- PostgreSQL 教程: PostgreSQL 监控 - Redrock
- 一个能融会贯通PostgreSQL监控的人，大概率是高手 - 知乎
- 使用 Prometheus 和 Grafana 监控 PostgreSQL - Redrock
- postgresql数据库性能监控 - CSDN
- 如何监控PostgreSQL - Cloud Insight
- PostgreSQL 性能指南 - TimescaleDB
书籍和培训资源
- PostgreSQL: Tutorials & Other Resources
- PostgreSQL Tutorial - Neon
- PostgreSQL Tutorial - W3Schools
- PostgreSQL Documentation
- Top PostgreSQL Courses - Udemy
- Free EDB Postgres Training and Certification - EDB
- Learn PostgreSQL: Build and manage high-performance database - Amazon
- PostgreSQL: Books
GitHub Awesome 列表
- dhamaniasad/awesome-postgres - GitHub
- Awesome Postgres
- pg-tr/awesome-postgres - GitHub
- edib/awesome-postgres - GitHub
其他监控项目
- shadabshaukat/postgres-monitoring - GitHub
- DataDog/the-monitor PostgreSQL monitoring tools
- free/sql_exporter - Database agnostic SQL exporter
- databaseleague/percona-postgres_exporter
- Grafana Agent postgres.exporter component
专业博客和文章
- Top Postgres Monitoring Tools in 2024 - Bytebase
- Top PostgreSQL Monitoring Tools in 2025 - Uptrace
- Effective PostgreSQL Monitoring - Percona
- Effective PostgreSQL Monitoring pg_stat_all_tables - EDB
- PostgreSQL Reporting Tools PgBadger - DBAMaster
- PGBadger stands out - Baremon
- A detailed look at Pgwatch2 - Cybertec
- How to utilize pgBadger - EDB
- Best PostgreSQL Monitoring Tools - Sematext
- PostgreSQL monitoring pgwatch - Cybertec
- PGWatch: Optimized PostgreSQL monitoring
- Whats New in Postgres 10 Monitoring - pganalyze
- pgCenter, TOP like utility - FatDBA
- Postgres performance at any scale - pganalyze
- Monitor PostgreSQL Cluster using pgCenter - DBsGuru
- pgCenter download - SourceForge
- App to monitor PostgreSQL queries in real time - Stack Overflow
高级主题
- Data Observability in 2025 - Dagster
- PostgreSQL SolarWinds Monitoring
- How to efficiently vacuum analyze tables - Stack Overflow
- agapoff/check_kubernetes - Nagios/Icinga/Zabbix plugin
- Checkmk vs. Nagios XI vs. Zabbix Comparison
- Best monitoring solution for proxmox - Proxmox Forum
- Comparison to other monitoring tools - Zabbix Forums
- Aurora PostgreSQL Audit Log - DataSunrise
- Postgres Auditing - DataSunrise
- Audit logs with Cloud SQL and pgAudit - Google Cloud
- Using Google Cloud PostgreSQL as source for AWS DMS
- PostgreSQL CDC Source Legacy Connector - Confluent
- Pome - PostgreSQL Metrics Dashboard
- PMM - Percona Monitoring and Management
- pgmetrics - PostgreSQL metrics tool
- pg_view - PostgreSQL activity viewer
- opm.io - Open PostgreSQL Monitoring
- Metabase - Simple dashboards for PostgreSQL
- PoWA - PostgreSQL Workload Analyzer
- Top PostgreSQL Courses Online - Updated 2025
其他重要资源
- PostgreSQL Tutorial PDF
- PostgreSQL Tutorial - Part I
- Amazon RDS for PostgreSQL Pricing
- Monitor PostgreSQL Comparison to other tools
- How to setup effective PostgreSQL monitoring
学习目标
完成本章学习后，你将能够：
- 理解监控原理：掌握 PostgreSQL 监控的核心概念和层次架构
- 使用内置工具：熟练使用 pg_stat_* 视图和 pg_stat_statements 进行性能分析
- 搭建监控系统：能够独立搭建 Prometheus + Grafana 监控栈
- 配置告警规则：设计合理的告警策略和阈值
- 分析性能问题：使用监控数据定位和解决性能瓶颈
- 审计与合规：配置审计日志满足合规要求
- 容器化监控：在 Kubernetes 环境中部署和管理监控
- 优化监控策略：根据业务需求定制监控方案
小结
监控是保障 PostgreSQL 数据库稳定运行的关键环节。通过本章的学习，你将掌握从基础的内置监控工具到企业级监控平台的完整知识体系，能够构建适合自己业务需求的监控解决方案，及时发现和解决数据库性能问题，确保系统的高可用性和最佳性能。
最后更新于