Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Viewing the Activity Table on PostgreSQL in detail and Terminating a pid
-- To be able to use these in full detail, you must have the necesarry role authentication
-- Find activity list on DB
select * from pg_stat_activity
-- Find pid, user name, query, backend start date, transaction start date, query start date and the longest running sql times that are active and idle in transaction
SELECT pid, usename, query, backend_start, xact_start, query_start, (now() - query_start) AS run_time FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction') ORDER BY query_start
-- Terminate the sql with the given pid
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = {{database_name}}
AND pid in({{pid}});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment