Postgres SQL: Sessions

Global overview:

v8.3

SELECT
    sum(CASE WHEN waiting THEN 1 ELSE 0 END) AS waiting,
    sum(CASE WHEN current_query='' THEN 1 ELSE 0 END) AS idle,
    sum(CASE WHEN current_query=' in transaction' THEN 1 ELSE 0 END) AS idletransaction,
    sum(CASE WHEN current_query='' THEN 1 ELSE 0 END) as unknown,
    sum(CASE WHEN NOT waiting AND current_query NOT IN ('', ' in transaction', '') THEN 1 ELSE 0 END) AS active
FROM pg_stat_activity WHERE procpid != pg_backend_pid() and datname = current_database();

v9.3

all sessions (full)

select * from pg_stat_activity

all sessions (lite)

select datname, usename, state, waiting, query from pg_stat_activity;

Sessions state per DB

select datname, state, count(*) from pg_stat_activity group by datname, state order by 1, 2;

nb sessions waiting

select waiting, count(*) from pg_stat_activity group by waiting;

nb sessions waiting per DB

select datname, waiting, count(*) from pg_stat_activity group by datname, waiting order by 1, 2;

querries per DB

select datname, query, count(*) from pg_stat_activity group by datname, query order by 1,3 desc