扩展监控工具

PostgreSQL提供了丰富的扩展机制，允许通过安装额外的扩展包来增强数据库的监控能力。这些扩展工具能够提供更详细、更全面的性能指标，帮助DBA深入了解数据库的运行状态。

pg_stat_statements

pg_stat_statements是PostgreSQL最常用的性能监控扩展之一，用于跟踪SQL语句的执行统计信息。

安装与配置

PostgreSQL 12+版本

sql

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 在postgresql.conf中配置
shared_preload_libraries = 'pg_stat_statements'  -- 必须在shared_preload_libraries中加载
pg_stat_statements.track = all                 -- 跟踪所有语句（包括嵌套）
pg_stat_statements.max = 10000                 -- 最多跟踪10000条语句
pg_stat_statements.track_utility = on          -- 跟踪工具命令（如CREATE、DROP等）
pg_stat_statements.save = on                   -- 重启后保存统计信息

PostgreSQL 10-11版本

sql

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 在postgresql.conf中配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.track_utility = on
-- 注意：10-11版本不支持pg_stat_statements.save参数

常用查询

sql

-- 查看执行时间最长的10条SQL
SELECT queryid, query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 查看调用次数最多的SQL
SELECT queryid, query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- 查看平均执行时间最长的SQL
SELECT queryid, query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 重置统计信息
SELECT pg_stat_statements_reset();

pg_stat_kcache

pg_stat_kcache用于跟踪SQL语句的内核级缓存命中情况，包括数据块的读写统计。

安装与配置

安装依赖

bash

# 在Debian/Ubuntu系统上
apt-get install postgresql-15-pg-stat-kcache

# 在RedHat/CentOS系统上
yum install postgresql15-contrib postgresql15-devel

配置

sql

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;

-- 在postgresql.conf中配置
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'  -- 确保先加载依赖
pg_stat_kcache.track = all                                     -- 跟踪所有语句

常用查询

sql

-- 查看SQL语句的缓存命中率
SELECT query, calls, 
       shared_blks_hit, shared_blks_read, 
       (shared_blks_hit::float / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100 AS hit_rate
FROM pg_stat_kcache
ORDER BY hit_rate ASC
LIMIT 10;

-- 查看IO密集型SQL
SELECT query, calls, 
       shared_blks_read, shared_blks_written,
       temp_blks_read, temp_blks_written
FROM pg_stat_kcache
ORDER BY (shared_blks_read + shared_blks_written) DESC
LIMIT 10;

pg_buffercache

pg_buffercache用于查看PostgreSQL缓冲区缓存的使用情况，帮助DBA了解哪些表或索引被频繁访问。

安装与配置

sql

-- 安装扩展（无需预加载）
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

常用查询

sql

-- 查看缓冲区缓存中使用最多的表
SELECT c.relname, count(*) AS buffers
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())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

-- 查看缓冲区缓存命中率
SELECT 
  SUM(CASE WHEN usagecount > 0 THEN 1 ELSE 0 END) AS used_buffers,
  COUNT(*) AS total_buffers,
  (SUM(CASE WHEN usagecount > 0 THEN 1 ELSE 0 END)::float / COUNT(*)) * 100 AS cache_hit_rate
FROM pg_buffercache;

-- 查看特定表在缓冲区中的使用情况
SELECT 
  relname,
  SUM(CASE WHEN usagecount > 0 THEN 1 ELSE 0 END) AS cached_blocks,
  (pg_relation_size(relname) / current_setting('block_size')::int) AS total_blocks,
  (SUM(CASE WHEN usagecount > 0 THEN 1 ELSE 0 END)::float / (pg_relation_size(relname) / current_setting('block_size')::int)) * 100 AS cache_ratio
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
WHERE relname = 'your_table_name'
GROUP BY relname;

pg_stat_progress_vacuum

pg_stat_progress_vacuum用于监控VACUUM操作的进度，这对于大型表的VACUUM操作尤为重要。

安装与配置

从PostgreSQL 9.6开始，pg_stat_progress_vacuum是内置视图，无需安装扩展。

常用查询

sql

-- 查看正在进行的VACUUM操作
SELECT 
  pid,
  datname,
  relname,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum
JOIN pg_class ON pg_stat_progress_vacuum.relid = pg_class.oid;

-- 计算VACUUM进度百分比
SELECT 
  pid,
  relname,
  phase,
  CASE 
    WHEN heap_blks_total = 0 THEN 0
    ELSE (heap_blks_scanned::float / heap_blks_total) * 100 
  END AS heap_scan_progress,
  CASE 
    WHEN heap_blks_total = 0 THEN 0
    ELSE (heap_blks_vacuumed::float / heap_blks_total) * 100 
  END AS heap_vacuum_progress
