构建高性能的慢查询分析平台(pgBadger + TimescaleDB + Grafana)

一、引言

在数据库优化场景中，慢查询分析是性能瓶颈排查的关键一步。PostgreSQL 本身提供日志功能，但可视化能力较弱。本文将手把手带你搭建一个基于 pgBadger + TimescaleDB + Grafana 的慢查询分析平台，实现自动日志分析、存储与可视化，帮助数据库工程师高效定位问题。

二、架构总览

+-------------------+ +-------------------+ +------------------+

| PostgreSQL | ---> | pgBadger | ---> | TimescaleDB |

| 采集日志 | | 分析慢查询日志 | | 存储查询数据 |

+-------------------+ +-------------------+ +------------------+

|

+------------------+

| Grafana |

| 慢查询趋势可视化 |

+------------------+

三、环境准备

• 操作系统：Ubuntu 20.04 / CentOS 7+
• PostgreSQL 13+
• pgBadger 12+
• TimescaleDB（PostgreSQL 插件）
• Grafana 10+

四、配置 PostgreSQL 启用日志输出

编辑 postgresql.conf：

logging_collector = on

log_directory = 'log'

log_filename = 'postgresql.log'

log_statement = 'none'

log_min_duration_statement = 200 # 记录超过 200ms 的慢查询

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

重启数据库：

sudo systemctl restart postgresql

五、安装并配置 pgBadger

1. 安装依赖

sudo apt install libjson-xs-perl libtext-csv-xs-perl libdigest-md5-perl

2. 下载并安装 pgBadger

wget https://github.com/darold/pgbadger/archive/refs/tags/v12.3.tar.gz

tar xzf v12.3.tar.gz && cd pgbadger-12.3

perl Makefile.PL && make && sudo make install

3. 分析日志输出报告

pgbadger /var/lib/postgresql/data/log/postgresql.log -o report.html

访问 HTML 报告查看慢查询详情。

六、将分析结果导入 TimescaleDB

1. 安装 TimescaleDB 插件

sudo apt install timescaledb-postgresql-13

在 postgresql.conf 中启用插件：

shared_preload_libraries = 'timescaledb'

并执行：

SELECT timescaledb_preload_libraries();

2. 创建日志分析数据表

CREATE TABLE slow_queries (

ts TIMESTAMP,

query TEXT,

duration_ms FLOAT,

db TEXT,

user_name TEXT,

app TEXT

);

SELECT create_hypertable('slow_queries', 'ts');

3. 将 pgBadger 输出解析后写入数据库（可脚本化）

解析结果建议以 CSV 格式导入：

pgbadger -f stderr -o slow.csv /var/log/postgresql/postgresql.log

# 然后使用 COPY 命令导入 TimescaleDB

七、配置 Grafana 可视化慢查询数据

1. 添加 TimescaleDB 数据源

• 数据源类型：PostgreSQL
• URL：TimescaleDB 地址
• Access：Server

2. 创建可视化仪表盘

推荐图表：

• 查询耗时趋势（时间序列）
• 前 10 慢查询（表格）
• 慢查询总次数、平均耗时（指标）

SQL 示例：

SELECT time_bucket('1 minute', ts) AS time, avg(duration_ms)

FROM slow_queries

GROUP BY time

ORDER BY time;

八、总结

本实战方案结合 pgBadger + TimescaleDB + Grafana 实现了从日志采集、分析、存储到可视化的一体化慢查询诊断平台，适合中大型 PostgreSQL 应用系统。后续还可以：

• 增加告警功能（Grafana Alert）
• 接入 Ansible 自动部署分析链路
• 整合 Prometheus 实现统一监控视图

Tags：grafana是什么