pg_activity: Open Source Monitoring & Observability

What is pg_activity?

pg_activity is a top-like command-line tool for PostgreSQL server activity monitoring, built for database administrators, on-call engineers, and developers who need real-time visibility into what queries and sessions a PostgreSQL instance is running.

What it is

pg_activity is a terminal application written in Python and distributed under the PostgreSQL licence that renders live PostgreSQL backend activity in the style of the Unix top command. It connects to a PostgreSQL server, reads current activity, and redraws an updating view in the terminal showing query text alongside database, duration, system information, temporary file data, WAL receiver state, and database size. It is published on PyPI as the pg_activity package, installed with the psycopg driver, and is also shipped by Linux distributions and by the PostgreSQL Global Development Group (PGDG) for RPM-based and Debian-based systems. Its topic list reads plainly: activity, cli, monitoring, postgresql, sql, top.

The problem it solves is the manual loop of opening psql and repeatedly querying pg_stat_activity, then reading the result by eye while a slow query is still running. pg_activity replaces that with a continuously refreshed, togglable display that also surfaces data psql does not show without extra work, such as tempfile counts and sizes, walreceiver checks, total database size, and operating-system-level information about backend processes. To get the fullest data set, the connection must use a PostgreSQL super-user and the OS user running pg_activity must be the same user running the PostgreSQL server (postgres by default) or hold more rights such as root; otherwise pg_activity falls back to a degraded mode where system information and temporary file data are not displayed.

Key capabilities

Renders a top-style live view of PostgreSQL backend activity, usable locally or remotely, with the full-fidelity path exercised by sudo -u postgres pg_activity -U postgres.
Stores running queries as CSV through --output FILEPATH, so activity observed in the terminal can be captured for later reading.
Filters displayed activity with a case-insensitive regular expression via --filter FIELD:REGEX, with dbname among the known fields.
Changes what the duration column measures with --duration-mode, accepting 1-QUERY (the default), 2-TRANSACTION, or 3-BACKEND, and hides short-lived work with --min-duration SECONDS.
Shapes the query column with -w/--wrap-query to wrap instead of truncate, and --strip-comments to remove SQL comments from query text.
Toggles auxiliary panels: --db-size/--no-db-size for total database size, --tempfiles/--no-tempfiles for tempfile count and size, and --walreceiver/--no-walreceiver for walreceiver checks.
Enables AWS RDS behaviour with --rds, which implies --no-tempfiles and filters the rdsadmin database out of space calculation, and loads named settings with -P/--profile PROFILE from a PROFILE.conf file in ${XDG_CONFIG_HOME:~/.config}/pg_activity/ or /etc/pg_activity/, or from a built-in profile.

Who uses it and how

A DBA diagnosing a slow production instance runs pg_activity on the database host as the postgres user, gaining both query activity and system-level detail that a degraded, non-matching login would hide.
An engineer reaching a server remotely points pg_activity at it through the connection string instead of logging into the host, subject to the same PostgreSQL privilege requirements.
Teams running PostgreSQL on AWS RDS use --rds mode, which adapts the display to the managed environment by dropping tempfile collection and excluding the rdsadmin database.
Organizations with established packaging workflows install pg-activity from distribution packages or PGDG repositories, keeping the monitoring tool under the same upgrade process as the rest of the system.
Contributors and testers working on development versions clone the repository, build a virtual environment, and install from source rather than consuming a release.

Getting started

On Debian-based distributions the simplest route is sudo apt install pg-activity, with PGDG repositories covering both RPM-based and Debian-based systems. Otherwise install from PyPI with python3 -m pip install "pg_activity[psycopg]" or run it isolated with pipx install "pg_activity[psycopg]".

How it compares

No paid product and no comparable tool is named in the facts for this entry, so pg_activity stands alone in this registry. It occupies the conceptual niche of top, applied to PostgreSQL activity rather than to operating-system processes.

When to use it — and when not to

A self-hoster operates only a PostgreSQL client, Python and the psycopg driver, but full-fidelity data depends on a super-user connection and an OS user that matches the account running the PostgreSQL server, so restricted environments get a reduced view. Distribution packages may lag the latest releases, and anyone who needs retained history or dashboards rather than a live terminal view and CSV capture should not pick it.

project readme (upstream, from github) — read inline

Command line tool for PostgreSQL server activity monitoring.

pg_activity screenshot

Installation

From distribution packages

The simplest way to install pg_activity is through the package manager of your Linux distribution, if it ships with a package. E.g., on Debian-based distributions (e.g. Debian, Ubuntu, Mint...):

$ sudo apt install pg-activity

(on Debian bullseye, the current stable version, a backport is available: apt install pg-activity/bullseye-backports).

