暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL运维—pg_top安装及使用说明

PostgreSQL运维—pg_top安装及使用说明

原创 李先生 2022-02-11
5449

我们在操作系统上经常喜欢用top命令，使用top命令查看操作系统层面的资源使用情况。我们使用top命令查看信息的时候，并没有数据库中的一些进程信息，与数据库进程信息无法紧密结合。

所以我们可以使用pg_top工具来查看，比如top也有一些衍生的工具（单独监控IO、CPU等）。在数据库监控层，我们可以组合的使用pg_top命令，可以将io、iops，内存，CPU 等信息结合起来查看。

今天我们来学习一下pg_top工具的安装及使用。

pg_top 说明

pg_top 是 PostgreSQL 的’top’。它源自 Unix Top。与 top 类似，pg_top 允许您监控 PostgreSQL 进程。它还允许您：

  • 查看进程当前正在运行的 SQL 语句。
  • 查看当前正在运行的 SELECT 语句的查询计划。
  • 查看进程持有的锁。
  • 查看每个进程的 I/O 统计信息。
  • 查看下游节点的复制统计信息。

pg_top 安装

软件下载

安装包下载地址：
墨天轮地址：https://cdn.modb.pro/download/357651
gitlb地址：https://gitlab.com/pg_top/pg_top
gihub地址：https://github.com/markwkm/pg_top

安装准备

解压压缩包

[postgres@lyp ~]$ ls -rlt pg_top-master.zip 
-rw-r--r--. 1 postgres postgres 189401 Feb 10 14:30 pg_top-master.zip
[postgres@lyp ~]$ unzip pg_top-master.zip 
Archive:  pg_top-master.zip
fb32e974ee21f2f9af58f08bbdfe53b38a57207f
   creating: pg_top-master/
  inflating: pg_top-master/.gitignore  
..........
  inflating: pg_top-master/version.c  
  inflating: pg_top-master/version.h  
[postgres@lyp ~]$

要编译并安装“pg_top”，请阅读文件“INSTALL.rst”，然后按照其中包含的指示和建议进行编译安装pg_top。

查看安装文件

[postgres@lyp pg_top-master]$ more INSTALL.rst 
pg_top
======
Installation
------------
Configuring
~~~~~~~~~~~
::
  cmake [options] CMakeLists.txt
options:
-DCMAKE_INSTALL_PREFIX=PREFIX   Install files in PREFIX.  Default is
                                '/usr/local'.
-DENABLE_COLOR=0                Default on.  Include code that allows for the
                                use of color in the output display.  Use
                                -DENABLE_COLOR=0 if you do not want this
                                feature compiled in to the code.  The configure
                                script also recognizes the spelling "colour".
Installing
~~~~~~~~~~
::
  make install
Uninstalling
~~~~~~~~~~~~
::
  xargs rm < install_manifest.txt
[postgres@lyp pg_top-master]$

配置安装路径并检查

安装路径选择postgresql软件安装路径：/opt/pgsql14.1/

[postgres@lyp pg_top-master]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pgsql14.1/ CMakeLists.txt 
bash: cmake: command not found...
Similar command is: 'make'
[postgres@lyp pg_top-master]$

缺少cmake工具，安装cmake工具，后重新安装

[root@lyp ~]# yum -y install cmake
[root@lyp ~]# su - postgres
Last login: Thu Feb 10 19:53:18 CST 2022 on pts/5
[postgres@lyp ~]$ cd pg_top-master/
[postgres@lyp pg_top-master]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pgsql14.1/ CMakeLists.txt 
-- The C compiler identification is GNU 4.8.5
-- Check for working C compiler: /bin/cc
-- Check for working C compiler: /bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- machine - linux
-- arch - x86_64
-- pg_config --includedir - /opt/pgsql14.1/include
-- pg_config --libdir - /opt/pgsql14.1/lib
-- Looking for 4 include files stdlib.h, ..., float.h
-- Looking for 4 include files stdlib.h, ..., float.h - found
-- Looking for include file string.h
-- Looking for include file string.h - found
-- Looking for include file strings.h
-- Looking for include file strings.h - found
-- Looking for include files sys/time.h, time.h
-- Looking for include files sys/time.h, time.h - found
-- Looking for include file sys/time.h
-- Looking for include file sys/time.h - found
-- Looking for include file sys/resource.h
-- Looking for include file sys/resource.h - found
-- Looking for include file unistd.h
-- Looking for include file unistd.h - found
-- Looking for getopt
-- Looking for getopt - found
-- Looking for memcpy
-- Looking for memcpy - found
-- Looking for setpriority
-- Looking for setpriority - found
-- Looking for sigaction
-- Looking for sigaction - found
-- Looking for sighold
-- Looking for sighold - found
-- Looking for sigprocmask
-- Looking for sigprocmask - found
-- Looking for sigrelse
-- Looking for sigrelse - found
-- Looking for snprintf
-- Looking for snprintf - found
-- Looking for strchr
-- Looking for strchr - found
-- Looking for strerror
-- Looking for strerror - found
-- Performing Test SIGNAL_RETURN
-- Performing Test SIGNAL_RETURN - Failed
-- Performing Test TIME_T_DEFINED
-- Performing Test TIME_T_DEFINED - Success
-- Configuring done
-- Generating done
-- Build files have been written to: /home/postgres/pg_top-master
[postgres@lyp pg_top-master]$

