DEV Community

leroykayanda
leroykayanda

Posted on • Edited on

postgres troubleshooting queries

get long running queries

select pg_stat_activity.usename, pid, pg_stat_activity.query_start, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity where state = 'active' and (now() - pg_stat_activity.query_start) > interval '60 minute'
Enter fullscreen mode Exit fullscreen mode

query to find query that has locked rows

SELECT concat(client_addr, ':', client_port) AS origin_of_the_statement, datname AS database_name, usename AS database_user, pid, usename, pg_blocking_pids (pid) AS blocked_by_pid, concat(wait_event_type, ':', wait_event) wait_event, query AS blocked_statement FROM pg_stat_activity WHERE CARDINALITY(pg_blocking_pids (pid)) > 0 AND pid <> pg_backend_pid();
Enter fullscreen mode Exit fullscreen mode

Then to get process details

SELECT datname,pid,usename,query_start,wait_event_type,wait_event,state,query
FROM pg_stat_activity where pid=27581;

To get queries that have been running for a long time.

select pg_stat_activity.usename, pid, pg_stat_activity.query_start, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity where state = 'active' and (now() - pg_stat_activity.query_start) > interval '0 minute'
Enter fullscreen mode Exit fullscreen mode

Top comments (0)