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.
6 Answers
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
) - Run
pg_top
- So hard to find a windows executable. You (almost) never have the full GCC suite or Mingw on a Windows server (not my choice, don't blame me).oligofren– oligofren2019-05-22 15:29:49 +00:00Commented May 22, 2019 at 15:29
- @oligofren Running Postgresql on Windows is a bit of an adventure, if for no other reason than that so many of the utilities and tools are written for Linux.Wayne Conrad– Wayne Conrad2019-05-22 15:45:05 +00:00Commented May 22, 2019 at 15:45
- 3
-
Reference taken from this article.
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();
-
query
andpid
were namedcurrent_query
andprocpid
, andstate
is not available inpg_stat_activity
prior to PostgreSQL 9.2.ohmu– ohmu2016-05-03 03:27:14 +00:00Commented May 3, 2016 at 3:27
Two ways to monitor in Ubuntu 18.04, just for reference
One using pg_top
:
$ sudo apt-get install ptop $ pg_top # similar to top as others mentioned
Two using pgAdmin4
:
$ sudo apt-get install pgadmin4 pgadmin4-apache2 # type in password and use default url $ pgadmin4
In the dashboard, check the total/active as
PostgreSQL ASH Viewer (PASH Viewer) provides graphical view of active session history data. https://github.com/dbacvetkov/PASH-Viewer It's free and open source.