安装软件

问题1

[postgres@lyp pg_top-master]$ make install
Scanning dependencies of target pg_top
[  7%] Building C object CMakeFiles/pg_top.dir/color.c.o
[ 15%] Building C object CMakeFiles/pg_top.dir/commands.c.o
[ 23%] Building C object CMakeFiles/pg_top.dir/display.c.o
[ 30%] Building C object CMakeFiles/pg_top.dir/getopt.c.o
[ 38%] Building C object CMakeFiles/pg_top.dir/screen.c.o
[ 46%] Building C object CMakeFiles/pg_top.dir/sprompt.c.o
[ 53%] Building C object CMakeFiles/pg_top.dir/pg.c.o
[ 61%] Building C object CMakeFiles/pg_top.dir/pg_top.c.o
[ 69%] Building C object CMakeFiles/pg_top.dir/utils.c.o
[ 76%] Building C object CMakeFiles/pg_top.dir/version.c.o
[ 84%] Building C object CMakeFiles/pg_top.dir/machine/m_remote.c.o
/home/postgres/pg_top-master/machine/m_remote.c:14:24: fatal error: bsd/stdlib.h: No such file or directory
 #include <bsd/stdlib.h>
                        ^
compilation terminated.
make[2]: *** [CMakeFiles/pg_top.dir/machine/m_remote.c.o] Error 1
make[1]: *** [CMakeFiles/pg_top.dir/all] Error 2
make: *** [all] Error 2
[postgres@lyp pg_top-master]$

在执行make的过程中，遇到了一个报错。在centos/redhat系统都会报这个错误。该错误提示找不到bsd/stdlib.h。可以根据报错大致判断是因为缺少bsd的lib包导致的。

安装libbsd包
libbsd下载地址：https://cdn.modb.pro/download/359238
libbsd-devel下载地址：https://cdn.modb.pro/download/359481

[root@lyp ~]# rpm -ivh libbsd-0.8.3-1.el7.x86_64.rpm
warning: libbsd-0.8.3-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libbsd-0.8.3-1.el7               ################################# [100%]
[root@lyp ~]# rpm -ivh libbsd-devel-0.8.3-1.el7.x86_64.rpm
warning: libbsd-devel-0.8.3-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libbsd-devel-0.8.3-1.el7         ################################# [100%]
[root@lyp ~]#

问题2

此时重新安装不能重复执行make install继续安装。否则会报以下报错。

[postgres@lyp pg_top-master]$ make install
[  7%] Building C object CMakeFiles/pg_top.dir/machine/m_remote.c.o
[ 15%] Building C object CMakeFiles/pg_top.dir/machine/m_common.c.o
[ 23%] Building C object CMakeFiles/pg_top.dir/machine/m_linux.c.o
Linking C executable pg_top
CMakeFiles/pg_top.dir/machine/m_remote.c.o: In function `get_process_info_r':
m_remote.c:(.text+0x2d8c): undefined reference to `reallocarray'
CMakeFiles/pg_top.dir/machine/m_linux.c.o: In function `get_process_info':
m_linux.c:(.text+0x2d06): undefined reference to `reallocarray'
collect2: error: ld returned 1 exit status
make[2]: *** [pg_top] Error 1
make[1]: *** [CMakeFiles/pg_top.dir/all] Error 2
make: *** [all] Error 2
[postgres@lyp pg_top-master]$

