One day, you will need to monitor your database to analyse the performences, the number of queries, … Some tools exist !
This is the cousin of the simple commant top, but applied to psql. It will show you the active connection.
See the process list
This is the default screen. To start pg_top:
pg_top [-d ] [-U ] [-W]
Where ‘-d’ specifies the database to connect to, ‘-U’ specifies the database user, and ‘-W’ will prompt for the password if a password is required to connect.
Use the ‘Q’ key and enter a PID once pg_top is started.
Use the ‘E’ key and enter a PID once pg_top is started. The ‘E’ commands runs ‘EXPLAIN’ and ‘EXPLAIN ANALYZE’ can be used by using the ‘A’ key followed by a PID.
Use the ‘L’ key and enter a PID once pg_top is started.
Use the ‘R’ key to display user table statistics. Use ‘t’ to toggle between displaying cumulative and differential statistics.
Use the ‘X’ key to display user index statistics. Use ‘t’ to toggle between displaying cumulative and differential statistics.
pg_activity - Realtime PostgreSQL database server monitoring tool
pg_activity is a htop like application for PostgreSQL server activity monitoring.
-U USERNAME, --username=USERNAME Database user name (default: $USER). -p PORT, --port=PORT Database server port (default: "5432"). -h HOSTNAME, --host=HOSTNAME Database server host or socket directory (default: "localhost"). -d DBNAME, --dbname=DBNAME Database name to connect to (default: "postgres"). -C, --no-color Disable color usage. --help Show this help message and exit. --version Show program's version number and exit.
PGPASSWORD PostgreSQL password PGPASSFILE Path to .pgpass file (default is ~/.pgpass)
--no-database Disable DATABASE. --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.
C Activate/deactivate colors. 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. Space Pause on/off. v Change queries display mode: full, truncated, indented UP / DOWN Scroll process list. q Quit + Increase refresh time. Maximum value : 3s - Decrease refesh time. Minimum Value : 1s F1/1 Running queries monitoring. F2/2 Waiting queries monitoring. F3/3 Blocking queries monitoring. h Help page. R Refresh.
UP Move up the cursor DOWN Move down the cursor k Cancel the backend Space Back to activity q Quit
PGPASSWORD='mypassword' pg_activity -U pgadmin -h 127.0.0.1 --no-client -C pg_activity -h /tmp
You can also query the pg_stat_activity table of your database.