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'
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();
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'
Top comments (0)