README.md
1![pg_activity](https://github.com/dalibo/pg_activity/raw/master/docs/imgs/logo-horizontal.png)
2
3Command line tool for PostgreSQL server activity monitoring.
4
5[![Latest PyPI version](https://img.shields.io/pypi/v/pg_activity.svg)](https://pypi.python.org/pypi/pg_activity)
6[![Lint](https://github.com/dalibo/pg_activity/actions/workflows/lint.yml/badge.svg)](https://github.com/dalibo/pg_activity/actions/workflows/lint.yml)
7[![Tests](https://github.com/dalibo/pg_activity/actions/workflows/tests.yml/badge.svg)](https://github.com/dalibo/pg_activity/actions/workflows/tests.yml)
8
9![pg_activity screenshot](https://raw.github.com/dalibo/pg_activity/master/docs/imgs/screenshot.png)
10
11Installation
12------------
13
14`pg_activity` requires Python 3.6 or later. It can be installed using pip
15(available, e.g., as `apt install python3-pip` on Debian-based distributions):
16
17 $ python3 -m pip install pg_activity psycopg2-binary
18
19or directly from your Linux distribution, if available, e.g.:
20
21 $ sudo apt install pg-activity
22
23`pg_activity` requires psycopg2 version 2.8 or higher.
24
25Usage
26-----
27
28`pg_activity` works locally or remotely. In local execution context, to obtain
29sufficient rights to display system informations, the system user running
30`pg_activity` must be the same user running postgresql server (`postgres` by
31default), or have more rights like `root`. Otherwise, `pg_activity` can fallback
32to a degraded mode without displaying system informations. On the same way,
33PostgreSQL user used to connect to the database must be super-user.
34ex:
35
36 sudo -u postgres pg_activity -U postgres
37
38Options
39-------
40
41 pg_activity [options]
42
43 Options:
44 --version Show program's version number and exit
45 -U USERNAME, --username=USERNAME
46 Database user name (default: "postgres").
47 -p PORT, --port=PORT Database server port (default: "5432").
48 -h HOSTNAME, --host=HOSTNAME
49 Database server host or socket directory (default:
50 "localhost").
51 -d DBNAME, --dbname=DBNAME
52 Database name to connect to (default: "postgres").
53 --blocksize=BLOCKSIZE Filesystem blocksize (default: 4096).
54 --rds Enable support for AWS RDS.
55 --output=FILEPATH Store running queries as CSV.
56 --help Show this help message and exit.
57 --no-db-size Skip total size of DB.
58 --min-duration Don't display queries with smaller than specified
59 duration (in seconds).
60 --verbose-mode=VERBOSE_MODE
61 Queries display mode. Values: 1-TRUNCATED,
62 2-FULL(default), 3-INDENTED
63 --duration-mode=DURATION_MODE
64 Duration mode. Values: 1-QUERY(default),
65 2-TRANSACTION, 3-BACKEND
66
67
68 Display options, you can exclude some columns by using them :
69 --no-database Disable DATABASE.
70 --no-user Disable USER.
71 --no-client Disable CLIENT.
72 --no-cpu Disable CPU%.
73 --no-mem Disable MEM%.
74 --no-read Disable READ/s.
75 --no-write Disable WRITE/s.
76 --no-time Disable TIME+.
77 --no-wait Disable W.
78 --no-app-name Disable App.
79
80
81Notes
82-----
83
84Length of SQL query text that `pg_activity` reports relies on PostgreSQL
85parameter `track_activity_query_size`. Default value is `1024` (expressed in
86bytes). If your SQL query text look truncated, you should increase
87`track_activity_query_size`.
88
89
90Interactives commands
91---------------------
92
93| Key | Action |
94|-----------|------------------------------------------------------------------|
95| `r` | Sort by READ/s, descending |
96| `w` | Sort by WRITE/s, descending |
97| `c` | Sort by CPU%, descending |
98| `m` | Sort by MEM%, descending |
99| `t` | Sort by TIME+, descending |
100| `T` | Change duration mode: query, transaction, backend |
101| `Space` | Pause on/off |
102| `v` | Change queries display mode: full, indented, truncated |
103| `UP/DOWN` | Scroll processes list |
104| `k/j` | Scroll processes list |
105| `q` | Quit |
106| `+` | Increase refresh time. Maximum value : 5s |
107| `-` | Decrease refresh time. Minimum Value : 0.5s |
108| `F1/1` | Running queries list |
109| `F2/2` | Waiting queries list |
110| `F3/3` | Blocking queries list |
111| `h` | Help page |
112| `R` | Refresh |
113| `D` | Refresh Database Size (including when --no-dbzise option applied)|
114
115Navigation mode
116---------------
117
118| Key | Action |
119|------------|-----------------------------------------------|
120| `UP`/`k` | Move up the cursor |
121| `DOWN`/`j` | Move down the cursor |
122| `K` | Terminate the current backend/tagged backends |
123| `C` | Cancel the current backend/tagged backends |
124| `Space` | Tag or untag the process |
125| `q` | Quit |
126| `Other` | Back to activity |
127
128FAQ
129---
130
131**I can't see my queries only TPS is shown**
132
133`pg_activity` scans the view `pg_stat_activity` with a user defined refresh
134time comprised between O.5 and 5 seconds. It can be modified in the interface
135with the `+` and `-` keys. Any query executed between two scans won't be
136displayed.
137
138
139What is more, `pg_activity` uses different queries to get :
140
141* settings from `pg_settings`
142* version info using `version()`
143* queries and number of connections from `pg_stat_activity`
144* locks from `pg_locks`
145* tps from `pg_database` using `pg_stat_get_db_xact_commit()` and
146 `pg_stat_get_db_xact_rollback()`
147* and more ( eg : `pg_cancel_backend()` and `pg_terminate_backend()` )
148
149Thoses queries cannot be seen in the query tab because all queries issued from
150the `pg_activity` backend are considered as noise and are not displayed . On
151the other hand, the transactions used to get the info for `pg_activity`'s
152reporting are still accounted for by postgres in `pg_stat_get_db_xact_commit()`
153and `pg_stat_get_db_xact_commit()`. Therefore `pg_activity` will display a non
154zero TPS even with no activity on the database, and/or no activity displayed on
155screen.
156