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 687 · Accepted Answer · answered Dec 12 '14 at 02:23

687

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

answered Dec 12 '14 at 02:23

Tregoreg

18,872
15
48
69

60

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
2

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
1

if you want to make the output more human readable, run ``\x on;`` first. – Mawardy Dec 21 '20 at 08:19
Short n' sweet & focused on remotes: SELECT datname,usename,application_name,client_addr FROM pg_stat_activity; – n8thanael Aug 25 '22 at 13:48
`SELECT COUNT(1) FROM pg_stat_activity;` for getting just the number of connections. – EvgenyKolyakov Sep 01 '22 at 09:10

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

93

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

9,895
4
37
68

answered Feb 02 '18 at 05:38

Neeraj Bansal

2,580
16
8

2

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
2

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
@ReneChan it is due to too many connections to your database. Normally the maximum is 100. You must have created more than that. – Anandakrishnan Nov 07 '22 at 18:06

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

42

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

4,579
3
29
49

score 11 · 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';

score 11 · Answer 5 · answered Aug 08 '22 at 11:27

11

If you would like to use PgAdmin (for me it is more than convenient), you could do these simple steps. Glad if this is helps

SELECT * FROM pg_stat_activity;

answered Aug 08 '22 at 11:27

Igor S.

301
3
7

How to list active connections on PostgreSQL?

5 Answers5

Linked