utils for viewing and killing queries

pg_stat_activity.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;