pgsql-35 监控与日志管理

2025-09-25 约 1438 字预计阅读 3 分钟

1. 概述

“没有监控的数据库是黑盒”。本篇讲 PostgreSQL 的可观测性：用内置统计视图看实时状态，用日志留存审计与诊断信息，用 Prometheus + Grafana 做长期监控告警。

对比 MySQL：MySQL 用 SHOW STATUS、performance_schema、information_schema 和 SHOW ENGINE INNODB STATUS；PostgreSQL 用一套 pg_stat_* 系统视图。理念相通——都是把内部计数器暴露为可查询的视图。

2. 核心统计视图

视图	用途
`pg_stat_activity`	当前连接/会话与正在执行的查询
`pg_stat_database`	每个库的提交/回滚/缓存命中/死锁
`pg_stat_user_tables`	表的读写次数、死元组、VACUUM 时间
`pg_stat_user_indexes`	索引使用次数
`pg_stat_replication`	复制状态与延迟
`pg_statio_user_tables`	表的物理/缓存 IO
`pg_stat_statements`	SQL 级统计（需扩展）

3. 关键监控指标与查询

3.1 连接数与状态

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- 关注 active / idle / 'idle in transaction'（后者危险，见连接池篇）

-- 距离 max_connections 还剩多少
SELECT count(*) AS 当前, current_setting('max_connections') AS 上限 FROM pg_stat_activity;

3.2 缓存命中率（应 >99%）

SELECT datname,
       round(blks_hit*100.0 / nullif(blks_hit+blks_read,0), 2) AS 缓存命中率
FROM pg_stat_database WHERE datname = current_database();
-- 命中率低说明 shared_buffers 不足或有大量冷数据扫描

3.3 事务与死锁

SELECT datname, xact_commit, xact_rollback, deadlocks, temp_files, temp_bytes
FROM pg_stat_database WHERE datname = current_database();
-- deadlocks 增长、temp_files 多(work_mem不足) 都是告警点

3.4 表膨胀与 VACUUM

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;
-- 死元组多、长期没 autovacuum 需关注（见 VACUUM 篇）

3.5 长事务/长查询

SELECT pid, now()-xact_start AS 事务时长, state, query
FROM pg_stat_activity
WHERE state <> 'idle' AND now()-xact_start > interval '1 min'
ORDER BY 事务时长 DESC;

3.6 复制延迟

SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS 延迟字节
FROM pg_stat_replication;

3.7 数据库/表大小

SELECT pg_size_pretty(pg_database_size(current_database())) AS 库大小;
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS 大小
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

4. 日志管理

# postgresql.conf 常用日志配置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB

log_min_duration_statement = 1000     # 慢查询阈值(ms)
log_line_prefix = '%m [%p] %u@%d %r ' # 时间 进程 用户@库 客户端
log_checkpoints = on                  # 记录检查点
log_connections = on                  # 记录连接建立
log_disconnections = on
log_lock_waits = on                   # 记录锁等待(排查阻塞)
log_temp_files = 0                    # 记录临时文件使用(work_mem不足信号)
log_autovacuum_min_duration = 0       # 记录autovacuum活动

审计需求：用 pgAudit 扩展做细粒度操作审计（谁在何时执行了什么）。
日志可接入 ELK / Loki 做集中检索。

5. Prometheus + Grafana 监控栈

生产标配的可视化监控方案：

# postgres_exporter 采集 PG 指标暴露给 Prometheus
docker run -d -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://monitor:pwd@db:5432/postgres?sslmode=disable" \
  quay.io/prometheuscommunity/postgres-exporter

postgres_exporter 把 pg_stat_* 转成 Prometheus 指标。
Prometheus 抓取并存储，Grafana 用现成的 PostgreSQL 仪表盘展示。
配合 Alertmanager 对"连接数逼近上限、复制延迟过大、缓存命中率下降、磁盘不足、长事务"等设告警。

6. 需要建立告警的关键指标

连接数接近 max_connections。
缓存命中率 < 99%。
复制延迟过大 / 复制中断。
idle in transaction 长事务堆积。
死元组比例高 / autovacuum 长期未运行。
磁盘剩余空间 / WAL 目录膨胀。
死锁数增长、临时文件频繁（work_mem 不足）。
XID 回卷风险（age(datfrozenxid) 接近阈值）。

7. 面试高频问答

Q1：PostgreSQL 靠什么做监控？ 一套 pg_stat_* 系统视图暴露内部计数器（活动会话、数据库统计、表/索引使用、复制状态等），配合 pg_stat_statements 做 SQL 级统计，外部用 postgres_exporter + Prometheus + Grafana 做可视化和告警。

Q2：缓存命中率怎么算？多少算健康？ blks_hit / (blks_hit + blks_read)，来自 pg_stat_database，健康值应 >99%；偏低说明 shared_buffers 不足或大量冷数据扫描。

Q3：怎么发现长事务和阻塞？ 查 pg_stat_activity：按 now()-xact_start 找长事务，state='idle in transaction' 找泄漏，pg_blocking_pids() 找阻塞源。

Q4：哪些指标必须告警？ 连接数逼近上限、复制延迟/中断、缓存命中率下降、长事务堆积、死元组过多、磁盘/WAL 膨胀、XID 回卷风险。

Q5：需要审计谁改了数据怎么做？ 开启 log_connections/log_statement 记录基本操作，或用 pgAudit 扩展做细粒度审计，日志集中到 ELK/Loki 检索。

8. 下一步

学习用户权限与安全，掌握访问控制。

目录