I'm investigating an issue with DB connections being left open indefinitely, causing problems on the DB server. How do I see currently open connections to a PostgreSQL server, particularly those using a specific database? Ideally I'd like to see what command is executing there as well. Basically, I'm looking for something equivalent to the "Current Activity" view in MSSQL.
See also pg-top, which acts like top except it shows postgres activity.
- Install pg-top (in Debian, the package is called "ptop").
- Become the postgres user (e.g.
sudo su postgres)
SELECT pid ,datname ,usename ,application_name ,client_hostname ,client_port ,backend_start ,query_start ,query FROM pg_stat_activity WHERE state <> 'idle' AND pid<>pg_backend_pid();
Two ways to monitor in Ubuntu 18.04, just for reference
$ sudo apt-get install ptop $ pg_top # similar to top as others mentioned
$ sudo apt-get install pgadmin4 pgadmin4-apache2 # type in password and use default url $ pgadmin4
In the dashboard, check the total/active as