这次我们来看一个专门解决 PostgreSQL 数据库监控痛点的开源工具Pg_stat_ch。它的核心任务很明确——将 PostgreSQL 的查询遥测数据Query Telemetry实时导出到 ClickHouse。对于任何在运维 PostgreSQL 生产环境尤其是面临性能分析、慢查询追踪和容量规划挑战的团队来说这个工具直接命中了一个关键需求如何低成本、高效率地存储和分析海量的 SQL 执行明细。传统的pg_stat_statements视图虽然强大但数据存储在内存中重启即失历史分析能力弱。而 Pg_stat_ch 扮演了一个“搬运工”和“翻译官”的角色它周期性地抓取这些性能数据并将其转化为适合 ClickHouse 这种列式分析数据库的格式进行存储。这样一来你就能利用 ClickHouse 强大的聚合查询和实时分析能力对数据库的运行状况进行深度洞察。本文将带你快速搞懂 Pg_stat_ch 的核心能力、部署门槛和实战用法。如果你关心以下问题那么这篇文章值得你仔细阅读功能定位它到底是做什么的解决了什么具体问题部署成本安装复杂吗对生产环境侵入性大吗资源开销作为后台进程它会拖慢我的数据库吗使用效果数据导出后在 ClickHouse 里能怎么分析落地实践从安装配置到查询分析一整套流程如何跑通我们会重点关注它的架构原理、一键启动方式、数据导出机制以及如何利用导出的数据构建实用的性能监控仪表盘。整个流程不涉及复杂的 AI 模型或显卡需求核心是数据库生态的运维工具整合。1. 核心能力速览在深入细节之前先用一个表格快速了解 Pg_stat_ch 的关键特性判断它是否适合你的技术栈。能力项说明项目类型数据库监控数据导出器Exporter核心功能将 PostgreSQL 的pg_stat_statements等性能视图数据定期同步到 ClickHouse数据源PostgreSQL 9.4 (需启用pg_stat_statements扩展)目标库ClickHouse部署模式独立进程二进制文件或容器作为 Sidecar 运行对 PostgreSQL 本身无侵入同步方式定时轮询可配置间隔增量导出主要输出指标查询调用次数、总耗时、平均耗时、行统计、共享块命中率等是否支持 API通常提供 HTTP 健康检查端点核心是数据导出服务是否支持批量任务本身就是持续的批量数据同步任务配置复杂度中等需配置两端数据库连接信息适合场景PostgreSQL 生产环境性能监控、慢查询历史分析、容量规划、自定义监控仪表盘简单来说你可以把它理解为一个专为 PostgreSQL 和 ClickHouse 打造的、定制化的“监控 Agent”它省去了你手动搭建流水线如通过 Logstash、Fluentd 或自定义脚本的麻烦。2. 适用场景与使用边界2.1 谁需要这个工具Pg_stat_ch 主要服务于以下角色和场景DBA 与运维工程师需要长期追踪数据库性能趋势定位历史慢查询进行容量评估。开发团队希望了解应用 SQL 的真实执行情况优化代码逻辑。SRE 团队构建统一、可扩展的数据库监控体系将数据接入现有的 Grafana 等可视化平台。拥有高负载 PostgreSQL 集群的团队需要对 SQL 执行情况进行精细化分析。2.2 它能解决什么问题历史数据持久化pg_stat_statements的数据在重启后会丢失Pg_stat_ch 将其持久化到 ClickHouse实现长期存储。高性能分析利用 ClickHouse 的列式存储和向量化执行引擎即使面对数十亿条 SQL 记录也能快速进行聚合、筛选和关联查询。定制化监控摆脱预置监控工具的固定报表可以根据业务需求自由地对 SQL 指标进行多维分析如按用户、客户端 IP、数据库、查询模式分组。降低监控成本相比将详细 SQL 指标存入传统关系型数据库或昂贵的 APM 服务ClickHouse 在存储和查询这类时间序列数据上通常更具成本效益。2.3 不适合什么场景仅需实时告警如果只需要对当前活跃的慢查询进行实时告警直接查询pg_stat_statements视图或使用现有监控工具可能更简单。无 ClickHouse 环境引入 Pg_stat_ch 意味着需要维护一个 ClickHouse 实例这会增加架构复杂度。如果没有 ClickHouse 或不愿意维护则不适合。极低延迟要求数据同步是周期性的如每分钟不适合需要亚秒级延迟的实时监控场景。轻量级测试环境对于个人开发或测试环境pg_stat_statements自带的查询可能已足够引入此工具略显繁重。2.4 合规与安全边界敏感信息pg_stat_statements可能包含 SQL 查询文本其中或涉及敏感数据如电话号码、邮箱片段。在将数据导出到 ClickHouse 前需评估是否需要进行脱敏处理并确保 ClickHouse 集群的访问权限得到严格控制。数据所有权明确监控数据的存储、访问和使用策略符合公司数据安全管理规定。资源占用需评估该工具对 PostgreSQL 和 ClickHouse 的额外负载避免影响核心业务。3. 环境准备与前置条件在部署 Pg_stat_ch 之前请确保以下环境就绪。3.1 源端PostgreSQL 数据库版本PostgreSQL 9.4 或更高版本建议使用较新版本如 12。扩展必须启用pg_stat_statements扩展。-- 在需要监控的数据库中执行 CREATE EXTENSION IF NOT EXISTS pg_stat_statements;配置在postgresql.conf中调整相关参数确保能收集到足够的统计信息。# 增加跟踪的语句数量默认5000 pg_stat_statements.max 10000 # 跟踪所有语句包括嵌套调用和工具命令 pg_stat_statements.track all # 保存查询文本 pg_stat_statements.save on修改后需重启 PostgreSQL 或执行SELECT pg_reload_conf();。权限需要创建一个专用于数据采集的数据库用户并授予读取pg_stat_statements视图的权限。CREATE USER pg_monitor WITH PASSWORD your_strong_password; GRANT pg_monitor TO your_admin_user; -- 或者直接授予必要权限 -- 确保该用户能连接到目标数据库并查询 pg_stat_statements3.2 目标端ClickHouse 数据库安装部署一个 ClickHouse 服务器。可以从 官网 下载安装包或使用 Docker 镜像。建表需要预先在 ClickHouse 中创建用于接收数据的表。表结构需要与 Pg_stat_ch 导出的数据格式匹配。通常工具会提供建表 DDL 或自动建表功能。权限创建一个拥有向目标表INSERT权限的 ClickHouse 用户。3.3 运行 Pg_stat_ch 的主机操作系统Linux (x86_64) 是主要支持平台macOS 可能支持Windows 支持情况需查看具体版本。网络该主机需要能同时访问 PostgreSQL 和 ClickHouse 的服务端口。资源Pg_stat_ch 本身是轻量级 Go 应用消耗很少的 CPU 和内存。主要资源消耗在于网络 I/O 和 ClickHouse 的写入负载。4. 安装部署与启动方式Pg_stat_ch 通常提供多种部署方式这里以最常见的二进制文件部署为例。4.1 下载与安装访问项目的 GitHub Releases 页面下载对应你操作系统架构的二进制文件。# 示例假设最新版本为 v0.1.0适用于 linux amd64 wget https://github.com/your-org/pg_stat_ch/releases/download/v0.1.0/pg_stat_ch-linux-amd64将文件移动到系统路径并赋予执行权限。sudo mv pg_stat_ch-linux-amd64 /usr/local/bin/pg_stat_ch sudo chmod x /usr/local/bin/pg_stat_ch4.2 配置文件准备Pg_stat_ch 通常通过 YAML 或 TOML 文件进行配置。创建一个配置文件例如config.yaml。# config.yaml postgres: host: 192.168.1.100 port: 5432 database: your_monitored_db user: pg_monitor password: your_strong_password sslmode: disable # 根据实际情况调整 clickhouse: host: 192.168.1.200 port: 9000 database: metrics table: pg_stat_statements user: ch_writer password: clickhouse_password exporter: interval: 60s # 导出间隔例如每分钟一次 batch_size: 1000 # 每批插入 ClickHouse 的记录数 # 可能还有其他选项如是否重置 pg_stat_statements 计数器等重要请务必将配置文件中的密码等敏感信息妥善保管或使用环境变量替代。4.3 在 ClickHouse 中创建目标表根据 Pg_stat_ch 的数据模型创建表。以下是一个示例 DDL-- 在 ClickHouse 中执行 CREATE DATABASE IF NOT EXISTS metrics; CREATE TABLE metrics.pg_stat_statements ( timestamp DateTime DEFAULT now(), pg_instance 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 (timestamp, pg_instance, dbname, queryid) SETTINGS index_granularity 8192;请注意实际字段可能因 Pg_stat_ch 版本而异请以官方文档为准。4.4 启动服务使用配置文件启动 Pg_stat_ch 服务。pg_stat_ch --config ./config.yaml如果一切正常你将看到类似以下的日志输出表明它已开始周期性工作INFO[0000] Starting pg_stat_ch exporter INFO[0000] Connected to PostgreSQL at 192.168.1.100:5432 INFO[0000] Connected to ClickHouse at 192.168.1.200:9000 INFO[0000] Exporter started with interval 60s INFO[0060] Exported 1234 queries to ClickHouse INFO[0120] Exported 567 queries to ClickHouse4.5 使用 Docker 启动可选如果项目提供了 Docker 镜像部署会更简单。docker run -d \ --name pg_stat_ch \ -v /path/to/your/config.yaml:/config.yaml \ -e CONFIG_PATH/config.yaml \ your-registry/pg_stat_ch:latest5. 功能测试与效果验证启动后我们需要验证数据是否正常从 PostgreSQL 流向 ClickHouse。5.1 验证数据写入 ClickHouse连接到你的 ClickHouse 服务器。clickhouse-client --host 192.168.1.200 --user default --password查询metrics.pg_stat_statements表检查是否有数据。USE metrics; SELECT count(), min(timestamp), max(timestamp) FROM pg_stat_statements;如果查询返回了计数和时间戳说明数据同步成功。5.2 验证数据完整性在 PostgreSQL 端执行一些查询然后观察数据是否被捕获。在 PostgreSQL 中执行一个辨识度高的测试查询。SELECT pg_stat_ch_test_ || now() AS test_marker;等待一个导出周期如60秒后在 ClickHouse 中搜索这个查询。SELECT query, calls, mean_time FROM metrics.pg_stat_statements WHERE query LIKE %pg_stat_ch_test_% ORDER BY timestamp DESC LIMIT 5;你应该能看到刚刚执行的查询记录包含其执行次数和平均时间。5.3 验证周期性导出观察 Pg_stat_ch 的日志确认它正在按配置的间隔如每分钟稳定运行并且每次导出的记录数合理非零。同时可以连续执行几次SELECT count() FROM metrics.pg_stat_statements观察行数是否随时间增长。6. 接口 API 与批量任务Pg_stat_ch 的核心是一个持续运行的导出服务它本身通常不提供复杂的对外 API 供外部调用以触发单次任务。它的“批量任务”就是其核心的周期性同步作业。6.1 服务健康检查许多此类导出器会提供一个简单的 HTTP 端点用于健康检查。你可以查看其文档或代码确认是否支持。例如它可能在http://localhost:8080/health提供一个端点。你可以用curl测试curl http://localhost:8080/health预期返回OK或类似的 JSON 健康状态。6.2 任务配置与管理“批量任务”的特性体现在配置文件中interval: 控制任务执行频率。根据监控粒度需求调整如30s,1m,5m。batch_size: 控制每次同步时一次性插入 ClickHouse 的数据量。适当调大可以提高写入效率但需考虑 ClickHouse 的负载和内存。重置行为有些工具提供reset_statements选项。如果设置为true则在每次导出后会调用pg_stat_statements_reset()清空 PostgreSQL 端的统计信息避免重复计数。生产环境慎用此选项除非你确定需要从零开始计数。6.3 与调度系统集成虽然 Pg_stat_ch 自带调度但在某些架构中你可能希望由外部系统如 Kubernetes CronJob来控制执行。这时可以配置 Pg_stat_ch 以“单次运行”模式执行然后由外部调度器定期调用。pg_stat_ch --config ./config.yaml --once这种模式下程序执行一次数据导出后就会退出。你需要查阅项目文档确认是否支持--once这类参数。7. 资源占用与性能观察作为数据库的监控组件其自身的资源消耗必须足够低。7.1 Pg_stat_ch 进程资源占用CPU通常在空闲时接近 0%在数据导出瞬间会有小幅波动整体可忽略不计。内存作为 Go 编写的静态二进制程序内存占用很小一般在几十 MB 以内。网络 I/O每个同步周期会产生一次从 PostgreSQL 读取数据和向 ClickHouse 写入数据的网络流量。数据量取决于pg_stat_statements中累积的 SQL 种类数量。你可以使用top、htop或ps命令观察其资源使用情况。ps aux | grep pg_stat_ch top -p $(pgrep -f pg_stat_ch)7.2 对 PostgreSQL 的影响Pg_stat_ch 通过执行SELECT * FROM pg_stat_statements之类的查询来获取数据。这个查询本身是轻量级的因为它查询的是内存中的统计视图。只要同步间隔不是特别短如每秒对生产数据库的性能影响微乎其微。7.3 对 ClickHouse 的影响影响主要在于写入写入压力写入频率和批量大小interval和batch_size决定了写入压力。对于每分钟万次级别的 SQL 调用写入量是可控的。表引擎选择使用MergeTree系列引擎能很好地处理这种时间序列数据的顺序写入和后台合并。分区策略如前例按toYYYYMM(timestamp)分区可以有效管理数据生命周期方便删除旧数据。7.4 性能调优建议调整同步间隔对于高并发系统可以适当缩短间隔如30秒以获取更实时的数据对于低负载系统可以延长间隔如5分钟以降低开销。优化 ClickHouse 写入确保 ClickHouse 有足够的内存处理插入批次。如果batch_size很大可以监控 ClickHouse 的MemoryTracker指标。监控导出延迟可以在日志中观察每次导出的耗时或通过对比 ClickHouse 中最新数据的时间戳与当前时间来判断延迟。8. 常见问题与排查方法部署和使用过程中可能会遇到一些问题下表列出了常见现象及解决方法。问题现象可能原因排查方式解决方案启动失败无法连接 PostgreSQL1. 网络不通或防火墙阻止。2. 连接参数主机、端口、密码错误。3. PostgreSQL 未启用pg_stat_statements。1. 使用telnet或psql测试连通性。2. 检查配置文件。3. 在psql中执行SELECT * FROM pg_stat_statements LIMIT 1;看是否报错。1. 开通网络。2. 修正配置。3. 启用扩展并重启 PG。启动失败无法连接 ClickHouse1. 网络不通。2. ClickHouse 用户权限不足。3. 表不存在。1. 测试网络连通性。2. 使用clickhouse-client测试用户登录和 INSERT 权限。3. 登录 ClickHouse 检查表是否存在。1. 开通网络。2. 授予足够权限。3. 创建目标表。服务已启动但 ClickHouse 中无数据1. 同步间隔未到。2.pg_stat_statements中无数据新实例或刚重置。3. 程序读取或写入过程出错但日志级别不够。1. 等待一个周期并查看日志。2. 在 PostgreSQL 中执行一些查询再检查pg_stat_statements。3. 将日志级别调整为DEBUG如果支持查看详细过程。1. 耐心等待。2. 触发一些数据库活动。3. 根据 DEBUG 日志定位具体错误。ClickHouse 表中有数据但查询文本为乱码或截断1. 字符集不匹配。2. ClickHouse 表字段长度定义过短。1. 检查 PostgreSQL 和 ClickHouse 的字符集配置。2. 检查query字段的定义类型如String。1. 确保两端使用兼容的字符集如 UTF-8。2.String类型在 ClickHouse 中长度可变通常没问题。数据延迟很高1. 网络延迟大。2. ClickHouse 写入队列堆积。3. Pg_stat_ch 进程被系统调度阻塞。1. 检查网络状况。2. 查看 ClickHouse 的system.metrics表中关于插入的指标。3. 检查系统负载和 Pg_stat_ch 进程状态。1. 优化网络。2. 调整batch_size降低写入频率。3. 为进程分配适当的系统优先级。进程意外退出1. 配置错误导致启动后立即退出。2. 运行时发生 panic内存访问错误等。3. 被系统 OOM Killer 终止。1. 查看程序退出前的日志。2. 检查系统日志如/var/log/messages。3. 检查系统内存使用情况。1. 根据日志修正配置。2. 报告 bug 给开发者。3. 确保系统有足够内存。9. 最佳实践与使用建议为了让 Pg_stat_ch 稳定、高效地运行并最大化其价值遵循以下实践专用监控账户务必为 Pg_stat_ch 创建独立的、权限最小化的数据库账户仅授予查询pg_stat_statements和相关视图的权限切勿使用超级用户。配置文件管理使用版本控制系统如 Git管理配置文件但务必排除密码。密码应通过环境变量或安全的密钥管理服务注入。ClickHouse 表设计优化分区键使用PARTITION BY toYYYYMM(timestamp)按月分区是常见且有效的做法便于数据滚动删除。排序键ORDER BY (timestamp, pg_instance, dbname, queryid)将时间戳放在最前有利于按时间范围快速查询。TTL为表设置 TTL生存时间自动删除过期数据控制存储成本。例如TTL timestamp INTERVAL 90 DAY。监控 Pg_stat_ch 自身使用进程管理工具如 systemd, supervisor运行 Pg_stat_ch并配置重启策略。同时可以将其运行状态如进程是否存在、最后一次成功导出的时间戳纳入你的整体监控体系如 Prometheus。数据安全网络隔离确保 Pg_stat_ch 与 PostgreSQL、ClickHouse 之间的通信网络是受信任的生产环境建议使用内网。查询脱敏如果 SQL 文本可能包含敏感信息考虑在 PostgreSQL 层面或 Pg_stat_ch 中集成脱敏逻辑或在存储到 ClickHouse 后严格控制访问权限。与可视化工具集成数据进入 ClickHouse 后最大的价值在于分析。立即将其与 Grafana 连接开始构建仪表盘。常见的图表包括QPS 趋势图平均/95分位/最大查询耗时趋势图慢查询 TopN 排行榜按数据库、用户分组的资源消耗图建立基线与告警在 Grafana 中基于历史数据建立性能基线。对于关键指标如平均耗时突增、某个查询调用次数异常设置告警规则实现主动监控。10. 总结与下一步Pg_stat_ch 填补了 PostgreSQL 原生监控能力在历史数据存储和灵活分析方面的空白。通过将数据卸载到 ClickHouse它使得对海量 SQL 执行记录进行长期、低成本、高性能的分析成为可能。这个工具最值得尝试的点在于其架构的简洁性和带来的强大分析潜力。它没有重新发明轮子而是巧妙地连接了两个领域的优秀组件。部署成功后你应该立即着手进行以下验证数据流验证确认从 PostgreSQL 到 ClickHouse 的数据链路是稳定、低延迟的。基础分析在 ClickHouse 中尝试几个分析查询例如找出过去一小时内最耗时的 10 个查询感受其分析速度。可视化搭建花半小时在 Grafana 中连接 ClickHouse 数据源创建一个简单的 QPS 和平均延迟图表。最容易踩的坑主要集中在初期配置数据库连接参数错误、ClickHouse 表结构不匹配、权限不足。按照本文的步骤仔细检查每个环节能避开大部分问题。下一步你可以探索更高级的用法扩展监控范围除了pg_stat_statements是否可以导出pg_stat_database、pg_stat_user_tables等其他视图的数据多实例聚合部署多个 Pg_stat_ch 实例分别监控不同的 PostgreSQL 服务器将所有数据写入同一个 ClickHouse 集群实现集中监控。关联分析将 SQL 性能数据与应用程序的链路追踪如 Jaeger、SkyWalking数据在 ClickHouse 中进行关联实现端到端的性能分析。将数据库的运行时细节转化为可查询、可分析的数据资产是提升系统可观测性的关键一步。Pg_stat_ch 为此提供了一个轻量而高效的起点。建议收藏本文在部署和调试时作为参考。