转自:https://www.modb.pro/db/384945
命令行工具pg_activity
8.1 介绍
用于 PostgreSQL 服务器活动监控的命令行工具。
8.2 安装
从RPM包安装(推荐)
pg_activity
在许多 Linux 发行版中都可用; PostgreSQL 全球开发组 (PGDG) 还为基于 RPM (https://yum.postgresql.org/) 和基于 Debian 的发行版 (https://wiki.postgresql.org/wiki/Apt) 提供软件包:
$ sudo yum install pg_activity $ sudo apt install pg-activity
使用分发包是安装 pg_activity
的推荐方式。
从 pip 安装(我用pip,pip使用清华源)
[root@pg14 .pip]# pwd /root/.pip [root@pg14 .pip]# cat pip.conf [global] index-url = https://pypi.tuna.tsinghua.edu.cn/simple
或者,可以在 Python 3.7 或更高版本以及 psycopg2(2.8 或更高版本)上使用 pip 安装 pg_activity
:
$ python3 -m pip install pg_activity psycopg2-binary [root@pg14 ~]# pip install pg_activity psycopg2-binary Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple Collecting pg_activity Downloading https://pypi.tuna.tsinghua.edu.cn/packages/56/5a/ec5a65821802d00aabd8125ef967fb8d4b97599ca65646265e5152d47201/pg_activity-2.3.0-py3-none-any.whl (53 kB) |████████████████████████████████| 53 kB 900 kB/s Requirement already satisfied: psycopg2-binary in /usr/local/python3.6.15/lib/python3.6/site-packages (2.9.3) Collecting humanize Downloading https://pypi.tuna.tsinghua.edu.cn/packages/a6/35/2b1bcfaba1d9e65780f3833fde53f13359a9b8bc7b4d4e7c23135366b589/humanize-3.14.0-py3-none-any.whl (98 kB) |████████████████████████████████| 98 kB 1.5 MB/s Collecting blessed Downloading https://pypi.tuna.tsinghua.edu.cn/packages/94/c5/651224a1bbcb0253fecb7af6f60fa0c9ebabcb1df01ceadb8f4dfd2528cb/blessed-1.19.1-py2.py3-none-any.whl (58 kB) |████████████████████████████████| 58 kB 6.0 MB/s Collecting attrs!=21.1,>=17 Downloading https://pypi.tuna.tsinghua.edu.cn/packages/be/be/7abce643bfdf8ca01c48afa2ddf8308c2308b0c3b239a44e57d020afa0ef/attrs-21.4.0-py2.py3-none-any.whl (60 kB) |████████████████████████████████| 60 kB 6.0 MB/s Collecting psutil>=2.0.0 Downloading https://pypi.tuna.tsinghua.edu.cn/packages/64/87/461555057b080e1996427098a6c51c64a8a9025ec18571dabfe5be07eeec/psutil-5.9.0-cp36-cp36m-manylinux_2_12_x86_64.manylinux2010_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (279 kB) |████████████████████████████████| 279 kB 5.0 MB/s Collecting wcwidth>=0.1.4 Downloading https://pypi.tuna.tsinghua.edu.cn/packages/59/7c/e39aca596badaf1b78e8f547c807b04dae603a433d3e7a7e04d67f2ef3e5/wcwidth-0.2.5-py2.py3-none-any.whl (30 kB) Collecting six>=1.9.0 Downloading https://pypi.tuna.tsinghua.edu.cn/packages/d9/5a/e7c31adbe875f2abbb91bd84cf2dc52d792b5a01506781dbcf25c91daf11/six-1.16.0-py2.py3-none-any.whl (11 kB) Collecting importlib-metadata Downloading https://pypi.tuna.tsinghua.edu.cn/packages/a0/a1/b153a0a4caf7a7e3f15c2cd56c7702e2cf3d89b1b359d1f1c5e59d68f4ce/importlib_metadata-4.8.3-py3-none-any.whl (17 kB) Collecting typing-extensions>=3.6.4 Downloading https://pypi.tuna.tsinghua.edu.cn/packages/45/6b/44f7f8f1e110027cf88956b59f2fad776cca7e1704396d043f89effd3a0e/typing_extensions-4.1.1-py3-none-any.whl (26 kB) Collecting zipp>=0.5 Downloading https://pypi.tuna.tsinghua.edu.cn/packages/bd/df/d4a4974a3e3957fd1c1fa3082366d7fff6e428ddb55f074bf64876f8e8ad/zipp-3.6.0-py3-none-any.whl (5.3 kB) Installing collected packages: zipp, typing-extensions, wcwidth, six, importlib-metadata, psutil, humanize, blessed, attrs, pg-activity Successfully installed attrs-21.4.0 blessed-1.19.1 humanize-3.14.0 importlib-metadata-4.8.3 pg-activity-2.3.0 psutil-5.9.0 six-1.16.0 typing-extensions-4.1.1 wcwidth-0.2.5 zipp-3.6.0 WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv [root@pg14 ~]# pg_activity pg_activity: FATAL: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket?
从 git 存储库安装
这只需要测试开发版本。首先,克隆存储库:
[root@pg14 ~]# git clone https://github.com/dalibo/pg_activity.git Cloning into 'pg_activity'... remote: Enumerating objects: 4034, done. remote: Counting objects: 100% (621/621), done. remote: Compressing objects: 100% (232/232), done. remote: Total 4034 (delta 419), reused 523 (delta 375), pack-reused 3413 Receiving objects: 100% (4034/4034), 2.08 MiB | 589.00 KiB/s, done. Resolving deltas: 100% (2598/2598), done.
必要时更改分支。然后创建一个专用环境,安装依赖项,然后从 repo 安装 pg_activity
:
$ cd pg_activity $ python3 -m venv .venv $ . .venv/bin/activate (.venv) $ pip install psycopg2-binary . (.venv) $ pg_activity
要退出此环境并销毁它:
$ deactivate $ rm -r .venv
8.3 用法
pg_activity
在本地或远程工作。在本地执行上下文中,为了获得足够的权限来显示系统信息,运行 pg_activity
的系统用户必须是运行 postgresql 服务器(默认为 postgres)的同一用户,或者像 root 一样拥有更多的权限。否则,pg_activity
可以回退到降级模式而不显示系统信息。同理,用于连接数据库的 PostgreSQL 用户必须是超级用户。例子:
sudo -u postgres pg_activity -U postgres
8.4 选项
pg_activity [options] Options: --version Show program's version number and exit -U USERNAME, --username=USERNAME Database user name (default: "postgres"). -p PORT, --port=PORT Database server port (default: "5432"). -h HOSTNAME, --host=HOSTNAME Database server host or socket directory (default: "localhost"). -d DBNAME, --dbname=DBNAME Database name to connect to (default: "postgres"). --blocksize=BLOCKSIZE Filesystem blocksize (default: 4096). --rds Enable support for AWS RDS. --output=FILEPATH Store running queries as CSV. --help Show this help message and exit. --no-db-size Skip total size of DB. --duration-mode=DURATION_MODE Duration mode. Values: 1-QUERY(default), 2-TRANSACTION, 3-BACKEND --min-duration Don't display queries with smaller than specified duration (in seconds). --filter=FIELD:REGEX Filter activities with a (case insensitive) regular expression applied on selected fields. Known fields are: dbname. --verbose-mode=VERBOSE_MODE Queries display mode. Values: 1-TRUNCATED, 2-FULL(default), 3-INDENTED Display options, you can exclude some columns by using them : --no-database Disable DATABASE. --no-user Disable USER. --no-client Disable CLIENT. --no-cpu Disable CPU%. --no-mem Disable MEM%. --no-read Disable READ/s. --no-write Disable WRITE/s. --no-time Disable TIME+. --no-wait Disable W. --no-app-name Disable App.
8.5 参数解释
pg_activity
报告的 SQL 查询文本的长度取决于 PostgreSQL 参数 track_activity_query_size
。默认值为 1024(以字节表示)。如果您的 SQL 查询文本看起来被截断,您应该增加 track_activity_query_size
。
交互命令
Key | Action |
——— | ———————————————————— |
r | Sort by READ/s, descending |
w | Sort by WRITE/s, descending |
c | Sort by CPU%, descending |
m | Sort by MEM%, descending |
t | Sort by TIME+, descending |
T | Change duration mode: query, transaction, backend |
Space | Pause on/off |
v | Change queries display mode: full, indented, truncated |
UP/DOWN | Scroll processes list |
k/j | Scroll processes list |
q | Quit |
+ | Increase refresh time. Maximum value : 5s |
- | Decrease refresh time. Minimum Value : 0.5s |
F1/1 | Running queries list |
F2/2 | Waiting queries list |
F3/3 | Blocking queries list |
h | Help page |
R | Refresh |
D | Refresh Database Size (including when –no-dbzise option applied) |
导航模式
Key | Action |
———- | ——————————————— |
UP /k | Move up the cursor |
DOWN /j | Move down the cursor |
K | Terminate the current backend/tagged backends |
C | Cancel the current backend/tagged backends |
Space | Tag or untag the process |
q | Quit |
Other | Back to activity |
8.6 常见问题
我看不到我的查询,只显示 TPS
pg_activity
使用包含在 O.5 和 5 秒之间的用户定义的刷新时间扫描视图 pg_stat_activity
。可以在界面中用+和-键进行修改。不会显示在两次扫描之间执行的任何查询。
更重要的是,pg_activity
使用不同的查询来获取:
- settings from
pg_settings
- version info using
version()
- queries and number of connections from
pg_stat_activity
- locks from
pg_locks
- tps from
pg_database
usingpg_stat_get_db_xact_commit()
andpg_stat_get_db_xact_rollback()
- and more ( eg :
pg_cancel_backend()
andpg_terminate_backend()
)
这些查询不能在查询选项卡中看到,因为从 pg_activity
后端发出的所有查询都被视为噪声并且不显示。另一方面,用于获取 pg_activity
报告信息的事务仍然由 pg_stat_get_db_xact_commit()
和 pg_stat_get_db_xact_commit()
中的 postgres 计算。因此,即使数据库上没有活动,和/或屏幕上没有显示活动,pg_activity
也会显示非零 TPS。
如何指定身份验证密码?
pg_activity
使用 libpq 访问 PostgreSQL,因此所有传统方法都可用。
您可以在密码文件中传递数据库连接的密码。信息也可以通过 PostgreSQL 的环境变量(PGPASSFILE 或 PGPASSWORD)或通过连接字符串参数给出。
密码文件是首选,因为它更安全(安全性被推迟到操作系统)。请不惜一切代价避免在连接字符串中使用密码。