一款非常强大的PostgreSQL系列数据库信息收集工具—pg-collector

一、功能介绍

1.1、pg-collector信息概览

pg-collector 是一款用于收集 PostgreSQL 数据库信息的工具，它能够以统一的格式展示数据库的各种状态和统计数据。根据搜索结果显示，pg-collector 可以通过执行 SQL 脚本来快速上手使用，并能生成简洁的巡检报告。pg-collector 是纯 SQL 脚本编写而成，用户可以根据自己的需求对其进行定制，增加或调整巡检项目。此外，pg-collector 还提供了对数据库大小、使用情况、表、索引、序列使用情况的统计，以及对数据库负载、vacuum/analyze 操作进度的可视化展示等功能。

1.2、功能概述

PG Collector会收集有价值的数据库信息并将其显示在合并的 HTML 文件中，从而提供了一种方便的方式来查看和浏览报告的不同部分。

PG Collector 可在生产环境中安全运行，并且不会创建任何数据库对象来产生输出。 通过 PG Collector，操作员可以深入了解数据库的各个方面，例如：

• 数据库大小
• 配置参数
• 已安装的扩展
• 清理与统计
• 未使用的索引和无效索引
• 用户和角色信息
• Toast 表映射
• 数据库模式信息
• 碎片化（膨胀）
• 表空间信息
• 内存设置
• 表和索引的大小和信息
• 事务序号
• 复制槽
• Public模式的信息
• 未记录的表
• 更多内容，请查看示例报告

1.3、官网地址

https://github.com/awslabs/pg-collector/releases

二、环境初始化

2.1、安装扩展pg_stat_statements

• 说明：pg_stat_statements是pg-collector收集 PostgreSQL 数据库信息的必备扩展，必须先安装扩展。

• 代码如下：

 [postgres16@Server ~]$ cd /install/postgresql-16.0/contrib/pg_stat_statements/
[postgres16@Server pg_stat_statements]$ make && make install

2.2、下载pg-collector并解压

• 代码如下：

[postgres16@Server install]$ wget https://github.com/awslabs/pg-collector/archive/refs/tags/pg-collector-V1-for-PostgreSQL-16.tar.gz
--2024-07-24 15:19:54--  https://github.com/awslabs/pg-collector/archive/refs/tags/pg-collector-V1-for-PostgreSQL-16.tar.gz
正在解析主机 github.com (github.com)... 20.205.243.166
正在连接 github.com (github.com)|20.205.243.166|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://codeload.github.com/awslabs/pg-collector/tar.gz/refs/tags/pg-collector-V1-for-PostgreSQL-16 [跟随至新的 URL]
--2024-07-24 15:19:55--  https://codeload.github.com/awslabs/pg-collector/tar.gz/refs/tags/pg-collector-V1-for-PostgreSQL-16
正在解析主机 codeload.github.com (codeload.github.com)... 20.205.243.165
正在连接 codeload.github.com (codeload.github.com)|20.205.243.165|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：未指定 [application/x-gzip]
正在保存至: “pg-collector-V1-for-PostgreSQL-16.tar.gz”

pg-collector-V1-for-PostgreSQ     [              <=>                                 ] 334.88K   105KB/s  用时 3.2s

2024-07-24 15:19:59 (105 KB/s) - “pg-collector-V1-for-PostgreSQL-16.tar.gz” 已保存 [342917]

[postgres16@Server install]$




[postgres16@Server install]$ tar xzvf pg-collector-V1-for-PostgreSQL-16.tar.gz
pg-collector-pg-collector-V1-for-PostgreSQL-16/
pg-collector-pg-collector-V1-for-PostgreSQL-16/CHANGELOG.md
pg-collector-pg-collector-V1-for-PostgreSQL-16/CODE_OF_CONDUCT.md
pg-collector-pg-collector-V1-for-PostgreSQL-16/CONTRIBUTING.md
pg-collector-pg-collector-V1-for-PostgreSQL-16/LICENSE
pg-collector-pg-collector-V1-for-PostgreSQL-16/README.md
pg-collector-pg-collector-V1-for-PostgreSQL-16/img/
pg-collector-pg-collector-V1-for-PostgreSQL-16/img/pg_collector_header_V2.6.png
pg-collector-pg-collector-V1-for-PostgreSQL-16/img/pg_collector_logo.png
pg-collector-pg-collector-V1-for-PostgreSQL-16/pg_collector.sql
pg-collector-pg-collector-V1-for-PostgreSQL-16/sample_reports/
pg-collector-pg-collector-V1-for-PostgreSQL-16/sample_reports/pg_collector_testdb-2020-10-16_021546.html
[postgres16@Server install]$

