FAIRYFAR-INTERNAL
 
  FAIRYFAR-INTERNAL  |  SITEMAP  |  ABOUT-ME  |  HOME  
【译】postgresql扩展工具命令行工具pg_activity

转自:https://www.modb.pro/db/384945

命令行工具pg_activity

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 using pg_stat_get_db_xact_commit() and pg_stat_get_db_xact_rollback()
  • and more ( eg : pg_cancel_backend() and pg_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)或通过连接字符串参数给出。

密码文件是首选,因为它更安全(安全性被推迟到操作系统)。请不惜一切代价避免在连接字符串中使用密码。



打赏作者以资鼓励: