PG开源的巡检工具pg_gather

简介

https://github.com/jobinau/pg_gather

This is a single SQL-only script for gathering performance and configuration data from PostgreSQL databases. And another SQL script available for analyzing and generating detailed HTML reports from the collected data. Yes, everything SQL-Only!, leveraging the features of psql-The command-line utility of PostgreSQL Supported Versions : PostgreSQL 10, 11, 12, 13, 14 & 15 Minimum support versions : PostgreSQL 9.5, 9.6

纯SQL脚本，只生成一个html报告文件。

优缺点

需要导入数据库部分数据，会污染原有数据库，优点是免费开源。

但是我的巡检脚本不会污染原有数据库，但是缺点是收费…..

安装

wget https://codeload.github.com/jobinau/pg_gather/tar.gz/refs/tags/v16  -O pg_gather-16.tar.gz
tar -zxvf pg_gather-16.tar.gz
cd pg_gather-16

报告生成

-- 采集数据
psql -U postgres -h 172.17.0.2  -X -f gather.sql > out.txt

-- 导入数据
sed -e '/^Pager/d; /^Tuples/d; /^Output/d; /^SELECT pg_sleep/d; /^PREPARE/d; /^\s*$/d' out.txt | psql  -U postgres -h 172.17.0.2  -f gather_schema.sql -f -

-- 生成报告
psql  -U postgres -h 172.17.0.2  -X -f gather_report.sql > GatherReport.html

报告使用浏览器打开GatherReport.html即可。

结果

小麦苗PG巡检脚本

请参考：https://www.xmmup.com/pgshujukuxunjianjiaoben.html