How to see active connections and "current activity" in PostgreSQL 8.4

Question

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.

from PostgreSQL 9.0 on you will even see the name of the application causing the problem. maybe this is helpful for future checks. — postgresql007, Apr 05 '10 at 18:19

score 196 · Answer 1 · edited Feb 27 '19 at 00:12

196

OK, got it from someone else. This query should do the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename';

edited Feb 27 '19 at 00:12

Community

1

answered Mar 31 '10 at 23:33

EMP

5,172
10
37
33

Wayne Conrad · Answer 2 · 2023-03-07T18:33:13.340

34

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

edited Mar 07 '23 at 18:33

answered Nov 29 '10 at 18:29

Wayne Conrad

675
1
7
20

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 May 22 '19 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 May 22 '19 at 15:45
3

On buster, the debian package is called [pgtop](https://packages.debian.org/buster/pgtop) – phihag Mar 07 '20 at 12:28
The link is dead. – RonJohn Mar 07 '23 at 17:38

score 19 · Answer 3 · answered Feb 09 '16 at 20:11

19

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();

answered Feb 09 '16 at 20:11

Anvesh

674
7
3

`query` and `pid` were named `current_query` and `procpid`, and `state` is not available in `pg_stat_activity` prior to PostgreSQL 9.2. – Uyghur Lives Matter May 03 '16 at 03:27

Hearen · Answer 4 · 2019-03-20T08:22:14.687

4

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

edited Mar 20 '19 at 08:22

answered Mar 20 '19 at 08:07

Hearen

141
4

score 2 · Answer 5 · edited Jun 11 '20 at 10:02

2

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.

ScreenShot

edited Jun 11 '20 at 10:02

Community

1

answered Jun 25 '18 at 15:46

dcvetkov

21
2

score 1 · Answer 6 · answered Nov 26 '20 at 11:15

1

DBeaver is also showing the active/idle/total server sessions very nicely.

answered Nov 26 '20 at 11:15

Bodo

11
3

How to see active connections and "current activity" in PostgreSQL 8.4

6 Answers6