The following are some commands commonly used by DBAs as tools for checking PostgreSQL databases
Uptime service PostgreSQL
SELECT (now() – pg_postmaster_start_time()) as Uptime;
List Current Process
select pid as PROCESS_ID, usename as USER, datname as DATABASE, client_addr as HOST, application_name as APP_NAME, backend_start as STARTED_AT, state as STATE from pg_stat_activity;
Count Current Process
select count(*) from pg_stat_activity;
List Active Query by Timing
select pid as PID, datname as DATABASE, usename as USER, client_addr as HOST, now() – query_start as DURATION, state as STATE, query as QUERY from pg_stat_activity where state=’active’ order by duration desc;
Check Current Connections vs Max Connections
select * from (select count(*)connections from pg_stat_activity)as a,(select setting as max_connections from pg_settings where name=’max_connections’) as b;
Check Current Connections Count per-User, Per-host Sort by higest connections
select count(*),client_addr as HOST,state as STATE,usename as USER from pg_stat_activity group by state,usename,client_addr order by count desc;
Check query based on PID
select pid as PROCESS_ID, usename as USER, datname as DATABASE, query, state from pg_stat_activity where pid=’1010′;
Kill a Query
SELECT pg_cancel_backend(PID);
Kill idle connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = ‘databasename’ AND pid <> pg_backend_pid() AND state in (‘idle’);
Check top 10 query based on Durations
SELECT pid, AGE(clock_timestamp(), query_start) as DURATION, usename as USER, query, state FROM pg_stat_activity WHERE state != ‘idle’ AND query NOT ILIKE ‘%pg_stat_activity%’ ORDER BY DURATION desc limit 10