2.3、安装扩展件pg_stat_statements

• 代码如下：

[postgres16@Server install]$ /postgres16/server/bin/psql
psql (16.0)
Type "help" for help.

postgres16=#
postgres16=# create  extension pg_stat_statements;
CREATE EXTENSION
postgres16=#

2.4、修改postgresql.conf，配置扩展插件信息

vi /postgres16/data/postgresql.conf

shared_preload_libraries = 'pg_stat_statements' 

2.5、重启PostgreSQL数据库服务

• 代码如下：

[postgres16@Server install]$ /postgres16/server/bin/pg_ctl -D /postgres16/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2024-07-24 15:24:30.655 CST [70494] LOG:  redirecting log output to logging collector process
2024-07-24 15:24:30.655 CST [70494] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres16@Server install]$

2.6、运行 PG Collector 脚本（pg_collector.sql），收集数据库信息

• 说明：PG Collector 脚本将在/tmp目录下生成 HTML 文件。

方法1：

postgres16=#
postgres16=# \i /install/pg-collector-pg-collector-V1-for-PostgreSQL-16/pg_collector.sql
Output format is aligned.
Report name and location: /tmp/pg_collector_postgres16-2024-07-24_162158.html
postgres16=#

方法2：

[postgres16@Server pg-collector-pg-collector-V1-for-PostgreSQL-16]$ /postgres16/server/bin/psql -dpostgres16 -U postgres16 -f /install/pg-collector-pg-collector-V1-for-PostgreSQL-16/pg_collector.sql
Output format is html.
Report name and location: /tmp/pg_collector_postgres16-2024-07-24_164707.html

2.7、查看报告信息，下载收集的数据库HTML文件，在浏览器打开。

2.8、设置生成报告的时间，超时自动终止收集报告。

说明： 如果数据库有数万个表，某些查询可能会花费更长的时间。使用statement_timeout来中止任何花费超过指定毫秒数的语句。请查看下面的示例。

• 代码如下：

[postgres16@Server ~]$ /postgres16/server/bin/psql -dpostgres16 -U postgres16 -f /install/pg-collector-pg-collector-V1-for-PostgreSQL-16/pg_collector.sql  set statement_timeout=3
psql: warning: extra command-line argument "set" ignored
psql: warning: extra command-line argument "statement_timeout=3" ignored
Output format is html.
Report name and location: /tmp/pg_collector_postgres16-2024-07-24_174115.html

2.9、问题解决：

• 错误提示：psql:pg_collector.sql:923: error: Did not find any settings.

• 代码如下：

postgres16=# \d
                  List of relations
 Schema |          Name           | Type |   Owner
--------+-------------------------+------+------------
 public | pg_stat_statements      | view | postgres16
 public | pg_stat_statements_info | view | postgres16
(2 rows)

postgres16=#
postgres16=# \i pg_collector.sql
Output format is html.
Report name and location: /tmp/pg_collector_postgres16-2024-07-24_154704.html
psql:pg_collector.sql:923: error: Did not find any settings.
postgres16=#

• 解决如下：

--说明：注释 --\dres

\qecho <a name="Users_Roles_Info"></a>
\qecho <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#16191f"><b>Users & Roles Info</b></font><hr align="left" width="460">
\qecho <br>
\qecho <details>
\du
\qecho <br>
-- list of per database role settings (settings set at the role level)
--\drds
\qecho <br>
select * FROM pg_user;
\qecho </details>

三、使用教程

• 使用 pg-collector 进行数据库监控和性能分析的一般流程如下：

• 确认 PostgreSQL 数据库的安装和配置符合要求。
• 执行 pg_collector.sql 脚本，生成初始报告。
• 根据报告中的信息，分析数据库的性能状况，找出可能的性能瓶颈。
• 根据分析结果，调整数据库配置或执行必要的维护操作，如优化查询、调整索引等。
• 定期运行 pg-collector，监控数据库的变化趋势，预防潜在的问题。

四、总结

• pg-collector 是一个强大且易于使用的 PostgreSQL 数据库监控和性能分析工具，它可以帮助数据库管理员和开发人员更好地理解和优化数据库的性能。通过定期使用 pg-collector，可以有效保障数据库的健康状态，提升应用的稳定性和响应速度。

更多资料，请关注博主其他平台：

墨天轮主页：

https://www.modb.pro/topic/659255

链接二维码如下：

PGFans社区主页

https://pgfans.cn/user/home?userId=5710

公众号：