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'

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'