Provided by: pg-activity_3.0.3-1_all 
NAME
pg_activity - Realtime PostgreSQL database server monitoring tool
SYNOPSIS
pg_activity [option..] [connection string]
DESCRIPTION
Command line tool for PostgreSQL server activity monitoring.
pg_activity must run on the same server as the instance and as the user running the
instance (or root) to show CPU, MEM, READ or WRITE columns and other system information.
THE HEADER
The first line of the header displays PostgreSQL's version, the host name, the connection
string, the refresh rate and the duration mode.
The header is then divided in tree groups: instance, worker processes, system. The
information is filtered according to the filter parameters when appropriate. This is shown
in the following descriptions with the "(filtered)" mention. Depending on the version you
are on, some information might not be available. In that case, it will be replaced by a
dash.
The instance group displays information aubout the PostgreSQL instance (or cluster). This
group can be displayed or hidden with the I Key.
Global :
- uptime: since when is the instance running;
- dbs size: total size of the databases (filtered);
- growth: groth in B/s of the databases (filtered);
- cache hit ratio: the percentage of page read from the PostgreSQL's cache since last
snapshot (filtered).
Sessions :
- total: session count (filtered) / max_connections;
- active: number of active sessions (filtered);
- idle: number of idle sessions (filtered);
- idle in txn: number of sessions who are in the idle in transaction state (filtered);
- idle in txn abrt: number of sessions who are in the idle in transaction aborted state
(filtered);
- waiting: number of sessions that are waiting for a lock (filtered).
Activity :
- tps: transaction per second (sum of commit & rollback for all databases / time elapsed
since last snapshot) (filtered);
- insert/s: number of inserts per second (filtered);
- updates/s: number of updates per second (filtered);
- delete/s: number of deletes per second (filtered);
- tuples returned/s: number of tuples returned per second (filtered);
- temp files: number of temporary files created on the instance;
- temp size: total temporary file size on the instance.
The worker processes group displays information about backgroup workers, autovacuum
processes, wal senders and wal receivers. It also gives information about replication
slots. Except for the autovacuum workers count, most of this information is not related to
a specific database, therefore their values will be zero when the data is filtered.
Worker processes:
- total: total worker count / maximum number of worker slots, parallel workers and logical
replication wokers are taken from this amount (filtered);
- logical wokers: logical replication worker count / maximum number of logical replication
wokers (filtered);
- parallel workers: parallel worker count for maintenance & queries / maximum number of
parallel workers (filtered).
Other processes & information:
- autovacuum workers: number of autovacuum worker in action / maximum number of autovacuum
workers (filtered);
- wal senders: number of wal senders / maximum number of wal senders processes (filtered);
- wal receivers: number of wal receivers / maximum number of wal receiver processes
(filtered);
- repl. slots: number of replication slots / maximum number of replication slots
(filtered).
The last group displays system information:
- Mem.: total / free / used and buff+cached memory with the related percentages;
- Swap: total / free / used swap;
- IO: the number of IO per second, current Read and Write thoughput (aggregated data
gathered with the psutil library);
- Load: CPU load for the last 1, 5, 15 minutes;
THE RUNNING QUERIES PANEL
The running queries panel shows all running queries, transactions or backends (depending
on the DURATION_MODE setting) which have lasted for more than min duration seconds. It
displays the following information:
- PID: process id of the backend which executes the query;
- DATABASE: database specified in the connection string;
- APP: application name specified in the connection string;
- USER: user name specified in the connection string;
- CLIENT: client address or "local" in case of linux socker connection;
- CPU%: percentage of CPU used by the backend as reported by the psutil library;
- MEM%: percentage of memory used by the backend as reported by the psutil library;
- READ/s: read thruput as reported by the psutil library;
- WRITE/s: write thruput as reported by the psutil library;
- TIME: time since the beginning of the query / transaction / backend start depending on
the DURATION_MODE currently in use;
- Waiting: for PostgreSQL 9.6+: a specific wait event or nothing. Otherwise, a boolean
indicating if we are waiting for a Lock;
- IOW: boolean indicating that the process is waiting for IO as reported by the psutil
library;
- state: state of the backend;
- Query: the query.
THE WAITING QUERIES PANNEL
The waiting queries view displays queries that are wainting for a lock. It shows the
following information:
- PID: process id of the backend which executes the query;
- DATABASE: database specified in the connection string;
- APP: application name specified in the connection string;
- USER: user name specified in the connection string;
- CLIENT: client address or "local" in case of linux socker connection;
- RELATION: the name of the relation being locked if applicable;
- TYPE: the type of lock;
- MODE: the mode of the lock;
- TIME+: the duration of the query, transaction or session depending on the DURATION_MODE
setting;
- state: the state of the transaction;
- Query: the query.
THE BLOCKING QUERIES PANNEL
The blocking queries view displays the queries that lock an object which is required by
another session. It shows following information:
- PID: process id of the backend which executes the query;
- DATABASE: database specified in the connection string;
- APP: application name specified in the connection string;
- USER: user name specified in the connection string;
- CLIENT: client address or "local" in case of linux socker connection;
- RELATION: the name of the relation being locked if applicable;
- TYPE: the type of lock;
- MODE: the mode of the lock;
- TIME+: the duration of the query, transaction or session depending on the DURATION_MODE
setting;
- Waiting: for PostgreSQL 9.6+: a specific wait event or nothing. Otherwise, a boolean
indicating if we are waiting for a Lock;
- state: the state of the transaction;
- Query: the query.
COMMAND-LINE OPTIONS
OPTIONS
--blocksize=BLOCKSIZE
Filesystem blocksize (default: 4096).
--rds
Enable support for AWS RDS (implies --no-tempfiles and filters out the rdsadmin database from space calculation).
--output=FILEPATH
Store running queries as CSV.
--no-db-size
Skip total size of DB.
--no-tempfiles
Skip tempfile count and size.
--no-walreceiver
Skip walreceiver checks.
--no-walreceiver
Skip walreceiver checks.
-w, --wrap-query
Wrap query column instead of truncating.
--min-duration=SECONDS
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.
Note: It's possible to filter out a database with negative lookahead, eg: '^(?!database_name)'
--help
Show this help message and exit.
--version
Show program's version number and exit.
CONNECTION OPTIONS
-U USERNAME, --username=USERNAME
Database user name.
-p PORT, --port=PORT
Database server port.
-h HOSTNAME, --host=HOSTNAME
Database server host or socket directory.
-d DBNAME, --dbname=DBNAME
Database name to connect to.
PROCESS DISPLAY OPTIONS
--no-pid
Disable PID.
--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.
OTHER DISPLAY OPTIONS
--no-inst-info
Display instance information in header.
--no-sys-info
Display system information in header.
--no-proc-info
Display workers process information in header.
--refresh
Change the refresh rate, allowed values are: 0.5, 1, 2, 3, 4, 5 (in seconds) (default: 2).
ENVIRONMENT VARIABLES
All the environment variables supported by libpq (PostgreSQL's query protocol) are
supported by pg_activity.
See: https://www.postgresql.org/docs/current/libpq-envars.html
INTERACTIVE COMMANDS
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, truncated, indented.
UP / DOWN Scroll process list.
k / j Scroll process list.
q Quit.
+ Increase refresh time. Maximum value: 3s.
- Decrease refresh time. Minimum Value: 1s.
F1/1 Running queries monitoring.
F2/2 Waiting queries monitoring.
F3/3 Blocking queries monitoring.
h Help page.
R Refresh.
D Refresh database size.
NAVIGATION MODE
UP / k Move up the cursor.
DOWN / j Move down the cursor.
PAGE UP Move the cursor to the first line.
PAGE DOWN Move the cursor to the last line.
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.
MISSING DATA IN THE UI?
pg_activity is best used with a user owning the SUPERUSER privilege. Ordinary users can
only see all the information about their own sessions (sessions belonging to a role that
they are a member of). In rows about other sessions, many columns will be null or not
picked by pg_activity. It will impact both the information gathered in the HEADER section
and the ACTIVITY PANEL
If a user doesn't have the CONNECT privilege on a database the pg_database_size() function
will fail and pg_activity will crash. The --no-db-size option can be used in this case.
This situation is frequent for cloud database where the service provider has created a
service database with a restricted access.
Some settings are visible only to superusers or members of pg_read_all_settings such as
the data_directory guc. If the user cannot read this parameter or access the pid file in
the PGDATA directory, the system information HEADER group will not be displayed. The %CPU,
%MEM, Read/s and Write/s columns will also be missing from the ACTIVITY PANEL.
On some OS like MacOS, psutils.io_counters() is not implemented. The effect are the same
as those described when data_directory is not readable.
pg_activity needs to access the pgsql_tmp directory stored in all tablespaces in order to
compute the number and size of the temporary files. This requires the usage of the
pg_ls_tmpdir() function (or pg_ls_dir() and pg_stats_file() for versions older than
PostgreSQL 12). The user needs to own the SUPERUSER privilege, be a member of
pg_read_server_files or have EXECUTE rights on the function to fetch the information. When
the number of tempfiles grows a lot, the query might also timeout. The first failure to
access this data will disable tempfile statistics. The feature can be disabled with
--no-tempfiles.
Aurora doesn't provide the pg_stat_get_wal_receiver() function. Therefore there is no wal
receiver data in the process & information HEADER group. The first failure to access this
data might be logged by the PostgreSQL, the following checks will be skipped. Wal
receiver checks can be completely disabled with --no-walreceiver.
Finally, some information is not available in older version of PostgreSQL, the fields will
therefore be empty.
EXAMPLES
PGPASSWORD='mypassword' pg_activity -U pgadmin -h 127.0.0.1 --no-client
pg_activity -h /var/run/postgresql
pg_activity -h myserver -p 5433 -d nagios -U nagios