PostgreSQL 常用诊断工具

诊断工具概述

PostgreSQL提供了丰富的诊断工具，包括内置工具、第三方工具和扩展工具。这些工具可以帮助DBA进行性能监控、故障诊断、查询优化和日常运维。

内置诊断工具

1. psql命令行工具

psql是PostgreSQL的交互式命令行工具，提供了丰富的诊断命令。

常用命令

bash

# 连接数据库
psql -h hostname -p port -U username -d database

# 查看帮助
\?

# 查看数据库列表
\l

# 查看表列表
\dt
\dt+  # 包含大小信息

# 查看索引列表
\di
\di+  # 包含大小信息

# 查看视图列表
\dv

# 查看函数列表
\df

# 查看序列列表
\ds

# 查看表结构
\d table_name
\d+ table_name  # 包含注释和统计信息

# 查看索引结构
\d index_name

# 查看数据库大小
\l+

# 查看表大小
\dt+ table_name

# 查看索引大小
\di+ index_name

# 查看会话列表
\x  # 开启扩展输出
SELECT * FROM pg_stat_activity;

# 查看锁信息
SELECT * FROM pg_locks;

# 查看执行计划
EXPLAIN SELECT * FROM table_name WHERE column = 'value';
EXPLAIN ANALYZE SELECT * FROM table_name WHERE column = 'value';

# 查看查询统计
\timing  # 开启执行时间统计
SELECT * FROM table_name;

2. pg_controldata

查看数据库控制信息，包括数据库版本、检查点信息、WAL信息等。

bash

# 查看控制信息
pg_controldata /var/lib/postgresql/14/main

# 输出到文件
pg_controldata /var/lib/postgresql/14/main > controldata.txt

3. pg_waldump

解析WAL日志内容，用于诊断WAL相关问题。

bash

# 查看WAL文件内容
pg_waldump pg_wal/000000010000000000000001

# 按事务ID过滤
pg_waldump --transaction=12345 pg_wal/000000010000000000000001

# 按记录类型过滤
pg_waldump --filter="INSERT" pg_wal/000000010000000000000001

# 输出到文件
pg_waldump pg_wal/000000010000000000000001 > waldump.txt

4. pg_basebackup

创建数据库基础备份，同时可以用于诊断数据库状态。

bash

# 创建基础备份
pg_basebackup -D /path/to/backup -h hostname -p port -U replication -c fast -Fp -Xs -v -P

# 检查备份完整性
pg_controldata /path/to/backup

5. pg_dump和pg_restore

用于备份和恢复数据库，同时可以用于诊断数据库结构和数据完整性。

bash

# 备份数据库结构
pg_dump -h hostname -p port -U username -d database -s > schema.sql

# 备份数据库数据
pg_dump -h hostname -p port -U username -d database -a > data.sql

# 备份整个数据库
pg_dump -h hostname -p port -U username -d database -f backup.sql

# 压缩备份
pg_dump -h hostname -p port -U username -d database | gzip > backup.sql.gz

# 恢复数据库
pg_restore -h hostname -p port -U username -d database backup.sql

6. pg_resetwal/pg_resetxlog

重置WAL日志，用于修复WAL损坏问题。

bash

# PostgreSQL 10+ 使用pg_resetwal
pg_resetwal -D /var/lib/postgresql/14/main -f

# PostgreSQL 9.x 使用pg_resetxlog
pg_resetxlog -D /var/lib/postgresql/9.6/main -f

7. pg_isready

检查PostgreSQL实例是否正常运行。

bash

# 检查本地实例
pg_isready

# 检查远程实例
pg_isready -h hostname -p port

8. pg_test_fsync

测试文件系统的fsync性能，用于优化WAL相关参数。

bash

pg_test_fsync

# 测试更多选项
pg_test_fsync -f 1000 -s 200

9. pg_test_timing

测试系统时钟精度，用于优化某些PostgreSQL参数。

bash

pg_test_timing

# 测试指定时长
pg_test_timing -d 10

10. pg_receivewal

接收WAL日志，用于测试复制功能和诊断复制问题。

bash

# 接收WAL日志到文件
pg_receivewal -h hostname -p port -U replication -D /path/to/wal -v

第三方诊断工具

1. pgAdmin

