How to Check Active Connections in PostgreSQL?

In previous PostgreSQL tutorial we discussed about enabling archive in postgres, here will discuss about PostgreSQL connection details to list number of connections active on the database.

Listing all Connections on PostgreSQL

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_hostname, 
       application_name,
       backend_start,
       state,
       wait_event,
       state_change
from pg_stat_activity;

select *
from pg_stat_activity
where datname = 'REPLACE_DB_NAME_HERE';

Listing active connections on PostgreSQL

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_hostname, 
       application_name,
       backend_start,
       state,
       wait_event,
       state_change
from pg_stat_activity WHERE state = 'active';

postgres=# SELECT * FROM pg_stat_activity WHERE state = 'active';

Kill / Terminate connections in PostgreSQL

SELECT pg_terminate_backend(PID);
postgres=# SELECT pg_terminate_backend(22069);
 pg_terminate_backend
----------------------
 t
(1 row)

Hope the above queries will help you to identify idle and active connection details in PostgreSQL database. Based on your analysis, either you can kill the session if it is in hung state or idle from a long time.