我们在操作系统上经常喜欢用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
^
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, 米乐app官网下载 copyright (c) 1984 through 2007, william lefebvre
a top users display for postgresql
these single-character commands are available:
^l - redraw screen
- 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:
最后修改时间：2022-02-12 12:12:29
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。