How to find list of active sessions/connections in PostgreSQL database

In this article, we will see how to find list of active sessions/connections in PostgreSQL database.

We can find list of active/inactive sessions/connections by querying against PostgreSQL system view pg_stat_activity.

Query to get list of active sessions in PostgreSQL:

SELECT pid as Processs_id, usename as user_name,
datname as database_name,client_addr as client_address,
application_name,backend_start,state,state_change from pg_stat_activity;

The above query will give all inactive and active sessions. To get only active sessions

SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active';

Or simply execute below query:

SELECT * FROM pg_stat_activity WHERE state = 'active';

pg_stat_activity Columns description:

process_id – process ID of this backend
username – name of the user logged into this backend
database_name – name of the database this backend is connected to
client_address – IP address of the client connected to this backend
application_name – name of the application that is connected to this backend
backend_start – time when this process was started. For client backends, this is the time the client connected to the server.
state – current overall state of this backend. Possible values are:

active
idle
idle in transaction
idle in transaction (aborted)
fastpath function call
disabled

state_change – time when the state was last changed