Postgres Long-Running Processes

Thanks Chandler Thompson for this!

Identify Long Running Processes

SELECT pg_stat_activity.pid,
pg_class.relname,
pg_locks.transactionid,
pg_locks.granted,
age(now(), pg_stat_activity.query_start) AS "age"
FROM pg_stat_activity,
     pg_locks LEFT OUTER
JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_stat_activity.query <> ''
  AND pg_locks.pid = pg_stat_activity.pid AND pg_stat_activity.pid <> pg_backend_pid()
ORDER BY age DESC
LIMIT 10;

or

SELECT
  usename AS username,
  application_name,
  datname AS database,
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  wait_event,
  wait_event_type,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

If you need to stop a process try to CANCEL first, as Terminate can have some unwanted effects.

Locks

select 
    relname as relation_name, 
    query, 
    pg_locks.* 
from pg_locks
join pg_class on pg_locks.relation = pg_class.oid
join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid

Cancel Process

SELECT pg_cancel_backend(${pid});

Terminate Process

Only if absolutely necessary

SELECT pg_terminate_backend(${pid});