Psql Check Connections Recipe

psql_connections_check.sql

WITH connection_stats AS (
  SELECT
    state,
    COUNT(*) AS count
  FROM
    pg_stat_activity
  GROUP BY
    state
),
total_connections AS (
  SELECT
    COUNT(*) AS total_conn
  FROM
    pg_stat_activity
)
SELECT
  tc.total_conn,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
  ((SELECT setting::int FROM pg_settings WHERE name = 'max_connections') - tc.total_conn) AS available_conn,
  COALESCE(cs.count, 0) AS active,
  COALESCE((SELECT count FROM connection_stats WHERE state = 'idle in transaction'), 0) AS idle_in_transaction,
  COALESCE((SELECT count FROM connection_stats WHERE state = 'idle'), 0) AS idle
FROM
  total_connections tc
LEFT JOIN
  connection_stats cs ON cs.state = 'active';