Programming My Life - postgres

Programming My Life

[TIL] Killing Long Running Postgres Queries

I recently had issues with long running postgres operations and found these two commands, one for finding long running queries, and one for killing them. My issue turned out to be with an UPDATE instead of a query, but I'm noting this for future use. Note that the interval can be updated in this query to be more flexible for shorter/longer time frames.

Finding Long Running Queries

SELECT
    pid,
    usename AS username,
    datname AS database_name,
    state,
    now() - pg_stat_activity.query start AS running_time,
    query
FROM
    pg_stat_activity
WHERE
    state = 'active'
    AND now() - pg_stat_activity.query start > interval '5 minutes'
ORDER BY
    running_time DESC;

Killing Long Running Queries

SELECT pg_terminate_backend(<pid>);