postgres_admin_queries.sql

-- view interesting information for all queries for a user
select
    pid,
    client_addr,
    application_name,
    (current_timestamp - query_start) as duration,
    state,
    query
from
    pg_stat_activity
where
    usename = 'some_user'
order by duration desc;

-- kill all queries for some user
select
    pg_cancel_backend(pid)
from
    pg_stat_activity
where
    usename = 'some_user' and
    pid <> pg_backend_pid()
;

-- kill particular query with given pid
select pg_terminate_backend(pid);

-- kill all queries longer than a certain duration
select pg_cancel_backend(pid) from (
select
    pid,
    client_addr,
    application_name,
    (current_timestamp - query_start) as duration,
    state,
    query
from
    pg_stat_activity
where
    usename = 'some_user'
) x
where duration > '00:07:30';

-- check slave lag
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;