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 秒以上,因为它包含了适当的睡眠/延迟动作。建议以特权用户(如superuserrds_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所需的curlwget工具。因此,建议改为将 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

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