使用pg_gather巡检数据库

项目地址 https://github.com/jobinau/pg_gather/

亮点

1. 开放安全：简单、透明、完全可审计的代码。 为了确保收集、传输和分析内容的完全透明性，我们使用仅 SQL 的数据收集脚本，并避免使用任何控制结构的程序，从而提高数据收集的可读性和可审计性。这是将数据收集和分析分开的原因之一。

2. 无可执行文件：不需要在数据库主机上部署任何可执行文件， 在安全环境中使用可执行文件会在许多高度安全的环境中带来不可接受的风险。pg_gather只需要psql而不需要其他库或可执行文件。

3. 与身份验证无关 PostgreSQL 支持的任何身份验证机制都适用于 中的数据收集pg_gather，因为它使用标准psql命令行实用程序。

4. 任何操作系统 Linux（32/64 位）、Sun Solaris、Apple macOS 和 Microsoft Windows：pg_gather 可在psql任意可用的地方工作，确保最大程度的可移植性。（Windows 用户请参阅下面的注释部分）

5. 架构无关的 x86-64 位、ARM、Sparc、Power 和其他架构。它psql可在任何地方工作。

6. 可审计且可选屏蔽的数据： pg_gather以制表符分隔值 (TSV) 格式收集数据，便于在共享信息进行分析之前对其进行审查和审计。只需简单几步，即可进行额外的屏蔽或修剪。

7. 任何云/容器/k8s： 可与 AWS RDS、Azure、Google Cloud SQL、本地数据库等配合使用。 （请参阅下面注释部分中有关 Heroku、AWS Aurora、Docker 和 K8s 的具体说明）

8. 零故障设计： pg_gather即使数据收集不完整或者由于权限问题、不可用的表/视图或其他原因而失败，也可以根据可用信息生成报告。

9. 数据收集开销低： 根据设计，数据收集与数据分析是分开的。这样可以在独立的系统上分析收集的数据，以便分析查询不会对关键系统产生不利影响。在大多数情况下，数据收集的开销可以忽略不计。

10. 小型单文件数据转储： 生成尽可能小的文件，以便于gzip传输和存储，pg_gather并尽可能避免收集的数据出现冗余。

如何使用

0. 下载源码

git clone https://github.com/jobinau/pg_gather.git

cd pg_gather

1. 数据收集

要收集配置和性能信息，gather.sql请使用以下命令对数据库运行脚本psql：

psql <connection_parameters_if_any> -X -f gather.sql > out.tsv

注意： 默认是把采集全局指标还有当前库表的信息（不传库名默认采集的postgres库）

或者通过管道传输到压缩实用程序以获取压缩输出，如下所示：

psql <connection_parameters_if_any> -X -f gather.sql | gzip > out.tsv.gz

此脚本可能需要超过 20 秒才能运行，因为它包含休眠/延迟。我们建议以特权用户（例如superuser或rds_superuser）或具有特权的帐户运行该脚本pg_monitor。输出文件包含用于分析的性能和配置数据。

注意

1. Heroku和类似的 DaaS 托管对收集性能数据施加了非常严格的限制。对类似视图的查询pg_statistics可能会在数据收集期间产生错误，但这些错误可以忽略。

2. MS Windows用户！，客户端工具（如pgAdmin）包括psql，可用于pg_gather针对本地或远程数据库运行。例如：

  "C:\Program Files\pgAdmin 4\v4\runtime\psql.exe" -h pghost -U postgres -f gather.sql > out.tsv

1. AWS Aurora提供“与 PostgreSQL 兼容”的数据库。但是，它并不是真正的 PostgreSQL 数据库，尽管它看起来像一个。因此，您应该对gather.sql脚本执行以下操作，将任何不适用的行替换为“NULL”。

  sed -i -e 's/^CASE WHEN pg_is_in_recovery().*/NULL/' gather.sql

1. PostgreSQL 的Docker容器可能不包含curl或wget`` utilities necessary to download gather.sql . Therefore, it is recommended to pipe the contents of the SQL file to psql`。

  cat gather.sql | docker exec -i <container> psql -X -f - > out.tsv

1. Kubernetes 环境也有与 Docker 类似的限制。因此建议采用类似的方法。

  cat gather.sql | kubectl exec -i <PGpod> -- psql -X -f - > out.tsv

持续收集数据

可能需要连续和重复地收集数据。pg_gather有一个用于连续数据收集的特殊轻量级模式，当它连接到“template1”数据库时会自动启用。

请参阅有关连续和重复数据收集的详细文档(https://github.com/jobinau/pg_gather/blob/main/docs/continuous_collection.md)

2.数据分析

2.1 导入收集的数据

可以将收集的数据导入 PostgreSQL 实例。这会在public数据库架构中创建所需的架构对象。 注意：避免将数据导入关键环境/数据库。最好使用临时 PostgreSQL 实例。

psql -f gather_schema.sql -f out.tsv

已弃用以下用法sed：sed -e '/^Pager/d; /^Tuples/d; /^Output/d; /^SELECT pg_sleep/d; /^PREPARE/d; /^\s*$/d' out.tsv | psql -f gather_schema.sql -

2.2 生成报告

可以从导入的数据生成 HTML 格式的分析报告，如下所示。

psql -X -f gather_report.sql > GatherReport.html

您可以使用您最喜欢的网络浏览器阅读该报告。注意：生成分析报告需要 PostgreSQL 13 或更高版本。

样例

git clone https://github.com/jobinau/pg_gather.git

cd pg_gather

# 我这里是切到sbtest库里去采集的数据

psql sbtest -X -f gather.sql > out.tsv

psql sbtest -f gather_schema.sql -f out.tsv   # 这个命令，会在sbtest库下生成29个unlogged的表数据（如果不想把表放到业务库，建议把采集到的数据写到postgres库里或者写到其他的pg实例里）

psql sbtest  -X -f gather_report.sql > GatherReport.html

最终它还会给出一个结论