pgAdmin是PostgreSQL的图形化管理工具，提供了丰富的诊断功能。

主要功能：

图形化的数据库管理界面
可视化的执行计划分析
实时监控数据库状态
慢查询分析
备份和恢复管理
用户和权限管理

使用方法：下载并安装pgAdmin，连接到PostgreSQL实例后，通过界面进行各种诊断操作。

2. pgBadger

pgBadger是一个快速的PostgreSQL日志分析工具，生成HTML格式的报告。

主要功能：

错误日志分析
慢查询分析
连接统计
锁等待分析
WAL日志分析
复制延迟分析

安装方法：

bash

# 使用apt安装（Debian/Ubuntu）
apt-get install pgbadger

# 使用yum安装（CentOS/RHEL）
yum install pgbadger

# 从源码安装
wget https://github.com/darold/pgbadger/archive/v12.0.tar.gz
tar zxvf v12.0.tar.gz
cd pgbadger-12.0
perl Makefile.PL
make install

使用方法：

bash

# 分析错误日志
pgbadger -o report.html postgresql-2023-01-01_120000.log

# 分析多个日志文件
pgbadger -o report.html postgresql-2023-01-0*.log

# 分析慢查询日志
pgbadger -o slow_query_report.html --slowlog postgresql-slow.log

# 实时分析日志
pgbadger -o - -d 60 postgresql.log | tee realtime_report.html

3. pg_top

pg_top是一个实时监控PostgreSQL进程的工具，类似于top命令。

主要功能：

实时监控PostgreSQL进程
查看查询执行时间
查看资源消耗情况
查看锁等待

安装方法：

bash

# 使用apt安装（Debian/Ubuntu）
apt-get install pgtop

# 使用yum安装（CentOS/RHEL）
yum install pgtop

使用方法：

bash

# 连接数据库并启动监控
pg_top -h hostname -p port -U username -d database

4. pg_stat_monitor

pg_stat_monitor是Percona开发的增强版pg_stat_statements，提供更详细的查询统计信息。

主要功能：

更详细的查询执行统计
按时间分组的统计信息
支持查询计划存储
支持自定义监控窗口

安装方法：

bash

# 从源码编译安装
git clone https://github.com/percona/pg_stat_monitor.git
cd pg_stat_monitor
make USE_PGXS=1
make USE_PGXS=1 install

使用方法：

sql

-- 启用扩展
CREATE EXTENSION pg_stat_monitor;

-- 查看配置参数
SHOW pg_stat_monitor.pgsm_max;
SHOW pg_stat_monitor.pgsm_query_max_len;

-- 查看查询统计
SELECT * FROM pg_stat_monitor ORDER BY total_time DESC LIMIT 10;

5. Prometheus + Grafana

Prometheus是一个开源的监控系统，Grafana是一个开源的数据可视化工具，两者结合可以用于监控PostgreSQL。

主要功能：

实时监控PostgreSQL性能指标
可视化的仪表盘
灵活的告警规则
历史数据查询

使用方法：

安装Prometheus
安装PostgreSQL Exporter
配置Prometheus采集PostgreSQL指标
安装Grafana
配置Grafana连接Prometheus
导入PostgreSQL仪表盘模板

6. Zabbix

Zabbix是一个企业级的监控系统，支持监控PostgreSQL。

主要功能：

全面的监控指标
灵活的告警规则
自动发现功能
分布式监控

使用方法：

安装Zabbix Server
安装Zabbix Agent
配置PostgreSQL监控模板
添加PostgreSQL主机到Zabbix

PostgreSQL扩展诊断工具

1. pg_stat_statements

pg_stat_statements是PostgreSQL的内置扩展，用于统计查询执行情况。

主要功能：

统计查询执行次数
统计查询执行时间
统计查询资源消耗
支持按多种维度排序

安装方法：

sql

-- 在postgresql.conf中添加
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 重启数据库后创建扩展
CREATE EXTENSION pg_stat_statements;

使用方法：

sql

-- 查看查询统计
SELECT queryid, query, calls, total_exec_time, mean_exec_time, 
       min_exec_time, max_exec_time, rows
FROM pg_stat_statements 
ORDER BY total_exec_time DESC
LIMIT 10;

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

2. pgAudit

