| PG_ACTIVITY(1) | Command line tool for PostgreSQL server activity monitoring. | PG_ACTIVITY(1) |
pg_activity - Realtime PostgreSQL database server monitoring tool
pg_activity [option..] [connection string]
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 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 :
Sessions :
Activity :
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:
Other processes & information:
The last group displays system information:
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:
The waiting queries view displays queries that are wainting for a lock. It shows the following information:
The blocking queries view displays the queries that lock an object which is required by another session. It shows following information:
Filesystem blocksize (default: 4096).
Enable support for AWS RDS (implies --no-tempfiles and filters out the rdsadmin database from space calculation).
Store running queries as CSV.
Skip total size of DB.
Skip tempfile count and size.
Skip walreceiver checks.
Skip walreceiver checks.
Wrap query column instead of truncating.
Don't display queries with smaller than specified duration (in seconds).
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)'
Show this help message and exit.
Show program's version number and exit.
Database user name.
Database server port.
Database server host or socket directory.
Database name to connect to.
Disable PID.
Disable DATABASE.
Disable USER.
Disable CLIENT.
Disable CPU%.
Disable MEM%.
Disable READ/s.
Disable WRITE/s.
Disable TIME+.
Disable W.
Disable App.
Display instance information in header.
Display system information in header.
Display workers process information in header.
Change the refresh rate, allowed values are: 0.5, 1, 2, 3, 4, 5 (in seconds) (default: 2).
See: https://www.postgresql.org/docs/current/libpq-envars.html
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.
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
| 2023-01-17 | pg_activity 3.0.3 |