How to list active connections on PostgreSQL?

SqlPostgresqlDatabase ConnectionListings

Sql Problem Overview

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)

Sql Solutions

Solution 1 - Sql

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

Solution 2 - Sql

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.

Solution 3 - Sql

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.

Solution 4 - Sql

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

Categories
Recommended Sql Solutions
Recommended Postgresql Solutions
Recommended Database Connection Solutions

Previous Solution:

Checking if type == list in python

Python

Next Solution:

Laravel 5 – Remove Public from URL

Php.HtaccessLaravelMampLaravel 5

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTregoregView Question on Stackoverflow
Solution 1 - SqlTregoregView Answer on Stackoverflow
Solution 2 - SqlNeeraj BansalView Answer on Stackoverflow
Solution 3 - SqlAbdollahView Answer on Stackoverflow
Solution 4 - Sqlsantosh tiwaryView Answer on Stackoverflow