pgAudit是一个审计日志扩展，用于记录详细的用户操作。

主要功能：

记录DDL操作
记录DML操作
记录SELECT操作
支持细粒度的审计规则

安装方法：

sql

-- 在postgresql.conf中添加
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, write'

-- 重启数据库后创建扩展
CREATE EXTENSION pgaudit;

使用方法：

sql

-- 查看审计日志配置
SHOW pgaudit.log;

-- 修改审计日志配置
ALTER SYSTEM SET pgaudit.log = 'ddl, write, read';
SELECT pg_reload_conf();

3. pg_prewarm

pg_prewarm用于将数据加载到内存中，用于性能优化和诊断。

主要功能：

将表数据加载到内存
将索引数据加载到内存
支持多种加载方式

安装方法：

sql

CREATE EXTENSION pg_prewarm;

使用方法：

sql

-- 将表数据加载到内存
SELECT pg_prewarm('table_name');

-- 将索引数据加载到内存
SELECT pg_prewarm('index_name');

-- 使用特定方式加载
SELECT pg_prewarm('table_name', 'prefetch');
SELECT pg_prewarm('table_name', 'read');

4. pg_repack

pg_repack用于在线重组表和索引，用于解决表膨胀问题。

主要功能：

在线重组表，无需锁表
重组索引
减少表膨胀
支持只重组特定索引

安装方法：

bash

# 使用apt安装（Debian/Ubuntu）
apt-get install postgresql-14-repack

# 使用yum安装（CentOS/RHEL）
yum install postgresql14-repack

# 从源码编译安装
git clone https://github.com/reorg/pg_repack.git
cd pg_repack
make
make install

使用方法：

sql

-- 创建扩展
CREATE EXTENSION pg_repack;

bash

# 重组表
pg_repack -h hostname -p port -U username -d database -t table_name

# 重组索引
pg_repack -h hostname -p port -U username -d database -i index_name

# 只重组特定索引
pg_repack -h hostname -p port -U username -d database -t table_name --only-indexes

5. pg_cron

pg_cron是一个定时任务扩展，用于执行定时维护任务。

主要功能：

支持cron表达式
可以执行SQL语句
支持日志记录
支持并行执行

安装方法：

sql

-- 在postgresql.conf中添加
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

-- 重启数据库后创建扩展
CREATE EXTENSION pg_cron;

使用方法：

sql

-- 创建定时任务（每天凌晨2点执行VACUUM）
SELECT cron.schedule('0 2 * * *', 'VACUUM ANALYZE');

-- 创建定时任务（每周日凌晨3点执行备份）
SELECT cron.schedule('0 3 * * 0', 'COPY (SELECT * FROM table_name) TO ''/path/to/backup.csv'' CSV');

-- 查看定时任务
SELECT * FROM cron.job;

-- 查看任务执行日志
SELECT * FROM cron.job_run_details;

-- 删除定时任务
SELECT cron.unschedule(job_id);

不同PostgreSQL版本的工具差异

PostgreSQL 9.x

内置工具功能相对基础
缺少一些高级诊断功能
第三方工具支持较少
扩展生态不够完善

PostgreSQL 10+

增强了内置工具功能
引入了pg_resetwal（替代pg_resetxlog）
第三方工具支持更完善
扩展生态逐渐成熟

PostgreSQL 12+

增强了WAL相关工具
引入了更多性能视图
第三方工具功能更强大
扩展生态更加丰富

PostgreSQL 14+

增强了系统资源统计工具
改进了复制相关工具
第三方工具支持最新特性
扩展生态非常完善

工具选择最佳实践

1. 根据需求选择工具

性能监控：pg_stat_statements、pg_stat_monitor、Prometheus + Grafana
慢查询分析：pgBadger、pg_stat_statements、pg_stat_monitor
故障诊断：psql、pg_controldata、pg_waldump
日常运维：psql、pgAdmin、pg_cron
审计日志：pgAudit、ELK Stack

2. 结合使用多种工具

使用psql进行快速查询和分析
使用pgBadger生成定期报告
使用Prometheus + Grafana进行实时监控
使用pgAdmin进行图形化管理

3. 考虑版本兼容性

确保工具支持当前PostgreSQL版本
关注工具的更新和维护情况
选择活跃维护的工具

4. 自动化工具使用