FROM pg_stat_progress_vacuum
JOIN pg_class ON pg_stat_progress_vacuum.relid = pg_class.oid;

pg_stat_wal

pg_stat_wal用于监控WAL生成和写入情况，帮助DBA了解数据库的事务负载。

安装与配置

从PostgreSQL 13开始，pg_stat_wal是内置视图，无需安装扩展。

常用查询

sql

-- 查看WAL生成统计
SELECT 
  wal_records,
  wal_fpi,
  wal_bytes,
  stats_reset
FROM pg_stat_wal;

-- 计算WAL生成速率（需要两次查询的差值）
SELECT 
  wal_records - lag(wal_records) OVER (ORDER BY stats_reset) AS wal_records_diff,
  wal_bytes - lag(wal_bytes) OVER (ORDER BY stats_reset) AS wal_bytes_diff,
  extract(epoch FROM (stats_reset - lag(stats_reset) OVER (ORDER BY stats_reset))) AS seconds_diff,
  (wal_bytes - lag(wal_bytes) OVER (ORDER BY stats_reset)) / extract(epoch FROM (stats_reset - lag(stats_reset) OVER (ORDER BY stats_reset))) AS bytes_per_second
FROM pg_stat_wal;

auto_explain

auto_explain扩展用于自动记录慢查询的执行计划，无需手动执行EXPLAIN命令。

安装与配置

sql

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS auto_explain;

-- 在postgresql.conf中配置
shared_preload_libraries = 'auto_explain'  -- 必须在shared_preload_libraries中加载
auto_explain.log_min_duration = 500        -- 记录执行时间超过500ms的语句
auto_explain.log_analyze = true            -- 记录实际执行时间
auto_explain.log_buffers = true            -- 记录缓冲区使用情况
auto_explain.log_format = json             -- 使用JSON格式记录
auto_explain.log_nested_statements = true  -- 记录嵌套语句

查看自动记录的执行计划

自动记录的执行计划会出现在PostgreSQL的日志文件中，可以通过日志分析工具查看。

pg_stat_replication

pg_stat_replication用于监控流复制状态，对于主从架构的PostgreSQL集群至关重要。

安装与配置

pg_stat_replication是内置视图，无需安装扩展。

常用查询

sql

-- 查看复制状态
SELECT 
  application_name,
  client_addr,
  state,
  sync_state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

-- 计算复制延迟（秒）
SELECT 
  application_name,
  client_addr,
  state,
  sync_state,
  EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS replication_delay_seconds
FROM pg_stat_replication;

最佳实践

合理配置扩展参数：根据数据库规模和性能需求，调整各个扩展的配置参数，避免过度收集导致的性能开销。
定期清理统计信息：对于pg_stat_statements等扩展，定期清理旧的统计信息，保持统计数据的时效性。
结合监控平台使用：将扩展收集的指标集成到Prometheus、Grafana等监控平台，实现可视化监控和告警。
关注版本差异：不同PostgreSQL版本的扩展功能和配置可能存在差异，需要根据实际版本进行调整。
监控扩展本身的性能：某些扩展可能会带来一定的性能开销，需要监控扩展本身对数据库性能的影响。
定期更新扩展版本：及时更新扩展到最新版本，获取新功能和bug修复。

通过合理使用这些扩展监控工具，DBA可以全面了解PostgreSQL数据库的运行状态，及时发现和解决性能问题，确保数据库的稳定运行。

扩展监控工具 ​

pg_stat_statements ​

安装与配置 ​

PostgreSQL 12+版本 ​

PostgreSQL 10-11版本 ​

常用查询 ​

pg_stat_kcache ​

安装与配置 ​

安装依赖 ​

配置 ​

常用查询 ​

pg_buffercache ​

安装与配置 ​

常用查询 ​

pg_stat_progress_vacuum ​

安装与配置 ​

常用查询 ​

pg_stat_wal ​

安装与配置 ​

常用查询 ​

auto_explain ​

安装与配置 ​

查看自动记录的执行计划 ​

pg_stat_replication ​

安装与配置 ​

常用查询 ​

最佳实践 ​

扩展监控工具

pg_stat_statements

安装与配置

PostgreSQL 12+版本

PostgreSQL 10-11版本

常用查询

pg_stat_kcache

安装与配置

安装依赖

配置

常用查询

pg_buffercache

安装与配置

常用查询

pg_stat_progress_vacuum

安装与配置

常用查询

pg_stat_wal

安装与配置

常用查询

auto_explain

安装与配置

查看自动记录的执行计划

pg_stat_replication

安装与配置

常用查询

最佳实践