外观
PostgreSQL 监控扩展
概述
PostgreSQL 提供了丰富的内置监控功能，但在实际生产环境中，有时需要更详细和全面的监控信息来满足复杂的运维需求。PostgreSQL 的扩展机制允许用户安装和使用各种监控扩展，以增强数据库的监控能力。
本文将介绍 PostgreSQL 中常用的监控扩展，包括它们的安装、配置、使用方法和最佳实践，帮助 DBA 和开发人员在实际工作中合理选择和使用这些扩展，提高数据库监控的效率和质量。
常用监控扩展
pg_stat_statements - SQL性能分析
什么是 pg_stat_statements
pg_stat_statements 是 PostgreSQL 提供的一个 SQL 性能分析扩展，可以跟踪和统计数据库中执行的 SQL 语句的执行频率、执行时间、资源消耗等信息，帮助 DBA 识别和优化慢查询。
版本支持
- PostgreSQL 9.1+ 内置支持
- PostgreSQL 12+ 新增
wal_bytes字段，用于跟踪 WAL 生成量 - PostgreSQL 13+ 新增
mean_time字段，用于直接查看平均执行时间
安装方法
sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;配置参数
在 postgresql.conf 中添加以下配置：
ini
# 启用 pg_stat_statements 扩展
shared_preload_libraries = 'pg_stat_statements'
# 配置 pg_stat_statements
pg_stat_statements.max = 10000 # 最多保存多少条 SQL 语句，生产建议 10000-50000
pg_stat_statements.track = all # 跟踪所有 SQL 语句
pg_stat_statements.track_utility = on # 跟踪工具命令
pg_stat_statements.save = on # 重启后保存统计信息
pg_stat_statements.track_planning = on # PostgreSQL 14+，跟踪计划时间配置后需要重启 PostgreSQL 服务使其生效。
生产环境最佳实践
sql
-- 查看执行时间最长的 SQL 语句，包含版本兼容处理
SELECT queryid, query, calls,
total_time,
COALESCE(mean_time, total_time / NULLIF(calls, 0)) AS mean_time,
rows,
CASE WHEN EXISTS (SELECT 1 FROM pg_stat_statements WHERE wal_bytes IS NOT NULL)
THEN wal_bytes ELSE 0 END AS wal_bytes
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- 查看资源消耗最高的 SQL 语句（结合 buffer 读写）
SELECT queryid, query, calls,
shared_blks_hit + shared_blks_read AS total_shared_blks,
temp_blks_read + temp_blks_written AS total_temp_blks
FROM pg_stat_statements
ORDER BY (shared_blks_read + temp_blks_read + temp_blks_written) DESC
LIMIT 10;
-- 定期重置统计信息（建议每周一次，记录快照后重置）
SELECT pg_stat_statements_reset();性能影响
- 启用后会增加约 1-5% 的性能开销，具体取决于 SQL 执行频率
- 建议在生产环境中启用，但调整
pg_stat_statements.max避免内存占用过高
pg_stat_kcache - 操作系统资源使用统计
什么是 pg_stat_kcache
pg_stat_kcache 是一个用于统计 SQL 语句操作系统资源使用情况的扩展，可以跟踪和统计 SQL 语句的 CPU 使用率、磁盘 I/O 等信息，帮助 DBA 了解 SQL 语句对系统资源的消耗情况。
版本支持
- 兼容 PostgreSQL 9.6+ 版本
- 需要单独编译安装，或通过包管理器安装
安装方法
bash
# Ubuntu/Debian
apt-get install postgresql-14-pg-stat-kcache
# CentOS/RHEL
yum install postgresql14-contrib postgresql14-pg-stat-kcachesql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;配置参数
在 postgresql.conf 中添加以下配置：
ini
# 启用 pg_stat_kcache 扩展
shared_preload_libraries = 'pg_stat_kcache,pg_stat_statements'
# 配置 pg_stat_kcache
pg_stat_kcache.track = all # 跟踪所有 SQL 语句
pg_stat_kcache.track_utility = on # 跟踪工具命令
pg_stat_kcache.show_system = off # 不显示系统查询生产环境使用示例
sql
-- 查看 SQL 语句的系统资源使用情况
SELECT queryid, query, calls, total_time, mean_time,
shared_blks_hit, shared_blks_read,
shared_blks_dirtied, shared_blks_written,
temp_blks_read, temp_blks_written,
blk_read_time, blk_write_time,
cpu_user_time, cpu_sys_time
FROM pg_stat_statements s
JOIN pg_stat_kcache c ON s.queryid = c.queryid
ORDER BY (cpu_user_time + cpu_sys_time) DESC
LIMIT 10;
-- 计算 CPU 使用率最高的 SQL 语句
SELECT query, calls,
ROUND((cpu_user_time + cpu_sys_time) * 100 / total_time, 2) AS cpu_percent
FROM pg_stat_statements s
JOIN pg_stat_kcache c ON s.queryid = c.queryid
WHERE total_time > 0
ORDER BY cpu_percent DESC
LIMIT 5;pg_stat_progress - 长查询进度监控
什么是 pg_stat_progress
pg_stat_progress 是 PostgreSQL 内置的一个监控扩展，用于监控长查询的执行进度，包括 VACUUM、ANALYZE、CREATE INDEX、CLUSTER 等操作的进度信息。
版本支持
- PostgreSQL 9.6+：支持 VACUUM、ANALYZE、CREATE INDEX
- PostgreSQL 10+：支持 CLUSTER
- PostgreSQL 11+：支持 COPY
- PostgreSQL 12+：支持 pg_repack（通过扩展）
- PostgreSQL 14+：支持 CREATE MATERIALIZED VIEW
生产环境应用
sql
-- 查看所有正在执行的长操作进度
SELECT
p.pid,
p.query,
p.state,
p.backend_type,
-- VACUUM 进度
CASE
WHEN pr.relid IS NOT NULL THEN
FORMAT('VACUUM: %s%%',
ROUND(CASE WHEN pr.total_work > 0 THEN (pr.heap_blks_scanned + pr.index_vacuum_count) * 100.0 / pr.total_work ELSE 0 END, 1))
-- CREATE INDEX 进度
WHEN pc.relid IS NOT NULL THEN
FORMAT('CREATE INDEX: %s%%',
ROUND(CASE WHEN pc.phase = 'building index' AND pc.blocks_total > 0 THEN pc.blocks_done * 100.0 / pc.blocks_total ELSE 0 END, 1))
-- ANALYZE 进度
WHEN pa.relid IS NOT NULL THEN
FORMAT('ANALYZE: %s%%',
ROUND(CASE WHEN pa.total_blocks > 0 THEN pa.blocks_done * 100.0 / pa.total_blocks ELSE 0 END, 1))
-- COPY 进度
WHEN pco.relid IS NOT NULL THEN
FORMAT('COPY: %s%%',
ROUND(CASE WHEN pco.file_size > 0 THEN pco.bytes_processed * 100.0 / pco.file_size ELSE 0 END, 1))
ELSE 'No progress data'
END AS progress
FROM pg_stat_activity p
LEFT JOIN pg_stat_progress_vacuum pr ON p.pid = pr.pid
LEFT JOIN pg_stat_progress_create_index pc ON p.pid = pc.pid
LEFT JOIN pg_stat_progress_analyze pa ON p.pid = pa.pid
LEFT JOIN pg_stat_progress_copy pco ON p.pid = pco.pid
WHERE p.state = 'active'
AND (pr.relid IS NOT NULL OR pc.relid IS NOT NULL OR pa.relid IS NOT NULL OR pco.relid IS NOT NULL)
ORDER BY p.query_start;pg_wait_sampling - 等待事件采样
什么是 pg_wait_sampling
pg_wait_sampling 是一个用于采样和分析 PostgreSQL 等待事件的扩展，可以定期采样数据库进程的等待状态，帮助 DBA 了解数据库的等待情况，识别性能瓶颈。
版本支持
- 兼容 PostgreSQL 10+ 版本
- 需要单独编译安装
安装方法
bash
# 从源码编译安装
git clone https://github.com/postgrespro/pg_wait_sampling.git
cd pg_wait_sampling
make && make installsql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_wait_sampling;配置参数
ini
# 启用 pg_wait_sampling 扩展
shared_preload_libraries = 'pg_wait_sampling'
# 配置 pg_wait_sampling
pg_wait_sampling.history_size = 5000 # 保存多少条历史记录，生产建议 10000
pg_wait_sampling.sample_rate = 10 # 采样频率（毫秒），生产建议 5-10
pg_wait_sampling.sample_period = 1000 # 采样周期（毫秒）生产环境监控示例
sql
-- 查看等待事件统计，按类型分组
SELECT wait_event_type, wait_event,
count(*) AS sample_count,
ROUND(avg(duration)::numeric, 2) AS avg_duration_ms,
ROUND(max(duration)::numeric, 2) AS max_duration_ms
FROM pg_wait_sampling_history
WHERE ts > NOW() - INTERVAL '1 hour'
GROUP BY wait_event_type, wait_event
ORDER BY sample_count DESC
LIMIT 10;
-- 查看具体等待事件的时间分布
SELECT wait_event,
COUNT(CASE WHEN duration < 1 THEN 1 END) AS less_than_1ms,
COUNT(CASE WHEN duration >= 1 AND duration < 10 THEN 1 END) AS ms_1_10,
COUNT(CASE WHEN duration >= 10 AND duration < 100 THEN 1 END) AS ms_10_100,
COUNT(CASE WHEN duration >= 100 THEN 1 END) AS more_than_100ms
FROM pg_wait_sampling_history
WHERE ts > NOW() - INTERVAL '30 minutes'
GROUP BY wait_event
ORDER BY (COUNT(CASE WHEN duration >= 10 THEN 1 END)) DESC;pg_stat_monitor - 增强型SQL监控
什么是 pg_stat_monitor
pg_stat_monitor 是一个增强型的 SQL 监控扩展，基于 pg_stat_statements 开发，提供了更丰富的监控信息和更灵活的查询方式，包括按时间分组、按数据库分组、按用户分组等功能。
版本支持
- 兼容 PostgreSQL 11+ 版本
- 由 Percona 维护，定期更新
安装方法
bash
# Percona 仓库安装
apt-get install percona-pg-stat-monitor15sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;生产环境优势
- 自动按时间窗口分组，便于分析性能变化趋势
- 支持查询执行计划捕获
- 提供直方图统计，便于分析执行时间分布
- 支持按数据库、用户、应用程序分组统计
sql
-- 查看最近 1 小时的性能变化趋势
SELECT bucket_id,
min(ts) AS window_start,
max(ts) AS window_end,
query,
sum(calls) AS total_calls,
sum(total_time) AS total_time
FROM pg_stat_monitor
WHERE ts > NOW() - INTERVAL '1 hour'
GROUP BY bucket_id, query
ORDER BY bucket_id, total_time DESC;
-- 查看执行计划异常的 SQL 语句
SELECT query, planid, execution_plan
FROM pg_stat_monitor
WHERE execution_plan IS NOT NULL
AND calls > 10
LIMIT 5;pg_buffercache - 缓冲区缓存监控
什么是 pg_buffercache
pg_buffercache 是 PostgreSQL 提供的一个缓冲区缓存监控扩展，可以查看 PostgreSQL 缓冲区缓存的使用情况，包括哪些页面在缓存中、缓存命中率等信息。
版本支持
- PostgreSQL 8.4+ 内置支持
生产环境监控示例
sql
-- 查看缓冲区缓存使用情况，按表分组
SELECT
c.relname,
n.nspname,
count(*) AS buffers,
round(100.0 * count(*) / (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers')::int, 2) AS buffer_percent
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
AND b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY c.relname, n.nspname
ORDER BY buffers DESC
LIMIT 10;
-- 计算表的缓存命中率
SELECT
relname,
round(100.0 * sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read), 2) AS cache_hit_rate
FROM pg_statio_user_tables
WHERE (heap_blks_hit + heap_blks_read) > 0
GROUP BY relname
ORDER BY cache_hit_rate ASC
LIMIT 5;pg_stat_replication - 复制状态监控
什么是 pg_stat_replication
pg_stat_replication 是 PostgreSQL 内置的一个复制状态监控扩展，用于监控主从复制的状态，包括复制延迟、复制槽状态、WAL 发送和接收情况等信息。
版本支持
- PostgreSQL 9.0+：基础复制监控
- PostgreSQL 9.4+：支持复制槽
- PostgreSQL 10+：支持
write_lag、flush_lag、replay_lag字段 - PostgreSQL 12+：支持
sync_priority、sync_state字段
生产环境复制监控
sql
-- 查看复制状态，包含版本兼容的延迟计算
SELECT
application_name,
client_addr,
state,
sync_state,
-- 版本兼容的延迟计算
CASE
WHEN EXISTS (SELECT 1 FROM pg_stat_replication WHERE write_lag IS NOT NULL)
THEN write_lag ELSE
CASE WHEN sent_lsn = replay_lsn THEN '00:00:00'::interval
ELSE NULL END
END AS write_lag,
CASE
WHEN EXISTS (SELECT 1 FROM pg_stat_replication WHERE replay_lag IS NOT NULL)
THEN replay_lag ELSE
CASE WHEN sent_lsn = replay_lsn THEN '00:00:00'::interval
ELSE NULL END
END AS replay_lag,
-- 计算具体的 LSN 差距（字节）
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lsn_diff_bytes
FROM pg_stat_replication;
-- 检查复制槽状态
SELECT
slot_name,
plugin,
slot_type,
active,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS wal_pending_bytes,
confirmed_flush_lsn
FROM pg_replication_slots
ORDER BY wal_pending_bytes DESC;监控扩展选择和部署建议
基础监控组合（推荐所有生产环境）
- pg_stat_statements：必须启用，SQL 性能分析基础
- pg_stat_progress：内置，无需额外安装，监控长操作进度
- pg_buffercache：内置，监控缓存使用情况
高级监控组合（建议中大型环境）
- 增加 pg_stat_kcache：系统资源消耗分析
- 增加 pg_wait_sampling：等待事件分析
- 增加 pg_stat_replication：复制环境必备
企业级监控组合
- 增加 pg_stat_monitor：增强型 SQL 监控
- 结合 Prometheus + Grafana：可视化监控
- 配置告警规则：基于关键指标设置阈值告警
性能影响评估
| 扩展名称 | 性能影响 | 建议配置 |
|---|---|---|
| pg_stat_statements | 低（1-5%） | 生产必开 |
| pg_stat_kcache | 低（2-3%） | 建议开启 |
| pg_wait_sampling | 极低（<1%） | 建议开启 |
| pg_stat_monitor | 中（3-7%） | 大型环境考虑 |
| pg_buffercache | 极低（<0.5%） | 按需使用 |
监控扩展维护最佳实践
定期维护任务
每周：
- 备份 pg_stat_statements 快照
- 重置统计信息
- 检查扩展版本更新
每月：
- 分析监控数据，识别长期性能趋势
- 调整监控配置参数
- 清理不再使用的扩展
每季度：
- 评估监控扩展的必要性
- 测试新版本扩展兼容性
- 更新监控仪表盘
安全配置
- 限制监控扩展的访问权限：仅授予 DBA 角色
- 对于 pg_stat_monitor 等包含敏感信息的扩展，启用加密存储
- 定期审计监控数据的访问日志
版本升级注意事项
- 升级 PostgreSQL 前，检查所有扩展的兼容性
- 升级后，重新编译或安装扩展
- 测试扩展功能正常后，再开放生产使用
总结
PostgreSQL 的监控扩展提供了丰富的功能，可以满足各种复杂的监控需求。DBA 应该根据实际情况选择合适的扩展组合，并遵循最佳实践进行安装、配置和使用。
在生产环境中，建议从基础监控组合开始，逐步根据需求扩展监控能力。同时，要注意监控扩展的性能影响，定期进行维护和优化，确保监控系统本身不会成为性能瓶颈。
通过合理使用监控扩展，可以显著提高 PostgreSQL 数据库的监控效率和质量，及时发现和解决性能问题，确保数据库的稳定运行。