PostgreSQL - querying for users connections and session times

postgresql-connections.md

Report on connected users and session time

SELECT pid as process_id, 
   usename as username, 
   datname as database_name, 
   client_addr as client_address, 
   application_name,
   backend_start,
   state,
   state_change
FROM pg_stat_activity;

Get number of used connections and configuration for superuser and normal connections.

SELECT * FROM
(SELECT count(*) used_connections FROM pg_stat_activity) q1,
(SELECT setting::int connections_reserved_for_superusers FROM pg_settings WHERE name=$$superuser_reserved_connections$$) q2,
(SELECT setting::int maximum_configured_connections FROM pg_settings WHERE name=$$max_connections$$) q3;

Aggregation of all postgres sessions per their status (how many are idle, how many doing something)

SELECT state, count(*) FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY 1 ORDER BY 1;