重新安装

此时需要把之前的安装目录清理掉，重新安装。

[postgres@lyp pg_top-master]$ cd ..
[postgres@lyp ~]$ rm -rf pg_top-master
[postgres@lyp ~]$ unzip pg_top-master.zip 
Archive:  pg_top-master.zip
fb32e974ee21f2f9af58f08bbdfe53b38a57207f
   creating: pg_top-master/
  inflating: pg_top-master/.gitignore  
..........
[postgres@lyp ~]$ cd pg_top-master/
[postgres@lyp pg_top-master]$ cmake -DCMAKE_INSTALL_PREFIX=/opt/pgsql14.1/ CMakeLists.txt 
-- The C compiler identification is GNU 4.8.5
-- Check for working C compiler: /bin/cc
..........
[postgres@lyp pg_top-master]$ make install
Scanning dependencies of target pg_top
[  7%] Building C object CMakeFiles/pg_top.dir/color.c.o
[ 15%] Building C object CMakeFiles/pg_top.dir/commands.c.o
[ 23%] Building C object CMakeFiles/pg_top.dir/display.c.o
[ 30%] Building C object CMakeFiles/pg_top.dir/getopt.c.o
[ 38%] Building C object CMakeFiles/pg_top.dir/screen.c.o
[ 46%] Building C object CMakeFiles/pg_top.dir/sprompt.c.o
[ 53%] Building C object CMakeFiles/pg_top.dir/pg.c.o
[ 61%] Building C object CMakeFiles/pg_top.dir/pg_top.c.o
[ 69%] Building C object CMakeFiles/pg_top.dir/utils.c.o
[ 76%] Building C object CMakeFiles/pg_top.dir/version.c.o
[ 84%] Building C object CMakeFiles/pg_top.dir/machine/m_remote.c.o
[ 92%] Building C object CMakeFiles/pg_top.dir/machine/m_common.c.o
[100%] Building C object CMakeFiles/pg_top.dir/machine/m_linux.c.o
Linking C executable pg_top
[100%] Built target pg_top
Install the project...
-- Install configuration: ""
-- Installing: /opt/pgsql14.1/bin/pg_top
-- Installing: /opt/pgsql14.1/share/man/man1/pg_top.1
[postgres@lyp pg_top-master]$

pg_top 使用

帮助说明

[postgres@lyp pg_top-master]$ pg_top --help
pg_top monitors a PostgreSQL database cluster.

Usage:
  pg_top [OPTION]... [COUNT]

General options:
  -b, --batch               use batch mode		--使用batch模式
  -c, --show-command        display command name of each process	--显示每个进程的命令名
  -C, --color-mode          turn off color mode		--关闭颜色模式
  -i, --interactive         use interactive mode		--使用交互模式
  -I, --hide-idle           hide idle processes		--隐藏空闲进程
  -n, --non-interactive     use non-interactive mode		--使用非交互模式
  -o, --order-field=FIELD   select sort order		--选择排序顺序
  -r, --remote-mode         activate remote mode		--启动远程模式
  -R                        display replication stats		--显示复制统计信息
  -s, --set-delay=SECOND    set delay between screen updates		--设置屏幕更新之间的延迟
  -T, --show-tags           show color tags		--显示颜色标签
  -V, --version             output version information, then exit		--输出版本信息，然后退出
  -x, --set-display=COUNT   set maximum number of displays		--设置最大显示数量，达到此数后退出
                            exit once this number is reached		
  -X                        display i/o stats		--显示i/o统计数据
  -z, --show-username=NAME  display only processes owned by given username	--仅显示给定进程所拥有的进程用户名
  -?, --help                show this help, then exit		

Connection options:
  -d, --dbname=DBNAME       database to connect to
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -W, --password            force password prompt, and persistent connection
[postgres@lyp pg_top-master]$

使用说明

通过pg_top可以监控主机的负载情况。包括CPU、内存、SWAP交换分区。以及PG进程信息。
监控时，我们可以关注主机的负载情况，也可以看进程的一些信息（XTIME/QTIME/LOCKS等信息）
这是一个动态的展示过程

