• Stars
    star
    2,472
  • Rank 18,595 (Top 0.4 %)
  • Language
    Python
  • License
    PostgreSQL License
  • Created about 12 years ago
  • Updated 2 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

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

pg_activity

Command line tool for PostgreSQL server activity monitoring.

Latest PyPI version Lint Tests

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 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 on Python 3.7 or later 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]

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.
  -w, --wrap-query      Wrap query column instead of truncating.
  --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 HOSTNAME, --host HOSTNAME
                        Database server host or socket directory.
  -p PORT, --port PORT  Database server port.
  -U USERNAME, --username USERNAME
                        Database user name.
  -d DBNAME, --dbname DBNAME
                        Database name to connect to.

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

  --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:
  --hide-queries-in-logs
                        Disable log_min_duration_statements and log_min_duration_sample for pg_activity.
  --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 REFRESH     Refresh rate. Values: 0.5, 1, 2, 3, 4, 5 (default: 2).

Configuration

pg_activity may be configured through 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, e.g.:

[client]
hidden = yes

[database]
width = 9

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
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, indented, truncated
UP/DOWN Scroll processes list
k/j Scroll processes list
q Quit
+ Increase refresh time. Maximum value : 5s
- Decrease refresh time. Minimum Value : 0.5s
F1/1 Running queries list
F2/2 Waiting queries list
F3/3 Blocking queries list
h Help page
R Refresh
D Refresh Database Size (including when --no-dbzise option applied)
s Display system information in header
i Display general instance information in header
o Display worker information in header

Navigation mode

Key Action
UP/k Move up the cursor
DOWN/j Move down the cursor
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

FAQ

I can't see my queries only TPS is shown

pg_activity scans the view pg_stat_activity with a user defined refresh time comprised between 0.5 and 5 seconds. It can be modified in the interface with the + and - keys. Any query executed between two scans won't be displayed.

What is more, pg_activity uses different queries to get :

  • settings from pg_settings
  • version info using version()
  • queries and number of connections from pg_stat_activity
  • locks from pg_locks
  • tps from pg_database using pg_stat_get_db_xact_commit() and pg_stat_get_db_xact_rollback()
  • and more ( eg : pg_cancel_backend() and pg_terminate_backend() )

Those queries cannot be seen in the query tab because all queries issued from the pg_activity backend are considered as noise and are not displayed . On the other hand, the transactions used to get the info for pg_activity's reporting are still accounted for by postgres in pg_stat_get_db_xact_commit() and pg_stat_get_db_xact_commit(). Therefore pg_activity will display a non zero TPS even with no activity on the database, and/or no activity displayed on screen.

How can I specify a password for authentication ?

pg_activity uses libpq to access to PostgreSQL therefore all the traditional methods are available.

You can pass the password for the database connection in a password file. Information can also be given via PostgreSQL's environment variables (PGPASSFILE or PGPASSWORD) or via the connection string parameters.

The password file is preferred since it's more secure (security is deferred to the OS). Please avoid password in connection strings at all cost.

Change log

See CHANGELOG.md.

More Repositories

1

pev2

Postgres Explain Visualizer 2
TypeScript
2,492
star
2

sqlserver2pgsql

Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database, as automatically as possible
Perl
507
star
3

temboard

PostgreSQL Remote Control
Python
449
star
4

ldap2pg

๐Ÿ˜ ๐Ÿ‘ฅ Manage PostgreSQL roles and privileges from YAML or LDAP
Go
208
star
5

pandocker

๐Ÿณ A simple docker image for pandoc with filters, templates, fonts, and the latex bazaar
Shell
145
star
6

pitrery

DEPRECATED. PostgreSQL Point In Time Recovery made easy
Shell
108
star
7

pgshark

Messing with PostgreSQL network traffic to make some usefull things
Perl
85
star
8

emaj

E-Maj is a PostgreSQL extension that tracks updates on tables sets with rollback capabilities
PLpgSQL
73
star
9

explain.dalibo.com

PEV2 Flask service. Visualizing and understanding PostgreSQL EXPLAIN plans made easy.
HTML
37
star
10

workshops

PostgreSQL Workshops
HTML
34
star
11

db2topg

Automated tool for DB2 migration to PostgreSQL, ala ora2pg
Perl
31
star
12

pgbadger

pgbadger.github.io
HTML
23
star
13

pgtoolkit

Postgres Support from Python
Python
21
star
14

temboard-agent

PostgreSQL Remote Control agent [MERGED IN dalibo/temboard]
Python
20
star
15

pgsnap

pgsnap is a PostgreSQL tool that mimics orasnap performance report tool for Oracle.
PHP
20
star
16

pg_log_authfail

C
16
star
17

pgsql-resource-agent

Simple Pacemaker OCF Agent for two PostgreSQL servers in streaming replication
Shell
11
star
18

docker

Docker images of Dalibo's projects
10
star
19

emaj_web

A web client for the E-Maj PostgreSQL extension
PHP
10
star
20

data2pg

PLpgSQL
10
star
21

check_patroni

A nagios plugin for patroni.
Python
7
star
22

pg_dumpacl

Tool for dumping database creation options
C
7
star
23

pgsql_logwatch

Monitor PostgreSQL logfile using Logwatch
ApacheConf
6
star
24

selinux-pgsql-pgdg

SELinux policy module for PGDG rpms
Shell
4
star
25

pg_query_settings

Module that dynamically set queries parameters based on their queryid
C
4
star
26

cornac

Migrated to GitLab
Python
4
star
27

docker-python26

Docker image with EOL Python 2.6
HTML
3
star
28

from-oracle-to-postgresql

Yet another transition guide for developers
HTML
3
star
29

patroni-rpm

Patroni RPM packaging (centos/rhel)
Shell
3
star
30

blog

๐Ÿ˜ blog.dalibo.com
HTML
3
star
31

hackingpg

Dรฉpรดt relatif aux journรฉes de hacking sur PostgreSQL
C
2
star
32

selinux-powa

SELinux policy for PoWA
Makefile
2
star
33

pgpool_adm

A PostgreSQL extension to control pgpool using its pcp protocol
C
2
star
34

pg_french_datatypes

A simple set of handy data types for french speaking PostgreSQL developpers
2
star
35

dalibo.github.io

Some PostgreSQL tools developped by DALIBO, the french PostgreSQL company
CSS
1
star
36

keyex

SSH Key Exchanger
Python
1
star
37

yang

Yet Another Nagios Grapher
JavaScript
1
star
38

docker-pgtap

1
star
39

docker-labs-sdk

Mutualised docker image for PostgreSQL tooling development
Dockerfile
1
star
40

docker-cornac-sdk

Docker image for CI and development of cornac
Makefile
1
star
41

vault-rpm

HashiCorp Vault RPM packaging for CentOS / RHEL 7
Shell
1
star
42

ansible-collection-dolibarr

Ansible collection to manage the lifecycle of your Dolibarr instance
Jinja
1
star