Top 10 Query to Check Connection Details in Postgres

November 8, 2021

In this PostgreSQL tutorial, we will discuss about connection details like, max connections, active / inactive connections, kill sessions, increase max connections in PostgreSQL.

How to Enable / Disable Archive Mode in Postgres

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

Check all connections in 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;

To Check max connection limit in Postgres database.

 SHOW max_connections;

SELECT current_setting('max_connections');

SELECT *
FROM   pg_settings
WHERE  name = 'max_connections';

To check connections by databases

SELECT datname, numbackends FROM pg_stat_database;

Terminate / Kill connections in PostgreSQL

SELECT pg_terminate_backend(PID);

To delete idle connections every 5 minutes

alter system set idle_in_transaction_session_timeout='5min';

To change max connections

postgres=# alter system set max_connections = 200;
postgres=# SELECT current_setting('shared_buffers');
postgres=# ALTER SYSTEM SET shared_buffers='128MB';
restart the postgresql service.

Must Read: