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)
Oh, I just found that command on PostgreSQL forum:
SELECT * FROM pg_stat_activity;
To limit to just one database:
SELECT * FROM pg_stat_activity WHERE datname = 'dbname';
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?
\x on; first.
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.
WHERE ... clause and to be able to see what connections are active or idle add the column state to the SELECT clause
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.
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';
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;
