How to get list of sessions on PostgreSQL database

In this article, we will see how to get list of sessions on PostgreSQL database. We will see all sessions in PostgreSQL and sessions specific to the database. We can get session by running queries against pg_stat_activity.

Syntax to get list of sessions on PostgreSQL database:

1. Following query will give sessions on entire PostgreSQL cluster.

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       query
from pg_stat_activity;

2. Following query gives sessions only for database ‘r2schools’.

select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       application_name,
       state
from pg_stat_activity where datname='r2schools';

3. In the same way, if we want to get list of sessions for particular user in PostgreSQL server.

select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       application_name,
       state
from pg_stat_activity where usename='james';

Few columns of pg_stat_activity:

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