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)

Answer 1

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

Answer 2

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;

Answer 3

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.

Answer 4

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';

Answer 5

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.