PostgreSQL数据库监控：15个核心指标与实施策略-育师

1. PostgreSQL数据库监控的重要性

作为一名长期与PostgreSQL打交道的DBA，我深刻体会到监控是数据库管理的生命线。PostgreSQL作为企业级开源数据库，虽然以稳定可靠著称，但缺乏有效监控的PG实例就像没有仪表盘的赛车——你永远不知道什么时候会撞墙。

数据库监控的核心价值在于三个方面：首先是预防性维护，通过关键指标趋势预测潜在问题；其次是性能优化，识别瓶颈并针对性调优；最后是故障快速定位，当问题发生时能第一时间找到根因。根据我的经验，完善的监控体系可以减少80%的突发故障和70%的性能问题。

2. 必须监控的15个核心指标

2.1 连接与会话指标

连接池使用率是首要监控项。通过以下SQL可以获取关键数据：

SELECT max_conn, used, (used::float/max_conn)*100 AS percent_used FROM (SELECT setting::int AS max_conn FROM pg_settings WHERE name='max_connections') AS max_conn, (SELECT count(*) AS used FROM pg_stat_activity) AS used;

警告：当使用率超过80%就需要立即处理，否则可能导致应用无法连接。我曾遇到过一个电商系统在大促时因连接耗尽导致服务不可用。

会话状态分布同样重要：

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

重点关注：

idle in transaction：长事务会阻塞vacuum
active：高并发时可能预示性能问题
idle：合理数量反映连接池配置

2.2 查询性能指标

慢查询是性能杀手，必须严控。建议设置log_min_duration_statement=100ms并分析日志。也可以通过pg_stat_statements实时监控：

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

临时文件使用量反映内存配置是否合理：

SELECT datname, temp_files, temp_bytes FROM pg_stat_database;

经验：temp_files突然增加往往说明work_mem需要调整，我曾通过增加work_mem使ETL作业性能提升3倍。

2.3 复制与高可用指标

主从延迟是复制监控的核心：

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag FROM pg_stat_replication;

复制槽积压需要特别关注：

SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS bytes_lag FROM pg_replication_slots;

去年我们曾因未监控复制槽导致主库WAL堆积耗尽磁盘空间。

2.4 存储与清理指标

表膨胀率监控脚本：

SELECT schemaname, relname, n_dead_tup, n_live_tup, (n_dead_tup::float/(n_dead_tup+n_live_tup)) AS dead_ratio FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_ratio DESC LIMIT 10;

关键阈值：当dead_ratio>0.2就需要考虑手动vacuum或调整autovacuum参数

WAL目录大小监控：

du -sh $PGDATA/pg_wal

2.5 系统资源指标

检查点性能指标：

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean FROM pg_stat_bgwriter;

缓冲区命中率反映内存效率：

SELECT sum(blks_hit)*100/sum(blks_hit+blks_read) AS hit_ratio FROM pg_stat_database;

3. 监控系统实施策略

3.1 工具选型建议

Prometheus+Granafa方案：

postgres_exporter采集指标

告警规则示例：

- alert: HighDeadTuplesRatio expr: pg_stat_user_tables_dead_tup_ratio > 0.3 for: 1h labels: severity: warning annotations: summary: "High dead tuple ratio on {{ $labels.table }}"

商业方案推荐：

Percona Monitoring and Management
SolarWinds Database Performance Analyzer

3.2 监控频率建议

实时监控（15s间隔）：

连接数
活跃查询
锁等待

小时级监控：

表膨胀率
索引使用率
复制延迟

天级监控：

存储增长趋势
统计信息准确性
配置合规检查

4. 典型问题排查案例

4.1 连接泄漏排查

症状：连接数缓慢增长直至耗尽排查步骤：

查询pg_stat_activity找空闲连接
检查应用连接池配置
分析应用连接生命周期管理

SELECT client_addr, application_name, backend_start FROM pg_stat_activity WHERE state='idle' ORDER BY backend_start;

4.2 性能突降分析

某次线上事故排查记录：

首先检查CPU、IO等系统指标
发现IO等待高
查询pg_stat_activity发现大量等待锁
最终定位到未提交的长事务

SELECT pid, usename, query_start, query FROM pg_stat_activity WHERE wait_event_type='Lock' ORDER BY query_start;

5. 高级监控技巧

5.1 自定义监控指标

扩展统计信息收集：

CREATE STATISTICS transaction_stats (dependencies) ON transaction_status, customer_id FROM transactions;

跟踪锁等待链：

WITH lock_chains AS ( SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED ) SELECT * FROM lock_chains;

5.2 预测性监控

使用pg_statsinfo建立基线：

SELECT * FROM statsrepo.get_snapshot();

趋势预测查询：

WITH growth AS ( SELECT datname, stats_reset, pg_database_size(datname) AS size, age(now(), stats_reset) AS age FROM pg_stat_database ) SELECT datname, size/(extract(epoch FROM age)/86400) AS bytes_per_day FROM growth;