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.
OK, got it from someone else. This query should do the trick:
select * from pg_stat_activity where datname = 'mydatabasename';
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