The pg_stat_activity table provides real-time information on active database sessions, including running queries, duration, user, and application. It’s useful for monitoring and troubleshooting performance issues.
The following query identifies queries running for more than 5 minutes:
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';
To stop a query gracefully, use:
SELECT pg_cancel_backend(__pid__);
(It may take a few seconds to take effect.)
If you want to force quit, the equivalent of a kill -9
, you can use pg_cancel_backend
instead. See:
SELECT pg_terminate_backend(__pid__);
To terminate all currently running queries (excluding the current session), run:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
Warning: This will forcefully terminate all active queries, which may cause disruptions.