Created
February 13, 2018 12:50
-
-
Save okanmenevseoglu/868b950847b8db931a967b8b8051a255 to your computer and use it in GitHub Desktop.
Viewing the Activity Table on PostgreSQL in detail and Terminating a pid
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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