PostgreSQL 第三方诊断工具

性能诊断工具

1. pgBadger

pgBadger 是最流行的 PostgreSQL 日志分析工具，提供详细的 HTML 报告，包括慢查询、连接统计、错误统计等。

安装

bash

# Debian/Ubuntu
apt-get update && apt-get install -y pgbadger

# CentOS/RHEL
yum install -y pgbadger

# 从源码安装
git clone https://github.com/dalibo/pgbadger.git
cd pgbadger
perl Makefile.PL
make && make install

使用示例

bash

# 分析日志并生成报告
pgbadger /var/log/postgresql/postgresql-15-main.log -o postgresql_report.html

# 指定日志格式
pgbadger -f stderr /var/log/postgresql/postgresql-15-main.log -o postgresql_report.html

# 分析多个日志文件
pgbadger /var/log/postgresql/postgresql-15-main.log* -o postgresql_report.html

2. pg_stat_statements

虽然 pg_stat_statements 是 PostgreSQL 的内置扩展，但通常被视为重要的性能诊断工具，用于跟踪 SQL 语句的执行统计信息。

安装和配置

sql

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

-- 在 postgresql.conf 中配置
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track_utility = on;

使用示例

sql

-- 查看最耗时的查询
SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查看执行次数最多的查询
SELECT queryid, query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

3. EXPLAIN ANALYZE 可视化工具

3.1 pgAdmin Explain Analyze

pgAdmin 提供了可视化的 EXPLAIN ANALYZE 功能，可以直观地查看查询执行计划。

3.2 Depesz Query Analyzer

在线工具，用于可视化和分析 EXPLAIN ANALYZE 输出：

bash

# 生成 EXPLAIN ANALYZE 输出
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM table_name WHERE condition;

# 将输出粘贴到 https://explain.depesz.com/ 进行分析

3.3 Postgres Explain Visualizer (pev2)

另一个在线 EXPLAIN 可视化工具：https://tatiyants.com/pev2/

监控工具

1. Prometheus + Grafana

Prometheus 是一个开源的监控和告警系统，Grafana 用于可视化监控数据。结合 postgres_exporter，可以监控 PostgreSQL 的各种指标。

安装 postgres_exporter

bash