The PostgreSQL Global Development Group (PGDG) also provides packages for RPM-based (https://yum.postgresql.org/) and Debian-based distributions (https://wiki.postgresql.org/wiki/Apt).

Note: distribution packages may not be up to date with the latest pg_activity releases. Before submitting a bug report here:

check the package version, compare that to our latest release and then review the [change log][changelog] to see if the bug has been fixed;
if the issue is about packaging, e.g. missing dependencies, reach out the package maintainer (or PGDG) first.

From PyPI

pg_activity can be installed using pip along with psycopg:

$ python3 -m pip install "pg_activity[psycopg]"

Alternatively, pipx can be used to install and run pg_activity in an isolated environment:

$ pipx install "pg_activity[psycopg]"

In case your $PATH does not already contain it, the full path is:

$ ~/.local/bin/pg_activity

From source, using git

This is only necessary to test development versions. First, clone the repository:

$ git clone https://github.com/dalibo/pg_activity.git

Change the branch if necessary. Then create a dedicated environment, and install pg_activity with the psycopg database driver:

$ cd pg_activity
$ python3 -m venv .venv
$ . .venv/bin/activate
(.venv) $ pip install ".[psycopg]"
(.venv) $ pg_activity

To quit this env and destroy it:

$ deactivate
$ rm -r .venv

Usage

pg_activity works locally or remotely. In local execution context, to obtain sufficient rights to display system information, the system user running pg_activity must be the same user running postgresql server (postgres by default), or have more rights like root. The PostgreSQL user used to connect to the database must be super-user in order to get as much data as possible. Otherwise, pg_activity can fall back to a degraded mode where some data like system information or temporary file data are not displayed.

ex:

sudo -u postgres pg_activity -U postgres

Options

pg_activity [options] [connection string]

Configuration:
  -P, --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.
--strip-comments      Strip SQL comments from query text.
  --duration-mode DURATION_MODE
                        Duration mode. Values: 1-QUERY(default),
                        2-TRANSACTION, 3-BACKEND.
  --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.
  --debug-file DEBUG_FILE
                        Enable debug and write it to DEBUG_FILE.
  --version             show program's version number and exit.
  --help                Show this help message and exit.

Connection Options:
  connection string     A valid connection string to the database, e.g.:
                        'host=HOSTNAME port=PORT user=USER dbname=DBNAME'.
  -h, --host HOSTNAME   Database server host or socket directory.
  -p, --port PORT       Database server port.
  -U, --username USERNAME
                        Database user name.
  -d, --dbname DBNAME   Database name to connect to.

Process table display options:
  These options may be used hide some columns from the processes table.

  --pid, --no-pid       Enable/disable PID.
  --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        Display instance information.
  --no-sys-info         Display system information.
  --no-proc-info        Display workers process information.

Other display options:
  --hide-queries-in-logs
                        Disable log_min_duration_statements and
                        log_min_duration_sample for pg_activity.
  --refresh REFRESH     Refresh rate. Values: 0.5, 1, 2, 3, 4, 5 (default: 2).

Configuration

pg_activity may be configured through a configuration file, in INI format, read from ${XDG_CONFIG_HOME:~/.config}/pg_activity.conf or /etc/pg_activity.conf in that order. Command-line options may override configuration file settings. This is used to control how columns in the processes table are rendered or which items of the header should be displayed, e.g.:

[header]
show_instance = yes
show_system = yes
show_workers = no

[client]
hidden = yes

[database]
width = 9

Alternatively, the user might define configuration profiles in the form of files located at ${XDG_CONFIG_HOME:~/.config}/pg_activity/.conf or /etc/pg_activity/.conf; these can then be used through the --profile command-line option. The format of these files is the same as the main configuration file.

pg_activity ships with a few built-in profiles:

narrow, providing a narrow user interface with most non-essential columns in the process table hidden,
wide, providing a wide user interface (the inverse of narrow), and,
minimal, providing an even more minimal user interface with header information hidden

Columns of the process table in pg_activity user interface can be assigned a custom color in the configuration file, e.g.:

[client]
color = magenta

[relation]
color = red

The color option illustrated above defines the color used to render the cell independently of its value, i.e. the "normal" color. Some columns may be colorized differently depending on the value of their cells; for example, the time column can handle tree colors depending on whether the time value is high, medium or low. The color of such columns cannot be currently customized and attempting to do so will result in pg_activity to exit early with an error message.

Notes

Length of SQL query text that pg_activity reports relies on PostgreSQL parameter track_activity_query_size. Default value is 1024 (expressed in bytes). If your SQL query text look truncated, you should increase track_activity_query_size.

Interactives commands

| Key | Action

Frequently asked questions

Is pg_activity free to use?

pg_activity is open source under the PostgreSQL licence. There is no licence fee and no seat count — you can self-host it or, where the project offers one, pay a vendor for a managed version instead.

What does pg_activity do?

pg_activity is a top like application for PostgreSQL server activity monitoring.

What is pg_activity written in?

pg_activity is primarily written in Python. Its source is publicly available at https://github.com/dalibo/pg_activity, and it has 3,038 GitHub stars.