[postgres@lyp ~]$ pg_top
last pid: 46448;  load avg:  0.00,  0.01,  0.05;       up 0+15:50:08                                                                                                                                                            21:03:14
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states:  0.0% user,  0.0% nice,  0.0% system,  100% idle,  0.0% iowait
Memory: 2933M used, 7047M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  46449 postgres    273M 7352K active   0:00   0:00   0.0     8 postgres: postgres postgres [local] idle
  44513             272M 2192K          0:00   0:00   0.0     0 postgres: autovacuum launcher 
  45597 replxs      272M 3200K idle     0:00   0:00   0.0     0 postgres: replxs postgres [local] idle
  44511             272M 2136K          0:00   0:00   0.0     0 postgres: background writer 
  45318 replxs      272M 2640K active   0:00   0:00   0.0     0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24000148
  44517 postgres    283M   15M idle     0:00   0:00   1.0     0 postgres: postgres postgres 192.168.60.1(56947) idle
  44514             271M 1112K          0:00   0:00   0.0     0 postgres: archiver last was 000000010000000000000023.00000028.backup
  44512             271M 5036K          0:00   0:00   0.0     0 postgres: walwriter        
  44516 postgres    272M 1592K          0:00   0:00   0.0     0 postgres: logical replication launcher 
  44510             272M 2556K          0:00   0:00   0.0     0 postgres: checkpointer

系统负载

load avg: 0.00, 0.01, 0.05;

进程数

10 processes: 6 other background task(s), 2 idle, 2 active

进程数量：10 ，后台进程：6 ，idle进程：2 ，活动进程：2

系统CPU情况

CPU states: 0.0% user, 0.0% nice, 0.0% system, 100% idle, 0.0% iowait

系统内存情况

Memory: 2933M used, 7047M free, 0K shared, 3664K buffers, 1572M cached

SWAP情况

Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

PG进程信息

列名 信息
PID 进程的pid
USERNAME 用户名
SIZE 进程使用内存
RES 常驻内存大小
STATE 状态
XTIME 事务时间
QTIME query执行时间
%CPU 占用Cpu百分比
LOCKS 持有锁数量
COMMAND 操作命令

远程监控

Connection options:
-d, --dbname=DBNAME       database to connect to
-h, --host=HOSTNAME       database server host or socket directory
-p, --port=PORT           database server port
-U, --username=USERNAME   user name to connect as
-W, --password            force password prompt, and persistent connection

监控remote主机的信息时，需要对remote主机上安装pg_proctab插件，只有安装插件才能在remote主机上进行pg_top命令的使用。

插件安装

在remote主机上安装pg_proctab插件

插件下载
墨天轮地址：https://www.modb.pro/download/430532
gitlb地址：https://gitlab.com/pg_proctab/pg_proctab
gihub地址：https://github.com/markwkm/pg_proctab
插件安装
[postgres@lyp ~]$ ls -lrt pg_proctab-main.zip 
-rw-r--r--. 1 postgres postgres 19062 Feb 10 14:30 pg_proctab-main.zip
[postgres@lyp ~]$ unzip pg_proctab-main.zip 
Archive:  pg_proctab-main.zip
e64333e8355586efb4c3fa2fced992450ab41795
   creating: pg_proctab-main/
..........
  inflating: pg_proctab-main/src/pg_proctab.c  
  inflating: pg_proctab-main/src/pg_proctab.h  
[postgres@lyp ~]$ cd pg_proctab-main/
[postgres@lyp pg_proctab-main]$ make && make install
cp sql/pg_proctab.sql sql/pg_proctab--0.0.9.sql
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal  -D_GNU_SOURCE   -c -o src/pg_proctab.o src/pg_proctab.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC src/pg_proctab.o -L/opt/pgsql14.1/lib   -Wl,--as-needed -Wl,-rpath,'/opt/pgsql14.1/lib',--enable-new-dtags  -shared -o src/pg_proctab.so
/usr/bin/mkdir -p '/opt/pgsql14.1/share/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/share/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/lib'
/usr/bin/mkdir -p '/opt/pgsql14.1/share/doc/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/bin'
/usr/bin/install -c -m 644 .//pg_proctab.control '/opt/pgsql14.1/share/extension/'
/usr/bin/install -c -m 644 .//sql/pg_proctab--0.0.5--0.0.6.sql .//sql/pg_proctab--0.0.9.sql  '/opt/pgsql14.1/share/extension/'
/usr/bin/install -c -m 755  src/pg_proctab.so '/opt/pgsql14.1/lib/'
/usr/bin/install -c -m 644 .//doc/README.pg_proctab '/opt/pgsql14.1/share/doc/extension/'
/usr/bin/install -c -m 755 .//contrib/ps-io-utilization.sh .//contrib/ps-processor-utilization.sh .//contrib/ps-util.pl .//contrib/ps-report.pl '/opt/pgsql14.1/bin/'
[postgres@lyp pg_proctab-main]$ psql
psql (14.1)
Type "help" for help.

