Session Monitoring - PostgreSQL

 

Session Monitoring

SELECT now()::timestamp(0) as time,datname,pid,(now() – query_start)::time(0) AS runtime,EXTRACT(EPOCH FROM (now() – query_start))*1000::INT AS runtime_millisecs,query_start::timestamp(0),usename,client_addr,state, query FROM pg_stat_activity Where pid <> pg_backend_pid() AND now() – query_start > ’15 milliseconds’::INTERVAL ORDER BY EXTRACT(EPOCH FROM (now() – query_start))::INT DESC;

SELECT pid,query::varchar(100),datname, now() – pg_stat_activity.query_start AS duration,usename,client_addr,state,wait_event_type,wait_event

FROM pg_stat_activity

WHERE state!=’idle’ and pid <> pg_backend_pid() — and now() – query_start > ‘1 minutes’::interval — and query not like ‘%VACUUM%’

order by duration desc;

SELECT pid,query::varchar(100),datname, now() – pg_stat_activity.query_start AS duration,usename,client_addr,state,wait_event_type,wait_event

FROM pg_stat_activity

WHERE pid <> pg_backend_pid()

order by duration desc;