1PG_ACTIVITCYo(m1m)and line tool for PostgreSQL server activity monitorPiGn_gA.CTIVITY(1)
2
6 pg_activity - Realtime PostgreSQL database server monitoring tool
7
9 pg_activity [option..] [connection string]
10
12 Command line tool for PostgreSQL server activity monitoring.
13 pg_activity must run on the same server as the instance and as the user
15 running the instance (or root) to show CPU, MEM, READ or WRITE columns
16 and other system information.
17 THE HEADER
19 The first line of the header displays PostgreSQL's version, the host
20 name, the connection string, the refresh rate and the duration mode.
21 The header is then divided in tree groups: instance, worker processes,
23 system. The information is filtered according to the filter parameters
24 when appropriate. This is shown in the following descriptions with the
25 "(filtered)" mention. Depending on the version you are on, some
26 information might not be available. In that case, it will be replaced
27 by a dash.
28 The instance group displays information aubout the PostgreSQL instance
30 (or cluster). This group can be displayed or hidden with the I Key.
31 Global :
33 - uptime: since when is the instance running;
35 - dbs size: total size of the databases (filtered);
36 - growth: growth in B/s of the databases (filtered);
37 - cache hit ratio: the percentage of page read from the PostgreSQL's
38 cache since last snapshot (filtered).
39 Sessions :
41 - total: session count (filtered) / max_connections;
43 - active: number of active sessions (filtered);
44 - idle: number of idle sessions (filtered);
45 - idle in txn: number of sessions who are in the idle in transaction
46 state (filtered);
47 - idle in txn abrt: number of sessions who are in the idle in
48 transaction aborted state (filtered);
49 - waiting: number of sessions that are waiting for a lock (filtered).
50 Activity :
52 - tps: transaction per second (sum of commit & rollback for all
54 databases / time elapsed since last snapshot) (filtered);
55 - insert/s: number of inserts per second (filtered);
56 - updates/s: number of updates per second (filtered);
57 - delete/s: number of deletes per second (filtered);
58 - tuples returned/s: number of tuples returned per second (filtered);
59 - temp files: number of temporary files created on the instance;
60 - temp size: total temporary file size on the instance.
61 The worker processes group displays information about backgroup
63 workers, autovacuum processes, wal senders and wal receivers. It also
64 gives information about replication slots. Except for the autovacuum
65 workers count, most of this information is not related to a specific
66 database, therefore their values will be zero when the data is
67 filtered.
68 Worker processes:
70 - total: total worker count / maximum number of worker slots, parallel
72 workers and logical replication wokers are taken from this amount
73 (filtered);
74 - logical wokers: logical replication worker count / maximum number of
75 logical replication wokers (filtered);
76 - parallel workers: parallel worker count for maintenance & queries /
77 maximum number of parallel workers (filtered).
78 Other processes & information:
80 - autovacuum workers: number of autovacuum worker in action / maximum
82 number of autovacuum workers (filtered);
83 - wal senders: number of wal senders / maximum number of wal senders
84 processes (filtered);
85 - wal receivers: number of wal receivers / maximum number of wal
86 receiver processes (filtered);
87 - repl. slots: number of replication slots / maximum number of
88 replication slots (filtered).
89 The last group displays system information:
91 - Mem.: total / free / used and buff+cached memory with the related
93 percentages;
94 - Swap: total / free / used swap;
95 - IO: the number of IO per second, current Read and Write thoughput
96 (aggregated data gathered with the psutil library);
97 - Load: CPU load for the last 1, 5, 15 minutes;
98 THE RUNNING QUERIES PANEL
100 The running queries panel shows all running queries, transactions or
101 backends (depending on the DURATION_MODE setting) which have lasted for
102 more than min duration seconds. It displays the following information:
103 - PID: process id of the backend which executes the query;
105 - DATABASE: database specified in the connection string;
106 - APP: application name specified in the connection string;
107 - USER: user name specified in the connection string;
108 - CLIENT: client address or "local" in case of linux socker connection;
109 - CPU%: percentage of CPU used by the backend as reported by the psutil
110 library;
111 - MEM%: percentage of memory used by the backend as reported by the
112 psutil library;
113 - READ/s: read thruput as reported by the psutil library;
114 - WRITE/s: write thruput as reported by the psutil library;
115 - TIME: time since the beginning of the query / transaction / backend
116 start depending on the DURATION_MODE currently in use;
117 - Waiting: for PostgreSQL 9.6+: a specific wait event or nothing.
118 Otherwise, a boolean indicating if we are waiting for a Lock;
119 - IOW: boolean indicating that the process is waiting for IO as
120 reported by the psutil library;
121 - state: state of the backend;
122 - Query: the query.
123 THE WAITING QUERIES PANEL
125 The waiting queries view displays queries that are waiting for a lock.
126 It shows the following information:
127 - PID: process id of the backend which executes the query;
129 - DATABASE: database specified in the connection string;
130 - APP: application name specified in the connection string;
131 - USER: user name specified in the connection string;
132 - CLIENT: client address or "local" in case of linux socker connection;
133 - RELATION: the name of the relation being locked if applicable;
134 - TYPE: the type of lock;
135 - MODE: the mode of the lock;
136 - TIME+: the duration of the query, transaction or session depending on
137 the DURATION_MODE setting;
138 - state: the state of the transaction;
139 - Query: the query.
140 THE BLOCKING QUERIES PANEL
142 The blocking queries view displays the queries that lock an object
143 which is required by another session. It shows following information:
144 - PID: process id of the backend which executes the query;
146 - DATABASE: database specified in the connection string;
147 - APP: application name specified in the connection string;
148 - USER: user name specified in the connection string;
149 - CLIENT: client address or "local" in case of linux socker connection;
150 - RELATION: the name of the relation being locked if applicable;
151 - TYPE: the type of lock;
152 - MODE: the mode of the lock;
153 - TIME+: the duration of the query, transaction or session depending on
154 the DURATION_MODE setting;
155 - Waiting: for PostgreSQL 9.6+: a specific wait event or nothing.
156 Otherwise, a boolean indicating if we are waiting for a Lock;
157 - state: the state of the transaction;
158 - Query: the query.
159
161 OPTIONS
162 --blocksize=BLOCKSIZE
163 Filesystem blocksize (default: 4096).
164 --rds
166 Enable support for AWS RDS (implies --no-tempfiles and filters out the rdsadmin database from space calculation).
167 --output=FILEPATH
169 Store running queries as CSV.
170 --no-db-size
172 Skip total size of DB.
173 --no-tempfiles
175 Skip tempfile count and size.
176 --no-walreceiver
178 Skip walreceiver checks.
179 --no-walreceiver
181 Skip walreceiver checks.
182 -w, --wrap-query
184 Wrap query column instead of truncating.
185 --min-duration=SECONDS
187 Don't display queries with smaller than specified duration (in seconds).
188 --filter=FIELD:REGEX
190 Filter activities with a (case insensitive) regular expression applied on selected fields. Known fields are: dbname.
191 Note: It's possible to filter out a database with negative lookahead, eg: '^(?!database_name)'
193 --help
195 Show this help message and exit.
196 --version
198 Show program's version number and exit.
199 CONNECTION OPTIONS
201 -U USERNAME, --username=USERNAME
202 Database user name.
203 -p PORT, --port=PORT
205 Database server port.
206 -h HOSTNAME, --host=HOSTNAME
208 Database server host or socket directory.
209 -d DBNAME, --dbname=DBNAME
211 Database name to connect to.
212 PROCESS DISPLAY OPTIONS
214 --no-pid
215 Disable PID.
216 --no-database
218 Disable DATABASE.
219 --no-user
221 Disable USER.
222 --no-client
224 Disable CLIENT.
225 --no-cpu
227 Disable CPU%.
228 --no-mem
230 Disable MEM%.
231 --no-read
233 Disable READ/s.
234 --no-write
236 Disable WRITE/s.
237 --no-time
239 Disable TIME+.
240 --no-wait
242 Disable W.
243 --no-app-name
245 Disable App.
246 OTHER DISPLAY OPTIONS
248 --no-inst-info
249 Display instance information in header.
250 --no-sys-info
252 Display system information in header.
253 --no-proc-info
255 Display workers process information in header.
256 --refresh
258 Change the refresh rate, allowed values are: 0.5, 1, 2, 3, 4, 5 (in seconds) (default: 2).
259
261 All the environment variables supported by libpq (PostgreSQL's query
262 protocol) are supported by pg_activity.
263 See: https://www.postgresql.org/docs/current/libpq-envars.html
265
267 r Sort by READ/s, descending.
268 w Sort by WRITE/s, descending.
269 c Sort by CPU%, descending.
270 m Sort by MEM%, descending.
271 t Sort by TIME+, descending.
272 T Change duration mode: query, transaction, backend.
273 Space Pause on/off.
274 v Change queries display mode: full, truncated, indented.
275 UP / DOWN Scroll process list.
276 k / j Scroll process list.
277 q Quit.
278 + Increase refresh time. Maximum value: 3s.
279 - Decrease refresh time. Minimum Value: 1s.
280 F1/1 Running queries monitoring.
281 F2/2 Waiting queries monitoring.
282 F3/3 Blocking queries monitoring.
283 h Help page.
284 R Refresh.
285 D Refresh database size.
286
288 UP / k Move up the cursor.
289 DOWN / j Move down the cursor.
290 PAGE UP Move the cursor to the first line.
291 PAGE DOWN Move the cursor to the last line.
292 K Terminate the current backend/tagged backends.
293 C Cancel the current backend/tagged backends.
294 Space Tag or untag the process.
295 q Quit.
296 Other Back to activity.
297
299 pg_activity is best used with a user owning the SUPERUSER privilege.
300 Ordinary users can only see all the information about their own
301 sessions (sessions belonging to a role that they are a member of). In
302 rows about other sessions, many columns will be null or not picked by
303 pg_activity. It will impact both the information gathered in the HEADER
304 section and the ACTIVITY PANEL
305 If a user doesn't have the CONNECT privilege on a database the
307 pg_database_size() function will fail and pg_activity will crash. The
308 --no-db-size option can be used in this case. This situation is
309 frequent for cloud database where the service provider has created a
310 service database with a restricted access.
311 Some settings are visible only to superusers or members of
313 pg_read_all_settings such as the data_directory guc. If the user cannot
314 read this parameter or access the pid file in the PGDATA directory, the
315 system information HEADER group will not be displayed. The %CPU, %MEM,
316 Read/s and Write/s columns will also be missing from the ACTIVITY
317 PANEL.
318 On some OS like MacOS, psutils.io_counters() is not implemented. The
320 effect are the same as those described when data_directory is not
321 readable.
322 pg_activity needs to access the pgsql_tmp directory stored in all
324 tablespaces in order to compute the number and size of the temporary
325 files. This requires the usage of the pg_ls_tmpdir() function (or
326 pg_ls_dir() and pg_stats_file() for versions older than PostgreSQL 12).
327 The user needs to own the SUPERUSER privilege, be a member of
328 pg_read_server_files or have EXECUTE rights on the function to fetch
329 the information. When the number of tempfiles grows a lot, the query
330 might also timeout. The first failure to access this data will disable
331 tempfile statistics. The feature can be disabled with --no-tempfiles.
332 Aurora doesn't provide the pg_stat_get_wal_receiver() function.
334 Therefore there is no wal receiver data in the process & information
335 HEADER group. The first failure to access this data might be logged by
336 the PostgreSQL, the following checks will be skipped. Wal receiver
337 checks can be completely disabled with --no-walreceiver.
338 Finally, some information is not available in older version of
340 PostgreSQL, the fields will therefore be empty.
341
343 PGPASSWORD='mypassword' pg_activity -U pgadmin -h 127.0.0.1 --no-client
344 pg_activity -h /var/run/postgresql
346 pg_activity -h myserver -p 5433 -d nagios -U nagios
348pg_activity 3.4.2 2023-06-01 PG_ACTIVITY(1)