postgres=# create extension pg_proctab ;
CREATE EXTENSION
postgres=#

远程监控

[postgres@lyp pgdata-14]$ pg_top -U postgres -d postgres  -h 192.168.60.190 -p 5433
last pid: 48082;  load avg:  0.00,  0.01,  0.05;       up 0+16:15:49                                                                                                                                                            21:28:54
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states:  0.0% user,  0.0% nice,  0.5% system, 99.5% idle,  0.0% iowait
Memory: 2937M used, 7043M free, 0K shared, 3664K buffers, 1575M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  48083 postgres    273M 7328K active   0:00   0:00   0.0     8 postgres: postgres postgres 192.168.60.190(50334) idle
  44513             272M 2432K          0:00   0:00   0.0     0 postgres: autovacuum launcher 
  45597 replxs      272M 3200K idle     0:00   0:00   0.0     0 postgres: replxs postgres [local] idle
  44511             272M 2608K          0:00   0:00   0.0     0 postgres: background writer 
  45318 replxs      272M 3028K active   0:00   0:00   0.0     0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24011DF0
  44517 postgres    283M   15M idle     0:00   0:00   0.0     0 postgres: postgres postgres 192.168.60.1(56947) idle
  44514             271M 1392K          0:00   0:00   0.0     0 postgres: archiver last was 000000010000000000000023.00000028.backup
  44512             272M 5556K          0:00   0:00   0.0     0 postgres: walwriter        
  44516 postgres    272M 2004K          0:00   0:00   0.0     0 postgres: logical replication launcher 
  44510             272M 3152K          0:00   0:00   0.0     0 postgres: checkpointer

常用参数说明

参数：-X

display i/o stats 显示i/o统计数据

展示PostgreSQL数据库每个进程的I/O信息。例如iops、Reads、Writes。能够监控到高耗I/O的进程。

[postgres@lyp ~]$ pg_top -X
last pid: 44147;  load avg:  0.00,  0.01,  0.05;       up 0+15:21:14                                                                                                                                                            20:34:19
15 processes: 6 other background task(s), 7 idle, 1 active, 1 idle txn
CPU states:  0.2% user,  0.0% nice,  0.6% system, 99.2% idle,  0.0% iowait
Memory: 2703M used, 7277M free, 0K shared, 3664K buffers, 1340M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID    IOPS   IORPS   IOWPS READS WRITES COMMAND
  44148      10       0      10    0B     0B postgres: postgres postgres [local] idle
  20646       0       0       0    0B     0B postgres: archiver         
  43084       0       0       0    0B     0B postgres: postgres mydb 192.168.60.1(51762) idle
  20648       0       0       0    0B     0B postgres: logical replication launcher 
  43059       9       0       9    0B     0B postgres: postgres postgres 192.168.60.1(51711) idle
  44020       0       0       0    0B     0B postgres: postgres postgres [local] idle
  20645       0       0       0    0B     0B postgres: autovacuum launcher 
  43053       0       0       0    0B     0B postgres: lxs postgres 192.168.60.1(51689) idle
  20642       0       0       0    0B     0B postgres: checkpointer     
  20644       0       0       0    0B     0B postgres: walwriter        
  20643       0       0       0    0B     0B postgres: background writer 
  43071       0       0       0    0B     0B postgres: lxs mydb 192.168.60.1(51749) idle
  43086       0       0       0    0B     0B postgres: postgres mydb1 192.168.60.1(51766) idle
  42743       0       0       0    0B     0B postgres: postgres postgres [local] idle in transaction
  43064       0       0       0    0B     0B postgres: lxs mydb1 192.168.60.1(51730) idle

参数：-R

display replication stats 显示复制统计信息

监控主从复制信息。监控主从延迟、监控主从的lsn的位置。这个跟在数据库里面查询pg_stat_replication一样的数据。