将常用诊断命令脚本化
使用cron或pg_cron定期执行诊断任务
配置监控工具自动告警

常见问题诊断案例

1. 数据库响应缓慢

诊断流程：

使用psql查看活跃会话和等待事件
使用pg_stat_statements查看慢查询
使用EXPLAIN ANALYZE分析查询计划
使用pgBadger分析历史慢查询
使用Prometheus + Grafana查看性能趋势

工具组合：

psql + pg_stat_statements + EXPLAIN ANALYZE + pgBadger

2. WAL损坏

诊断流程：

使用pg_controldata查看数据库状态
使用pg_waldump检查WAL文件完整性
使用pg_resetwal修复WAL损坏
使用pg_basebackup创建新的基础备份

工具组合：

pg_controldata + pg_waldump + pg_resetwal + pg_basebackup

3. 复制延迟

诊断流程：

使用psql查看主库复制状态
使用psql查看从库复制状态
使用pg_receivewal测试WAL传输
使用Prometheus + Grafana监控复制延迟

工具组合：

psql + pg_receivewal + Prometheus + Grafana

4. 表膨胀

诊断流程：

使用psql查看表大小和统计信息
使用pg_repack重组表和索引
使用pg_prewarm将数据加载到内存
使用pg_stat_statements验证优化效果

工具组合：

psql + pg_repack + pg_prewarm + pg_stat_statements

总结

PostgreSQL提供了丰富的诊断工具，DBA需要根据实际需求选择合适的工具。内置工具适合快速诊断和日常运维，第三方工具适合深度分析和监控，扩展工具适合特定功能需求。通过合理组合使用这些工具，DBA可以提高数据库诊断的效率和效果，更好地维护PostgreSQL数据库。

PostgreSQL 常用诊断工具 ​

诊断工具概述 ​

内置诊断工具 ​

1. psql命令行工具 ​

常用命令 ​

2. pg_controldata ​

3. pg_waldump ​

4. pg_basebackup ​

5. pg_dump和pg_restore ​

6. pg_resetwal/pg_resetxlog ​

7. pg_isready ​

8. pg_test_fsync ​

9. pg_test_timing ​

10. pg_receivewal ​

第三方诊断工具 ​

1. pgAdmin ​

2. pgBadger ​

3. pg_top ​

4. pg_stat_monitor ​

5. Prometheus + Grafana ​

6. Zabbix ​

PostgreSQL扩展诊断工具 ​

1. pg_stat_statements ​

2. pgAudit ​

3. pg_prewarm ​

4. pg_repack ​

5. pg_cron ​

不同PostgreSQL版本的工具差异 ​

PostgreSQL 9.x ​

PostgreSQL 10+ ​

PostgreSQL 12+ ​

PostgreSQL 14+ ​

工具选择最佳实践 ​

1. 根据需求选择工具 ​

2. 结合使用多种工具 ​

3. 考虑版本兼容性 ​

4. 自动化工具使用 ​

常见问题诊断案例 ​

1. 数据库响应缓慢 ​

2. WAL损坏 ​

3. 复制延迟 ​

4. 表膨胀 ​

总结 ​

PostgreSQL 常用诊断工具

诊断工具概述

内置诊断工具

1. psql命令行工具

常用命令

2. pg_controldata

3. pg_waldump

4. pg_basebackup

5. pg_dump和pg_restore

6. pg_resetwal/pg_resetxlog

7. pg_isready

8. pg_test_fsync

9. pg_test_timing

10. pg_receivewal

第三方诊断工具

1. pgAdmin

2. pgBadger

3. pg_top

4. pg_stat_monitor

5. Prometheus + Grafana

6. Zabbix

PostgreSQL扩展诊断工具

1. pg_stat_statements

2. pgAudit

3. pg_prewarm

4. pg_repack

5. pg_cron

不同PostgreSQL版本的工具差异

PostgreSQL 9.x

PostgreSQL 10+

PostgreSQL 12+

PostgreSQL 14+

工具选择最佳实践

1. 根据需求选择工具

2. 结合使用多种工具

3. 考虑版本兼容性

4. 自动化工具使用

常见问题诊断案例

1. 数据库响应缓慢

2. WAL损坏

3. 复制延迟

4. 表膨胀

总结