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

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.

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.