[postgres@lyp ~]$ pg_top -R
last pid: 45377;  load avg:  0.00,  0.01,  0.05;       up 0+15:35:10                                                                                                                                                            20:48:15
1 processes: 
CPU states:  0.0% user,  0.0% nice,  0.0% system,  100% idle,  0.0% iowait
Memory: 2931M used, 7049M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME APPLICATION          CLIENT STATE     PRIMARY    SENT       WRITE      FLUSH      REPLAY      SLAG  WLAG  FLAG  RLAG
  45318 replxs   walreceiver  192.168.60.190 streaming 0/24000148 0/24000148 0/24000148 0/24000148 0/24000148    0B    0B    0B    0B

postgres=# select * from pg_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr   | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | 
replay_lag | sync_priority | sync_state |          reply_time           
-------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+-
-----------+---------------+------------+-------------------------------
 45318 |    24749 | replxs  | walreceiver      | 192.168.60.190 |                 |       50318 | 2022-02-10 20:47:27.588443+08 |              | streaming | 0/24000148 | 0/24000148 | 0/24000148 | 0/24000148 |           |           | 
           |             0 | async      | 2022-02-10 20:48:57.789438+08
(1 row)

postgres=#

参数：-z

–show-username=NAME display only processes owned by given username --仅显示给定进程所拥有的进程用户名

如果数据库里面的用户比较多，也可以按用户做过滤。可以只监控replxs，监控这个用户会话连接的相关信息。

[postgres@lyp ~]$ pg_top -z replxs
last pid: 45603;  load avg:  0.00,  0.01,  0.05;       up 0+15:38:27                                                                                                                                                            20:51:33
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states:  0.5% user,  0.0% nice,  0.5% system, 99.0% idle,  0.0% iowait
Memory: 2932M used, 7048M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  45597 replxs      272M 3200K idle     0:00   0:00   0.0     0 postgres: replxs postgres [local] idle
  45318 replxs      272M 2640K active   0:00   0:00   0.0     0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24000148

参数：-o

–order-field=FIELD select sort order --选择排序顺序

如果想要对显示出来的数据进行排序，比如对xtime/qtime进行排序

注意：列名需要使用小写

[postgres@lyp ~]$ pg_top -o xtime
last pid: 45681;  load avg:  0.05,  0.03,  0.05;       up 0+15:39:41                                                                                                                                                            20:52:46
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states:  0.0% user,  0.0% nice,  0.5% system, 99.5% idle,  0.0% iowait
Memory: 2933M used, 7047M free, 0K shared, 3664K buffers, 1572M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  45682 postgres    273M 7352K active   0:00   0:00   0.0     8 postgres: postgres postgres [local] idle
  45597 replxs      272M 3200K idle     0:00   0:00   0.0     0 postgres: replxs postgres [local] idle
  45318 replxs      272M 2640K active   0:00   0:00   0.0     0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24000148
  44517 postgres    283M   15M idle     0:00   0:00   1.0     0 postgres: postgres postgres 192.168.60.1(56947) idle
  44513             272M 2192K          0:00   0:00   0.0     0 postgres: autovacuum launcher 
  44511             272M 2136K          0:00   0:00   0.0     0 postgres: background writer 
  44514             271M 1112K          0:00   0:00   0.0     0 postgres: archiver last was 000000010000000000000023.00000028.backup
  44512             271M 5036K          0:00   0:00   0.0     0 postgres: walwriter        
  44516 postgres    272M 1592K          0:00   0:00   0.0     0 postgres: logical replication launcher 
  44510             272M 2556K          0:00   0:00   0.0     0 postgres: checkpointer

参数：-x

set maximum number of displays exit once this number is reached --设置最大显示数量，达到此数后退出
适用于输出结果至文本，长期记录。

