How to list active connections on PostgreSQL?
Oh, I just found that command on PostgreSQL forum:
SELECT * FROM pg_stat_activity;
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';
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.
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.