pg_gather: 用于 PostgreSQL 健康巡检的 SQL 脚本

十二月 26, 2023

摘要：扫描 PostgreSQL 实例以查找潜在的问题。pg_gather是基于 psql 内置功能的纯 SQL 脚本。

目录

介绍

扫描和收集最少的数据量，用于识别 PostgreSQL 数据库中的潜在问题，然后使用这些数据生成分析报告。本项目为用户提供了两个 SQL 脚本：

  • gather.sql：从 PostgreSQL 数据库收集性能和配置数据。
  • gather_report.sql：分析收集的数据，并生成详细的 HTML 报告。

一切都是纯 SQL，完全基于 PostgreSQL 命令行工具 psql 的内置功能。

亮点

  1. 安全开放：

    简单、透明、完全可审计的代码。

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

  2. 无可执行文件：

    无需在数据库主机上部署任何可执行文件。

    在许多高度安全的环境中，在安全环境中使用可执行文件会带来不可接受的风险。pg_gather只需要标准的 PostgreSQL 命令行工具psql，不需要其他库或可执行文件。

  3. 与身份验证方式无关

    PostgreSQL 支持的任何身份验证机制，都适用于pg_gather中的数据收集，因为它使用标准的psql命令行工具。

  4. 任何操作系统

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

  5. 与 CPU 架构无关

    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会尽可能避免收集数据的冗余。

数据收集

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

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

或者也可以，通过管道传送给压缩工具以生成压缩后的输出，如下所示：

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

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

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

  4. PostgreSQL 的 Docker 容器可能不包含下载gather.sql所需的curl或wget工具。因此，建议改为将 SQL 文件的内容通过管道传递给psql。

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

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

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

持续收集部分数据

若要捕获一个偶发问题的详细信息，可能需要多次收集数据。pg_gather有一个特殊的用于持续收集数据的轻量级模式，当它连接到“template1”数据库时会自动启用。您可以规划每分钟对“template1”数据库运行一次gather.sql，并将输出文件收集到一个目录下。

以下是在 Linux/Unix 系统中使用cron进行调度的一个例子：

* * * * * psql -U postgres -d template1 -X -f /path/to/gather.sql | gzip >  /path/to/out/out-`date +\%a-\%H.\%M`.txt.gz 2>&1

连接到template1数据库时，gather 脚本仅收集实时的、动态的、与性能相关的信息，跳过所有特定于对象的信息。这称为部分收集，还可以使用 gzip 进一步压缩以显著减小输出大小。

数据分析

导入收集的数据

收集到的数据可以导入到一个 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 -

生成报告

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

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

您可以使用自己喜欢的 Web 浏览器阅读报告。

备注：需要 PostgreSQL 13 或更高版本才能生成分析报告。

导入部分的数据

如上一节所述，如果我们将gather.sql规划为一个简单的持续监控工具，则部分数据收集会很有帮助。名为history的单独模式可以保存导入的数据。pg_gather提供了一个名为history_schema.sql的脚本文件，用于创建此模式和对象。

psql -X -f history_schema.sql

此项目提供了一个示例imphistory.sh文件，用于自动将多个文件中的部分数据，导入到history模式中的表里。此脚本可以在包含所有输出文件的目录中执行。允许使用多个文件和通配符。下面是一个示例：

$ imphistory.sh out-*.gz > log.txt

如上所示，收集导入日志文件是一种很好的做法。

了解更多

pg_gather 项目

搜索

分类

标签