Tag: SHOW FULL PROCESSLIST in PostgreSQL

MySQL has show full processlist; to see a list of currently running queries.

PostgreSQL has a similar shell like select * from pg_stat_activity;

1. Simple Command

select * from pg_stat_activity;

2. Second Option

SELECT user, pid, client_addr, query, query_start, NOW() - query_start AS elapsed
FROM pg_stat_activity
WHERE query != '<IDLE>'
-- AND EXTRACT(EPOCH FROM (NOW() - query_start)) > 1
ORDER BY elapsed DESC;

3. Third Option

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

4. Finding Long Processing Time / Stuck Queries

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

5. Cancelling Long Running Queries

SELECT pg_cancel_backend(pid);

6. Kill Process ID / Terminate Stuck Queries

SELECT pg_terminate_backend(pid);

Reference: SHOW FULL PROCESSLIST in PostgreSQL