PG监控工具之pgcenter

简介

GitHub：https://github.com/lesovsky/pgcenter

pgCenter is a command-line admin tool for observing and troubleshooting Postgres.

pgCenter's main goal is to help Postgres DBA working with statistics and provide a convenient way to observe Postgres in runtime.

安装

普通安装使用

可以tar.gz也可以rpm包：

Docker使用

地址：https://hub.docker.com/r/lesovsky/pgcenter

使用过程：

输入密码后就可以返回如下结果：

帮助命令

pgcenter top命令常用的指令选项

使用pgcenter top命令进行监控，默认是一秒刷新一次

• h,F1 查看帮助
• q,‘ESC’,‘Ctrl+Q’ 退出
• z 设置刷新间隔，默认是1秒，可调范围是1到300
• Q 重置数据库统计信息
• ‘-’ 中划线符号，输入pid，取消查询，相当于调用pg_cancel_backend函数
• ‘_’ 下划线符号，输入pid，取消查询，相当于调用pg_terminate_backend函数
• B 同时查看磁盘信息，交替按会取消。
• N 同时查看网络接口信息，交替按会取消。
• L 查看数据库日志尾部行
• l 查看数据库的日志信息，翻页形式
• ‘~’ 打开psql会话，\q退出到pgcenter界面
• C、E、R 显示、编辑、重载PG配置参数
• a、d、f、r 活动连接信息, 数据库信息, 函数信息, 流复制信息
• s、t、i 分别显示表大小、表统计信息、索引统计信息
• x、X 小写x交替显示不同维度pg_stat_statements信息，大写X下拉选取pg_stat_statements 菜单
• p、P 小写p交替显示pg_statprogress信息,大写P下拉选取pg_statprogress菜单
• Up、Down 上下键调整列的宽度
• Left,Right,<,/ 左右键选择列, 左方括号交替排序, '/'过滤

pgcenter top示例

先使用pgbench压测，准备环境

使用pgcenter top命令进行监控，默认是一秒刷新一次

按下“ctrl+c”结束监控。

官方示例

配置

README: pgcenter config

参考：https://github.com/lesovsky/pgcenter/blob/master/doc/pgcenter-config-readme.md

pgcenter config is a supplementary tool which allows management of pgCenter’s additional SQL functions.

• General information
• Main functions
• Usage

General information

As mentioned earlier, pgCenter tracks system's usage through local procfs filesystem. It works very well when pgCenter runs on the same host with Postgres, however, what do you do if you want to run pgCenter on your laptop and connect it to a remote Postgres on a far datacenter?

It's not an issue and pgCenter can track remote system statistics through established Postgres connection using pgCenter's own SQL functions. All you need is to install Postgres built-in procedural language and pgCenter's functions into a remote database and connect as usual.

Note: when pgCenter runs on the same host with Postgres, it reads stats directly from /proc and doesn't use Postgres connection for reading system stats.

Installing and removing functions is possible with pgcenter config, however, with few limitations:

• plperlu (which means untrusted plperl) procedural language must be installed manually in the database you want to connect pgCenter to (see details here).
• perl modules Linux::Ethtool::Settings, Filesys::Df should be installed in the system, it's used to get stats about network interfaces and mounted filesystems needed to calculate metrics.

Main functions

• installing and removing SQL functions and views in a desired database.

Usage

Run config command and install stats schema into a database:

If Linux::Ethtool::Settings module is not installed in the system, pgcenter config -i will fail with the following error:

As you can see the problem is related to pgcenter.get_netdev_link_settings() function which depends on Linux::Ethtool::Settingsmodule. To fix the issue you need to install the module into your system.

In general, a preferred way is installing dependencies using distro's default package manager, but it might happen that Linux::Ethtool::Settings will not be available in the official package repo. In this case, you can install perl module using CPAN, but extra dependencies would have to be resolved, such as make,gcc and others.

Here is a complete example reproduced in Docker environment using using official docker image for postgresql.

As you can see, finally function pgcenter.get_netdev_link_settings() works well.

Perhaps it’s possible to use the same approach in other distros, because of perl module name is the same, but names of other packages may vary (eg. postgresql-10-plperl instead of postgresql-plperl).

Other notes

Of course, pgcenter top can also work with remote Postgres which don't have these SQL functions installed. In this case zeroes will be shown in the system stats interface (load average, cpu, memory, swap, io, network) and multiple errors will appear in Postgres log. For easier distribution, SQL functions and views used by pgCenter are hard-coded into the source code, but their usage is not limited, so feel free to use it.

Another limitation is related to procfs filesystem, which is Linux-specific file system, hence there might be problematic to run pgCenter on operation systems other than Linux. But you can still run pgCenter in Docker.

See other usage examples here.