PG_ACTIVITY(1)

📦
version 3.6.2 · 7 versions
, in fedora 43, fedora 44, ubuntu stonking

💖 pg_activity(1) 💖

okay bestie, welcome to pg_activity — the command line tool for PostgreSQL server activity monitoring, and yes she's that girl. this is a real manpage, pg_activity(1), and we're gonna get through all of it together. grab a strawberry 🍓, hydrate, and let's go.

✨ NAME ✨

pg_activity — Realtime PostgreSQL database server monitoring tool

🌸 SYNOPSIS 🌸

pg_activity [option..] [connection string]

🦋 DESCRIPTION 🦋

Command line tool for PostgreSQL server activity monitoring. periodt.

pg_activity has ONE big requirement and she's not flexible about it: she must run on the same server as the instance, and as the user running the instance (or root), otherwise the CPU, MEM, READ and WRITE columns and the other system info just... won't show up. no cap. it's giving "run me right or don't run me at all" 💅

👑 THE HEADER 👑

The first line of the header shows PostgreSQL's version, the host name, the connection string, the refresh rate and the duration mode. iconic.

After that, the header splits into three groups: instance, worker processes, system. The info gets filtered according to the filter parameters when that's appropriate — that's what the (filtered) mentions below mean, so watch for them. Depending on the version you're on, some info might not be available at all, and in that case it gets replaced by a dash. sad but honest 😭

The instance group displays information about the PostgreSQL instance (or cluster). You can show or hide this whole group with the I key.

Global:

  • uptime: since when is the instance running;
  • dbs size: total size of the databases (filtered);
  • growth: growth 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);
  • 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 background workers, autovacuum processes, wal senders and wal receivers. It also gives you the tea on replication slots 🫖. Heads up tho: except for the autovacuum workers count, most of this info is not related to a specific database, so their values will be zero when the data is filtered. not a bug, just how she is.

Worker processes:

  • total: total worker count / maximum number of worker slots, parallel workers and logical replication workers are taken from this amount (filtered);
  • logical workers: logical replication worker count / maximum number of logical replication workers (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).

And the last group is all about the system:

  • 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 throughput (aggregated data gathered with the psutil library);
  • Load: CPU load for the last 1, 5, 15 minutes;

🎀 THE RUNNING QUERIES PANEL 🎀

This panel shows all running queries, transactions or backends (depending on the DURATION_MODE setting) which have lasted for more than min duration seconds. she's picky about duration, we respect it. It displays:

  • PID: process id of the backend which executes the query;
  • XMIN: xmin horizon of the backend;
  • 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 socket 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 PANEL 💜

The waiting queries view displays queries that are waiting for a lock. (the drama. the tension.) It shows:

  • 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 socket 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 PANEL 🌈

The blocking queries view displays the queries that lock an object which is required by another session. yes, she's the problem 💀. 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 socket 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 ⭐

🧚 CONFIGURATION 🧚

  • -P PROFILE, --profile=PROFILE — Configuration profile matching a PROFILE.conf file in ${XDG_CONFIG_HOME:~/.config}/pg_activity/ or /etc/pg_activity/, or a built-in profile.

🔥 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.
  • --db-size, --no-db-size — Enable/disable total size of DB.
  • --tempfiles, --no-tempfiles — Enable/disable tempfile count and size.
  • --walreceiver, --no-walreceiver — Enable/disable 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 🌸

  • --pid, --no-pid — Enable/disable PID.
  • --xmin, --no-xmin — Enable/disable XMIN.
  • --database, --no-database — Enable/disable DATABASE.
  • --user, --no-user — Enable/disable USER.
  • --client, --no-client — Enable/disable CLIENT.
  • --cpu, --no-cpu — Enable/disable CPU%.
  • --mem, --no-mem — Enable/disable MEM%.
  • --read, --no-read — Enable/disable READ/s.
  • --write, --no-write — Enable/disable WRITE/s.
  • --time, --no-time — Enable/disable TIME+.
  • --wait, --no-wait — Enable/disable W.
  • --app-name, --no-app-name — Enable/disable APP.

🎀 HEADER DISPLAY OPTIONS 🎀

  • --no-inst-info — Hide instance information.
  • --no-sys-info — Hide system information.
  • --no-proc-info — Hide workers process information.

💅 OTHER DISPLAY OPTIONS 💅

  • --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. all of them. she's generous like that 💖

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.
  • y — Copy focused query to clipboard.
  • 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.
  • 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? 💀

okay bestie, real talk — if your UI is looking a lil sparse, it's probably not a bug, it's a permissions thing. here's the tea ☕

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. no cap, that's just how it is 😭

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. (cloud providers stay doing the most, fr fr 🙈)

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. gone. vanished. not invited to the party 💅

On some OS like MacOS, psutil's io_counters() is not implemented. The effects 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. it's giving "old software, old problems" 🦋

✨ EXAMPLES ✨

okay here's how the girlies actually run it 🫶

Password via env var, connect as pgadmin on localhost, and skip the client info:

PGPASSWORD='mypassword' pg_activity -U pgadmin -h 127.0.0.1 --no-client

Connect over the local unix socket:

pg_activity -h /var/run/postgresql

Hit a remote-ish host on a custom port with a specific db and user:

pg_activity -h myserver -p 5433 -d nagios -U nagios

and that's the whole page, bestie!! you ate that 💅✨ go watch your server, she's serving main character energy and you've got the receipts 👑

this was pg_activity version 3.6.2, dated 2026-05-12 — the PG...Y(1) manual page, if you're keeping track 📖. now go forth and monitor, mwah 🎀💖

📦 where this page comes from