Postgresql Connection Info

Some days you just want to know who is connecting up to your database. The following SQL works for 9.3 Postgresql.

SELECT COUNT(1) as num_connections,
COUNT(CASE WHEN s."state" = 'idle' THEN 1 ELSE NULL END) as num_idle_connections,
COUNT(CASE WHEN s."state" = 'idle in transaction' THEN 1 ELSE NULL END) as num_idle_in_tx_connections,
max(CASE WHEN s."state" = 'idle in transaction' THEN GREATEST(now(),s.query_start)-s.query_start ELSE NULL END) as age_of_oldest_tx,
s.datname,
s.client_addr
FROM pg_stat_activity s
WHERE s.pid != pg_backend_pid()
and s.client_addr is not null
GROUP BY s.client_addr, s.datname
ORDER BY 1 DESC;

This query shows you total connections from by ip address along with some info that I find helpful - namely how many connections are connected but idle and how many are idle in transactions (and if so for how long).

Enjoy Reading This Article?