How to list active connections on PostgreSQL?

Question

Is there a command in PostgreSQL to select active connections to a given database?

psql states that I can't drop one of my databases because there are active connections to it, so I would like to see what the connections are (and from which machines)

Possible duplicate of [How can you get the active users connected to a postgreSQL database via SQL?](http://stackoverflow.com/questions/464623/how-can-you-get-the-active-users-connected-to-a-postgresql-database-via-sql) — Brad Koch, Aug 09 '16 at 14:57
Great! i search (badly) and don't found anything. Can i do something to close others idle connection? — Speaker, May 23 '20 at 22:50

score 521 · Accepted Answer · answered Dec 12 '14 at 02:23

521

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

answered Dec 12 '14 at 02:23

Tregoreg

15,364
13
47
68

45

If you would like to limit it to just one database, you can use _SELECT * FROM pg_stat_activity WHERE datname = 'dbname';_ – user2182349 Apr 21 '17 at 16:59
1

How can i get the active database connection from the specific backed service? – GunasekaranR Jul 18 '18 at 11:07
2

And how about after running `pg_terminate_backend` and my app is still able to run query against the db but I could not see the new connections in pg_Stat_activity? – takacsot Sep 25 '19 at 12:05
if you want to make the output more human readable, run ``\x on;`` first. – Mawardy Dec 21 '20 at 08:19

score 70 · Answer 2 · edited Jan 29 '20 at 15:05

70

Following will give you active connections/ queries in postgres DB-

SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active';

You may use 'idle' instead of active to get already executed connections/queries.

edited Jan 29 '20 at 15:05

pdoherty926

10,116
2
34
61

answered Feb 02 '18 at 05:38

Neeraj Bansal

2,132
13
8

1

Does idle means connection is active?. If I m releasing connection, still will it be listed as idle? – Shivam Kubde Aug 21 '19 at 12:31
1

Yes @ShivamKubde but as 'idle', and the query above only show 'active' connections, so remove the `WHERE ...` clause and to be able to see what connections are active or idle add the column `state` to the `SELECT` clause – Mariano Ruiz Jan 29 '20 at 20:20
hey i am trying to understand why PGSQl throws me the following error: [2021-04-08 09:52:04] [53300] FATAL: sorry, too many clients already. is there someone familiar with the problem please ? – Rene Chan Apr 08 '21 at 01:54

Abdollah · Answer 3 · 2020-03-07T06:42:28.220

33

SELECT * FROM pg_stat_activity WHERE datname = 'dbname' and state = 'active';

Since pg_stat_activity contains connection statistics of all databases having any state, either idle or active, database name and connection state should be included in the query to get the desired output.

edited Mar 07 '20 at 06:42

answered Feb 25 '20 at 13:31

Abdollah

3,619
3
26
41

score 6 · Answer 4 · answered Nov 10 '21 at 08:35

You can check connection details in Postgres using pg_stat_activity. You can apply filter to satisfy your condition. Below are queries. References: https://orahow.com/check-active-connections-in-postgresql/

SELECT * FROM pg_stat_activity WHERE state = 'active';

select * from pg_stat_activity where state = 'active' and datname = 'REPLACE_DB_NAME_HERE';

How to list active connections on PostgreSQL?

4 Answers4

Linked