[postgres@lyp ~]$ pg_top -b  -x 20 > pg_top.log
[postgres@lyp ~]$ more pg_top.log 
last pid: 47809;  load avg:  0.00,  0.01,  0.05;       up 0+16:11:42   21:24:47
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states:  0.0% user,  0.0% nice,  0.5% system, 99.0% idle,  0.5% iowait
Memory: 2936M used, 7044M free, 0K shared, 3664K buffers, 1575M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  47810 postgres    273M 7352K active   0:00   0:00   0.0     8 postgres: postgres postgres [local] idle
  44513             272M 2192K          0:00   0:00   0.0     0 postgres: autovacuum launcher 
  45597 replxs      272M 3200K idle     0:00   0:00   0.0     0 postgres: replxs postgres [local] idle
  44511             272M 2136K          0:00   0:00   0.0     0 postgres: background writer 
  45318 replxs      272M 2896K active   0:00   0:00   0.0     0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24011D08
  44517 postgres    283M   15M idle     0:00   0:00   0.0     0 postgres: postgres postgres 192.168.60.1(56947) idle
  44514             271M 1112K          0:00   0:00   0.0     0 postgres: archiver last was 000000010000000000000023.00000028.backup
  44512             271M 5036K          0:00   0:00   0.0     0 postgres: walwriter        
  44516 postgres    272M 1592K          0:00   0:00   0.0     0 postgres: logical replication launcher 
  44510             272M 2556K          0:00   0:00   0.0     0 postgres: checkpointer     

last pid: 47814;  load avg:  0.00,  0.01,  0.05;       up 0+16:11:47   21:24:52
10 processes: 6 other background task(s), 2 idle, 2 active
CPU states:  0.2% user,  0.0% nice,  0.1% system, 99.7% idle,  0.0% iowait
Memory: 2936M used, 7044M free, 0K shared, 3664K buffers, 1575M cached
Swap: 0K used, 2044M free, 0K cached, 0K in, 0K out

    PID USERNAME    SIZE   RES STATE   XTIME  QTIME  %CPU LOCKS COMMAND
  47815 postgres    273M 7352K active   0:00   0:00   0.0     8 postgres: postgres postgres [local] idle
  44513             272M 2192K          0:00   0:00   0.0     0 postgres: autovacuum launcher 
  45597 replxs      272M 3200K idle     0:00   0:00   0.0     0 postgres: replxs postgres [local] idle
  44511             272M 2136K          0:00   0:00   0.0     0 postgres: background writer 
  45318 replxs      272M 2896K active   0:00   0:00   0.0     0 postgres: walsender replxs 192.168.60.190(50318) streaming 0/24011D08
  44517 postgres    283M   15M idle     0:00   0:00   0.4     0 postgres: postgres postgres 192.168.60.1(56947) idle
  44514             271M 1112K          0:00   0:00   0.0     0 postgres: archiver last was 000000010000000000000023.00000028.backup
  44512             271M 5036K          0:00   0:00   0.0     0 postgres: walwriter        
  44516 postgres    272M 1592K          0:00   0:00   0.0     0 postgres: logical replication launcher 
  44510             272M 2556K          0:00   0:00   0.0     0 postgres: checkpointer

帮助命令

进入pg_top之后，可以按 h 进入帮助页面，可以显示可以使用说明指令。

再根据想要获取的信息，直接输入指令即可。

pg_top version 4.0.0, Copyright (c) 1984 through 2007, William LeFebvre

A top users display for PostgreSQL

These single-character commands are available:

^L      - redraw screen
<sp>    - update screen
A       - EXPLAIN ANALYZE (UPDATE/DELETE safe)
a       - show PostgreSQL activity		--刷新
C       - toggle the use of color
E       - show execution plan (UPDATE/DELETE safe)		--显示执行计划
I       - show I/O statistics per process (Linux only)		--显示每个进程的I/O统计信息
L       - show locks held by a process		--显示进程持有的锁
Q       - show current query of a process		--显示进程的当前查询
c       - toggle the display of process commands
d       - change number of displays to show		--更改要显示的显示器数量
h or ?  - help; show this text
i       - toggle the displaying of idle processes		--切换idle进程的显示
n or #  - change number of processes to display		--更改要显示的进程数
o       - specify sort order (cpu, size, res, xtime, qtime, iops, iorps, iowps, reads, writes, locks, command, flag, rlag, slag, wlag)		--指定排序顺序
q       - quit
s       - change number of seconds to delay between updates		--更改刷新时间
u       - display processes for only one user (+ selects all users)		--仅显示一个用户的进程

Not all commands are available on all systems.
Hit any key to continue:
墨力计划postgresql运维postgresql
最后修改时间：2022-02-12 12:12:29
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

PostgreSQL分享—李先生（共44篇）
日常的PostgreSQL分享，包含运维、升级、安装部署、疑难问题等

评论