How to list active connections on PostgreSQL?

574.1k views Asked by At

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)

5

There are 5 answers

6
Tregoreg On BEST ANSWER

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;
0
Igor S. On

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;

example

0
Abdollah On
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.

0
santosh tiwary On

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';
3
Neeraj Bansal On

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.