Check Postgresql connections

Postgresql database provides 2 main configurations to limit the incoming connections. This is done in the file postgresql.conf

max_connections = 200                   # (change requires restart)
superuser_reserved_connections = 3      # (change requires restart)

Notes here refers to Postgresql version 11.

Following are common sql commands to monitor connections.

List number of connections

SELECT count(distinct(numbackends)) 
  FROM pg_stat_database; 

List connections by database

SELECT datname, numbackends 
  FROM pg_stat_database; 

    datname     | numbackends
----------------+-------------
 postgres      |           1
 template1     |           0
 template0     |           0
 shop01        |           0
 tutorial_php  |           0
 tutorial_linux|           0
 helpdesk      |          27
 telegram      |           6
(8 rows)

List the connections on a specific database

SELECT * FROM pg_stat_activity 
  WHERE datname='helpdesk';

List number of session that is active or idle.

SELECT state, count(*) FROM pg_stat_activity  

  WHERE pid <> pg_backend_pid() 

  GROUP BY 1 

  ORDER BY 1;

        state        | count

---------------------+-------

 active              |    55

 idle                |    38

 idle in transaction |     2

                     |     5

(4 rows)

These will provide data for planning and risk mitigations.