# 下载并安装 postgres_exporter
download_url=$(curl -s https://api.github.com/repos/prometheus-community/postgres_exporter/releases/latest | grep browser_download_url | grep linux-amd64 | cut -d '"' -f 4)
wget $download_url -O postgres_exporter.tar.gz
tar xzf postgres_exporter.tar.gz
cd postgres_exporter-*

# 配置数据库连接
export DATA_SOURCE_NAME="postgresql://postgres:password@localhost:5432/postgres?sslmode=disable"

# 启动 exporter
./postgres_exporter --web.listen-address=":9187"

配置 Prometheus

txt

# prometheus.yml
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']

导入 Grafana 仪表板

启动 Grafana 并登录
导入 PostgreSQL 相关仪表板，推荐仪表板 ID：763
配置数据源为 Prometheus

2. Zabbix

Zabbix 是一个企业级的监控解决方案，支持监控 PostgreSQL 数据库。

安装 Zabbix Agent

bash

# Debian/Ubuntu
apt-get install -y zabbix-agent

# CentOS/RHEL
yum install -y zabbix-agent

配置 Zabbix Agent

txt

# /etc/zabbix/zabbix_agentd.conf
Server=zabbix-server-ip
Hostname=postgres-server

安装 PostgreSQL 模板

在 Zabbix 服务器上导入 PostgreSQL 模板
配置监控项，包括连接数、查询速率、缓存命中率等
设置告警规则

3. Nagios

Nagios 是一个经典的监控系统，通过插件可以监控 PostgreSQL 数据库。

安装 Nagios 插件

bash

# 安装 nagios-plugins-basic
apt-get install -y nagios-plugins-basic

# 安装 check_postgres 插件
git clone https://github.com/bucardo/check_postgres.git
cd check_postgres
perl Makefile.PL
make && make install

配置 Nagios 服务

txt

# /etc/nagios/conf.d/postgresql.cfg
define service {
    host_name           postgres-server
    service_description PostgreSQL Connection
    check_command       check_postgres!connection!-u postgres -d postgres
    max_check_attempts  3
    check_interval      5
    retry_interval      1
    check_period        24x7
    notification_period 24x7
}

管理工具

1. pgAdmin

pgAdmin 是 PostgreSQL 官方的图形化管理工具，提供了全面的数据库管理功能，包括性能监控、查询分析、备份恢复等。

安装

bash

# Debian/Ubuntu
apt-get install -y pgadmin4

# CentOS/RHEL
yum install -y pgadmin4

# Docker 部署
docker run -p 5050:80 -e PGADMIN_DEFAULT_EMAIL=admin@example.com -e PGADMIN_DEFAULT_PASSWORD=admin -d dpage/pgadmin4

主要功能

图形化查询编辑器，支持语法高亮和自动完成
可视化的 EXPLAIN ANALYZE
数据库对象管理（表、索引、视图等）
备份和恢复功能
服务器监控仪表板
角色和权限管理

2. DBeaver

DBeaver 是一个通用的数据库管理工具，支持 PostgreSQL 和多种其他数据库，提供了强大的查询编辑和数据可视化功能。

安装

bash

# Debian/Ubuntu
download_url=$(curl -s https://api.github.com/repos/dbeaver/dbeaver/releases/latest | grep browser_download_url | grep amd64.deb | cut -d '"' -f 4)
wget $download_url -O dbeaver.deb
dpkg -i dbeaver.deb
apt-get install -f -y

# CentOS/RHEL
download_url=$(curl -s https://api.github.com/repos/dbeaver/dbeaver/releases/latest | grep browser_download_url | grep x86_64.rpm | cut -d '"' -f 4)
wget $download_url -O dbeaver.rpm
yum install -y dbeaver.rpm

主要功能

多数据库支持
强大的查询编辑器
数据可视化和导出
数据库比较和同步
ER 图生成
插件扩展系统

3. Adminer

Adminer 是一个轻量级的数据库管理工具，使用 PHP 开发，只有一个文件，便于部署和使用。

安装

bash

# 下载 Adminer
mkdir -p /var/www/html/adminer
cd /var/www/html/adminer
wget https://github.com/vrana/adminer/releases/latest/download/adminer.php

# 配置 Web 服务器（Apache）
a2enmod php8.1
systemctl restart apache2

备份恢复工具

1. Barman

Barman 是一个用于 PostgreSQL 备份和恢复管理的工具，支持远程备份、WAL 归档和时间点恢复。

安装

bash

# Debian/Ubuntu
apt-get install -y barman

# CentOS/RHEL
yum install -y barman

配置

txt

# /etc/barman.conf
[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
retention_policy = RECOVERY WINDOW OF 7 DAYS

# 配置 PostgreSQL 服务器
[primary]
host = primary.example.com
user = barman
password = barman_password
dbname = postgres
port = 5432
backup_method = postgres

使用示例

bash

# 执行基础备份
barman backup primary

# 查看备份列表
barman list-backup primary

# 恢复到指定时间点
barman recover primary latest /var/lib/postgresql/15/restore --target-time="2023-01-01 12:00:00"

2. pgBackRest

pgBackRest 是一个高性能的 PostgreSQL 备份恢复工具，支持并行备份、增量备份和压缩。

安装

bash

# Debian/Ubuntu
apt-get install -y pgbackrest

# CentOS/RHEL
yum install -y pgbackrest

配置

txt

# /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=7
log-level-console=info
log-level-file=debug

[main]
pg1-path=/var/lib/postgresql/15/main

使用示例

bash

# 执行全量备份
pgbackrest --stanza=main backup --type=full

# 执行增量备份
pgbackrest --stanza=main backup --type=incr

# 恢复备份
pgbackrest --stanza=main restore

安全诊断工具

1. pgAudit

pgAudit 是一个 PostgreSQL 扩展，用于提供详细的审计日志，记录数据库活动，包括查询、角色更改、权限更改等。

安装和配置

sql

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

-- 在 postgresql.conf 中配置
ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
ALTER SYSTEM SET pgaudit.log = 'write, function, role, ddl';
ALTER SYSTEM SET pgaudit.log_level = 'notice';
ALTER SYSTEM SET pgaudit.log_parameter = on;

使用示例

sql

-- 查看审计日志
SELECT * FROM pg_log WHERE message LIKE '%AUDIT: %';

2. pg_hba.conf 分析工具

2.1 pg_hba.conf 语法检查

bash

# 使用 pg_ctl 检查 pg_hba.conf 语法
pg_ctl -D /var/lib/postgresql/15/main checkhba

2.2 pg_hba.conf 可视化工具

在线工具：https://pg-hba-parser.cybertec.at/ 用于可视化和分析 pg_hba.conf 文件。

最佳实践

1. 工具选择原则

根据需求选择合适的工具：性能诊断、监控、管理或备份恢复
考虑工具的易用性和学习曲线
评估工具的性能影响，特别是在生产环境中
选择活跃维护的开源工具或商业支持的工具

2. 工具组合使用

结合使用多种工具，全面了解数据库状态
使用 pgBadger 分析日志，pg_stat_statements 分析查询性能
使用 Prometheus + Grafana 进行实时监控
使用 pgAdmin 或 DBeaver 进行日常管理
使用 Barman 或 pgBackRest 进行备份管理

3. 定期更新工具

定期更新工具版本，获取新功能和 bug 修复
测试新版本在非生产环境中的兼容性
关注工具的安全公告，及时修补安全漏洞

4. 配置合理的权限

为诊断工具创建专门的数据库用户，授予最小必要权限
限制工具访问敏感数据
加密工具与数据库之间的连接

5. 自动化工具使用

将工具集成到自动化脚本中，定期执行
配置工具的自动报告生成和发送
结合 CI/CD 流程，在部署前运行诊断工具

常见问题（FAQ）

Q1：如何选择适合的 PostgreSQL 诊断工具？

A1：根据具体需求选择：

日志分析：pgBadger
查询性能分析：pg_stat_statements
实时监控：Prometheus + Grafana
图形化管理：pgAdmin 或 DBeaver
备份恢复：Barman 或 pgBackRest
安全审计：pgAudit

Q2：第三方诊断工具会影响数据库性能吗？

A2：大多数工具的性能影响很小，但需要合理配置：

pg_stat_statements 会有轻微的性能开销，建议在生产环境中启用
日志分析工具（如 pgBadger）通常在离线环境中运行，不影响生产数据库
监控工具的性能影响取决于配置的监控项数量和频率

Q3：如何确保诊断工具的安全性？

A3：

为诊断工具创建专门的用户，授予最小必要权限
加密工具与数据库之间的连接
限制工具的网络访问范围
定期更新工具版本，修补安全漏洞
不要在诊断报告中包含敏感信息

Q4：如何自动化诊断工具的使用？

A4：

使用 cron 或 systemd timer 定期执行诊断脚本
配置工具自动生成报告并发送邮件
将工具集成到监控系统中，触发式执行
结合 CI/CD 流程，在部署前后运行诊断工具

Q5：有哪些商业的 PostgreSQL 诊断工具？

A5：

EnterpriseDB Postgres Enterprise Manager (PEM)
Datadog PostgreSQL Monitoring
New Relic PostgreSQL Monitoring
SolarWinds Database Performance Monitor
Amazon RDS Performance Insights（适用于 AWS RDS PostgreSQL）

Q6：如何分析诊断工具的输出？

A6：

关注关键指标：慢查询、高CPU查询、锁等待、连接数
对比历史数据，识别趋势变化
结合多个工具的输出，全面分析问题
关注异常值和突然变化
使用可视化工具，更直观地理解数据

PostgreSQL 第三方诊断工具 ​

性能诊断工具 ​

1. pgBadger ​

安装 ​

使用示例 ​

2. pg_stat_statements ​

安装和配置 ​

使用示例 ​

3. EXPLAIN ANALYZE 可视化工具 ​

3.1 pgAdmin Explain Analyze ​

3.2 Depesz Query Analyzer ​

3.3 Postgres Explain Visualizer (pev2) ​

监控工具 ​

1. Prometheus + Grafana ​

安装 postgres_exporter ​

配置 Prometheus ​

导入 Grafana 仪表板 ​

2. Zabbix ​

安装 Zabbix Agent ​

配置 Zabbix Agent ​

安装 PostgreSQL 模板 ​

3. Nagios ​

安装 Nagios 插件 ​

配置 Nagios 服务 ​

管理工具 ​

1. pgAdmin ​

安装 ​

主要功能 ​

2. DBeaver ​

安装 ​

主要功能 ​

3. Adminer ​

安装 ​

备份恢复工具 ​

1. Barman ​

安装 ​

配置 ​

使用示例 ​

2. pgBackRest ​

安装 ​

配置 ​

使用示例 ​

安全诊断工具 ​

1. pgAudit ​

安装和配置 ​

使用示例 ​

2. pg_hba.conf 分析工具 ​

2.1 pg_hba.conf 语法检查 ​

2.2 pg_hba.conf 可视化工具 ​

最佳实践 ​

1. 工具选择原则 ​

2. 工具组合使用 ​

PostgreSQL 第三方诊断工具

性能诊断工具

1. pgBadger

安装

使用示例

2. pg_stat_statements

安装和配置

使用示例

3. EXPLAIN ANALYZE 可视化工具

3.1 pgAdmin Explain Analyze

3.2 Depesz Query Analyzer

3.3 Postgres Explain Visualizer (pev2)

监控工具

1. Prometheus + Grafana

安装 postgres_exporter

配置 Prometheus

导入 Grafana 仪表板

2. Zabbix

安装 Zabbix Agent

配置 Zabbix Agent

安装 PostgreSQL 模板

3. Nagios

安装 Nagios 插件

配置 Nagios 服务

管理工具

1. pgAdmin

安装

主要功能

2. DBeaver

安装

主要功能

3. Adminer

安装

备份恢复工具

1. Barman

安装

配置

使用示例

2. pgBackRest

安装

配置

使用示例

安全诊断工具

1. pgAudit

安装和配置

使用示例

2. pg_hba.conf 分析工具

2.1 pg_hba.conf 语法检查

2.2 pg_hba.conf 可视化工具

最佳实践

1. 工具选择原则

2. 工具组合使用