你的 PostgreSQL 数据库性能监控是不是还停留在“慢查询日志 手动分析”的原始阶段当业务压力上来面对成百上千条 SQL你是否感到无从下手不知道哪条才是真正的性能瓶颈更无法追溯历史变化趋势今天要介绍的工具Pg_stat_ch正是为了解决这个核心痛点而生。它不是一个全新的监控系统而是一个精准的“数据搬运工”和“放大器”。它的核心价值在于将 PostgreSQL 内部最宝贵的性能遥测数据Query Telemetry——特别是pg_stat_statements模块收集的详细 SQL 执行统计信息——实时、高效地导出到 ClickHouse 这个专为分析而生的数据库中。这意味着什么意味着你可以用 SQL 对海量 SQL 性能数据进行任意维度的聚合、钻取和时序分析。你可以轻松回答以下问题“过去 24 小时哪个业务模块的查询平均耗时增长最快”“这条核心交易 SQL在每天下午 3 点的性能表现是怎样的趋势”“全库扫描最多的表是哪张是谁在频繁访问”本文的核心判断是Pg_stat_ch 的价值不在于其本身的复杂性而在于它打通了 PostgreSQL 性能数据与现代化分析引擎之间的“最后一公里”将原本局限于单机、快照式的性能视图变成了一个可持久化、可深度挖掘的数据资产。对于任何使用 PostgreSQL 支撑核心业务的中大型系统尤其是对数据库性能有持续优化需求的团队理解和部署这套组合拳能极大提升运维效率和问题排查的深度。接下来我们将从原理、部署到实战完整拆解如何使用 Pg_stat_ch 构建你的 PostgreSQL 性能分析平台。1. 为什么你需要关注查询遥测Query Telemetry在深入工具之前必须先理解它要搬运的“货物”究竟是什么。pg_stat_statements是 PostgreSQL 的内置扩展被誉为 DBA 和开发者的“性能神器”。它默默记录所有执行过的 SQL 语句的详细指标包括调用次数(calls)总执行时间(total_time)平均执行时间(mean_time)共享内存块命中数(shared_blks_hit)磁盘块读取数(shared_blks_read)临时文件 I/O(temp_blks_read/write)语句的归一化指纹(queryid)然而pg_stat_statements本身有几个固有局限数据是累积的它记录的是自扩展重置以来的总统计要计算时段差异需要手动做快照减法。存储于内存数据存储在共享内存中容量有限由pg_stat_statements.max控制旧的语句会被挤出。查询能力弱只能进行简单的排序和过滤缺乏强大的聚合、多维度分析和历史趋势对比能力。实例级别数据分散在各个数据库实例上难以集中分析和进行跨实例对比。这就是Query Telemetry查询遥测概念的用武之地。我们将这些离散的、瞬时的性能指标视为需要持续收集、上报和分析的遥测数据。Pg_stat_ch 扮演的就是Exporter导出器的角色像 Prometheus 的 Exporter 一样定期采集数据并将其转换为适合长期存储和分析的格式写入 ClickHouse。ClickHouse 的优势在于其卓越的实时分析能力。面对高频写入的 SQL 性能数据它能做到高速写入轻松应对每秒数千甚至上万行的数据插入。实时聚合在亿级数据上进行多维度 GROUP BY 查询依然能秒级响应。灵活分析支持标准的 SQL可以方便地与业务数据关联分析。所以这套方案的实质是pg_stat_statements数据源 - Pg_stat_ch采集与传输 - ClickHouse存储与分析。接下来我们看如何落地。2. 环境准备与前置条件在开始部署 Pg_stat_ch 之前请确保以下环境已经就绪。我们将以一个典型的 Linux 服务器环境为例进行说明。2.1 PostgreSQL 端配置你的 PostgreSQL 数据库版本建议 10 及以上需要启用pg_stat_statements扩展。修改配置文件postgresql.conf# 添加或修改以下参数 shared_preload_libraries pg_stat_statements # 多个库用逗号分隔 pg_stat_statements.max 10000 # 跟踪的语句数量根据需求调整 pg_stat_statements.track all # 跟踪所有语句包括嵌套的 pg_stat_statements.track_utility on # 跟踪 DDL 等实用命令 pg_stat_statements.save on # 服务器关闭时保存统计信息重启 PostgreSQL 服务使配置生效。在需要监控的数据库中创建扩展-- 连接到你的业务数据库例如 myapp_db \c myapp_db CREATE EXTENSION IF NOT EXISTS pg_stat_statements;验证扩展是否启用SELECT * FROM pg_stat_statements LIMIT 1;2.2 ClickHouse 端准备你需要一个可访问的 ClickHouse 服务版本建议 22.3 及以上。可以在另一台服务器安装或使用云服务。安装 ClickHouse以 Ubuntu 为例sudo apt-get install -y apt-transport-https ca-certificates dirmngr sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4 echo deb https://packages.clickhouse.com/deb stable main | sudo tee /etc/apt/sources.list.d/clickhouse.list sudo apt-get update sudo apt-get install -y clickhouse-server clickhouse-client sudo service clickhouse-server start登录并创建数据库和表。表结构需要与 Pg_stat_ch 导出的数据格式匹配。通常 Pg_stat_ch 的文档或代码中会提供推荐的建表 DDL。一个基础的示例如下CREATE DATABASE IF NOT EXISTS pg_telemetry; CREATE TABLE IF NOT EXISTS pg_telemetry.pg_stat_statements ( timestamp DateTime64(3) DEFAULT now(), host String, dbname String, username String, queryid UInt64, query String, calls UInt64, total_time Float64, min_time Float64, max_time Float64, mean_time Float64, stddev_time Float64, rows UInt64, shared_blks_hit UInt64, shared_blks_read UInt64, shared_blks_dirtied UInt64, shared_blks_written UInt64, local_blks_hit UInt64, local_blks_read UInt64, local_blks_dirtied UInt64, local_blks_written UInt64, temp_blks_read UInt64, temp_blks_written UInt64, blk_read_time Float64, blk_write_time Float64 ) ENGINE MergeTree PARTITION BY toYYYYMM(timestamp) ORDER BY (dbname, queryid, timestamp) SETTINGS index_granularity 8192;注实际表结构请以 Pg_stat_ch 项目的最新要求为准。2.3 Pg_stat_ch 导出器本身Pg_stat_ch 通常是一个独立的进程可能是用 Go、Python 等语言编写需要从源码编译或下载预编译的二进制文件。你需要准备运行它的服务器该服务器需要能同时网络访问 PostgreSQL 和 ClickHouse。3. Pg_stat_ch 的核心工作原理与配置Pg_stat_ch 的工作流程可以概括为一个简单的循环定时触发通过 Cron 任务或自身的内置调度器每隔 N 秒如 30 秒执行一次采集任务。数据采集连接到配置的 PostgreSQL 实例执行类似SELECT * FROM pg_stat_statements的查询获取当前快照。数据转换将采集到的数据转换为适合 ClickHouse 插入的格式如 JSON、CSV 或直接拼接 INSERT SQL。关键的一步是计算增量值。因为pg_stat_statements提供的是累积值所以 Pg_stat_ch 需要在内存中保留上一次的快照用本次值减去上次值得到间隔周期内的实际统计量。数据上报通过 ClickHouse 的 HTTP 接口或 TCP 原生接口将增量数据批量插入到指定的表中。清理与重置可选地在采集后执行SELECT pg_stat_statements_reset()来清空 PostgreSQL 端的累积统计避免数据溢出。但这会丢失历史对比基准更常见的做法是不重置而是依靠 Pg_stat_ch 自己进行差值计算。一个典型的 Pg_stat_ch 配置文件如config.yaml可能如下所示postgres: hosts: - host: 10.0.1.101 port: 5432 database: myapp_db username: monitor_user password: secure_password sslmode: disable # 采集间隔 scrape_interval: 30s clickhouse: host: 10.0.2.102 port: 8123 # HTTP 端口 database: pg_telemetry table: pg_stat_statements username: default password: # 批量插入大小 batch_size: 1000 logging: level: info output: /var/log/pg_stat_ch.log重要安全提醒配置中的密码应使用环境变量或密钥管理工具传入切勿直接硬编码在配置文件中。用于监控的数据库账号如monitor_user应仅授予必要的只读权限如对pg_stat_statements视图的 SELECT 权限。4. 部署与运行 Pg_stat_ch假设你已经获得了pg_stat_ch的二进制文件。部署步骤通常很简单。放置二进制文件与配置sudo mkdir -p /opt/pg_stat_ch sudo cp pg_stat_ch /opt/pg_stat_ch/ sudo cp config.yaml /opt/pg_stat_ch/ sudo chmod x /opt/pg_stat_ch/pg_stat_ch创建系统服务以 systemd 为例便于管理sudo vim /etc/systemd/system/pg_stat_ch.service写入以下内容[Unit] DescriptionPg_stat_ch - PostgreSQL to ClickHouse Exporter Afternetwork.target [Service] Typesimple Userpostgres # 建议使用 postgres 用户或专用用户 WorkingDirectory/opt/pg_stat_ch ExecStart/opt/pg_stat_ch/pg_stat_ch --config /opt/pg_stat_ch/config.yaml Restarton-failure RestartSec5s StandardOutputjournal StandardErrorjournal SyslogIdentifierpg_stat_ch [Install] WantedBymulti-user.target启动并启用服务sudo systemctl daemon-reload sudo systemctl start pg_stat_ch sudo systemctl enable pg_stat_ch sudo systemctl status pg_stat_ch # 检查状态查看日志确认运行正常sudo journalctl -u pg_stat_ch -f你应该能看到周期性的日志如“Scraping PostgreSQL at ...”、“Successfully wrote X rows to ClickHouse”。5. 数据验证与初步分析服务运行一段时间后登录 ClickHouse 验证数据。检查数据是否写入-- 连接到 ClickHouse clickhouse-client --host 10.0.2.102 --database pg_telemetry -- 查看数据量 SELECT count() FROM pg_stat_statements; -- 查看最新的一些记录 SELECT timestamp, dbname, left(query, 100) as short_query, calls, mean_time FROM pg_stat_statements ORDER BY timestamp DESC LIMIT 10;进行一个简单的分析找出平均耗时最长的查询。SELECT queryid, any(query) as sample_query, -- 取一个样例查询文本 sum(calls) as total_calls, avg(mean_time) as avg_latency_ms, sum(total_time) as total_time_spent FROM pg_stat_statements WHERE timestamp now() - INTERVAL 1 HOUR GROUP BY queryid HAVING total_calls 10 -- 过滤调用次数太少的 ORDER BY avg_latency_ms DESC LIMIT 20;这个查询能立即帮你定位过去一小时内最耗时的“热点”SQL。6. 构建性能监控仪表盘有了存储在 ClickHouse 中的标准化数据你可以使用任何喜欢的可视化工具如 Grafana来构建实时监控仪表盘。这是价值最大化的环节。在 Grafana 中添加 ClickHouse 数据源。创建仪表盘关键图表可以包括QPS 与平均延迟趋势图按数据库或用户聚合。慢查询 TOP N动态展示平均耗时或总耗时最高的查询。I/O 操作分析展示共享块命中率shared_blks_hit / (shared_blks_hit shared_blks_read)这是衡量缓存效率的关键指标。查询类型分布通过简单解析query字段如判断是否包含SELECT、UPDATE、INSERT了解数据库负载构成。错误查询监控可以关联 PostgreSQL 日志需另外收集或者监控mean_time或max_time异常突增的查询。一个 Grafana 查询示例每小时 QPS 与平均延迟SELECT toStartOfHour(timestamp) as time, dbname, sum(calls) as queries_per_second, avg(mean_time) as avg_latency_ms FROM pg_stat_statements WHERE timestamp $__timeFrom() AND timestamp $__timeTo() GROUP BY time, dbname ORDER BY time将此查询添加到 Grafana 的 Time series 图表中你就得到了一个专业的数据库负载监控视图。7. 常见问题与排查思路在部署和使用过程中你可能会遇到以下问题问题现象可能原因排查方式解决方案Pg_stat_ch 启动失败连接 PostgreSQL 被拒绝1. 网络不通或端口不对。2. 认证失败密码错误、pg_hba.conf 限制。3. 目标数据库未创建pg_stat_statements扩展。1. 用telnet pg_host pg_port测试连通性。2. 使用配置中的账号密码手动psql连接测试。3. 连接到目标库执行SELECT * FROM pg_stat_statements LIMIT 1;。1. 检查防火墙和安全组。2. 修正pg_hba.conf为监控主机/IP 添加md5或trust规则。3. 在目标库创建扩展。数据无法写入 ClickHouse1. ClickHouse 服务未运行或网络不通。2. 表结构不匹配。3. 账号权限不足。1. 检查 ClickHouse 服务状态和端口8123/9000。2. 查看 Pg_stat_ch 日志中的错误信息通常是字段类型或数量不匹配。3. 手动用相同账号执行 INSERT 语句测试。1. 启动服务或配置网络。2. 根据错误信息调整 ClickHouse 表结构或调整 Pg_stat_ch 的数据生成逻辑。3. 在 ClickHouse 中授予用户对目标库表的 INSERT 权限。ClickHouse 中查询到的calls等指标值异常小或为0Pg_stat_ch 的差值计算逻辑有问题或者两个采集周期之间 PostgreSQL 的统计被重置了。1. 检查 Pg_stat_ch 日志看是否有重置操作的记录。2. 直接查询 PostgreSQL 的pg_stat_statements对比数值。3. 检查 Pg_stat_ch 是否配置了自动重置 (pg_stat_statements_reset)。1. 确保 Pg_stat_ch 正确实现了差值计算并持久化存储了上一次的快照。2. 除非特定需求避免在 Pg_stat_ch 中或其它地方频繁执行重置操作。数据延迟高1. 采集间隔 (scrape_interval) 设置过长。2. 网络延迟高或批处理 (batch_size) 设置不合理。3. ClickHouse 写入压力大。1. 观察 Pg_stat_ch 日志中每个周期的耗时。2. 监控网络状况和 ClickHouse 的system.metrics表。1. 适当缩短采集间隔如从 60s 改为 30s但需权衡对 PostgreSQL 的查询压力。2. 优化网络链路。3. 调整 ClickHouse 的合并树MergeTree引擎参数或升级硬件。磁盘空间增长过快1. 采集频率太高数据量过大。2. 未对 ClickHouse 表设置合理的 TTL生存时间。1. 查询 ClickHouse 表的数据行数和大小。2. 检查表配置。1. 降低非核心指标的采集频率。2. 为表添加 TTL 设置自动删除过期数据如 30 天前。ALTER TABLE pg_stat_statements MODIFY TTL timestamp INTERVAL 30 DAY8. 最佳实践与进阶建议要让这套系统稳定、高效地运行并发挥最大价值请考虑以下建议权限最小化为 Pg_stat_ch 创建专用的 PostgreSQL 只读用户和 ClickHouse 用户仅授予必要权限。高可用与负载对于重要的生产集群可以考虑部署多个 Pg_stat_ch 实例或者让其具备从多个 PostgreSQL 实例采集数据的能力。确保 Exporter 本身无单点故障。数据聚合与降采样原始数据粒度很细长期存储成本高。可以在 ClickHouse 中创建物化视图Materialized View按小时或天对数据进行聚合如 sum(calls), avg(mean_time)并将原始数据表设置较短的 TTL聚合表设置较长的 TTL。关联上下文pg_stat_statements中的queryid是归一化后的指纹但query字段可能因参数不同而不同。考虑在应用中规范 SQL 日志输出带有参数绑定的queryid以便在应用日志和性能数据之间建立关联。监控 Pg_stat_ch 本身使用 Prometheus 监控 Pg_stat_ch 的进程状态、采集次数、写入行数等指标确保这个监控系统自身是健康的。安全考虑确保 PostgreSQL 和 ClickHouse 之间的通信以及 Pg_stat_ch 与它们之间的通信都在内网安全环境中。如果跨公网务必使用 SSL/TLS 加密连接。9. 总结Pg_stat_ch 作为一个轻量级的导出器其设计哲学是“做好一件事”。它不试图取代完整的 APM 系统而是为 PostgreSQL 数据库提供了一个低成本、高效率、高自由度的性能数据深度分析入口。通过将pg_stat_statements的数据实时同步到 ClickHouse你获得的不再是静态的快照而是一个鲜活的、可任意查询的数据库性能数据仓库。你可以基于此快速定位历史性能问题当业务反馈“昨天下午系统有点慢”时你可以直接查询那个时间段的慢查询 Top 10。建立性能基线通过长期的数据积累了解不同 SQL 在正常业务负载下的表现为异常检测提供依据。评估优化效果在进行了索引优化、参数调整或版本升级后通过对比优化前后特定 SQL 的mean_time、shared_blks_hit等指标量化优化成果。部署过程涉及 PostgreSQL 配置、ClickHouse 部署、Exporter 运行和可视化配置看似步骤不少但每一步都是标准的运维操作。一旦这套流水线搭建完毕它将成为你数据库运维工作中一个强大的“瞭望塔”。建议你从测试环境开始用本文的步骤搭建一个最小化的原型亲身体验从数据采集、存储到分析的全流程。当你第一次在 Grafana 上看到自己数据库的 SQL 性能以秒级刷新呈现时你就会明白这种对数据库内部运行状态的透明化洞察对于保障系统稳定和提升开发效率而言是多么重要的一步。