Created
October 11, 2017 02:33
-
-
Save unpluggedcoder/803262c99420924f2f660ebeed284d60 to your computer and use it in GitHub Desktop.
[Kill postgresql session/connection] #sql #postgresql
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
-- You can use pg_terminate_backend() to kill a connection. You have to be superuser to use this function. This works on all operating systems the same. | |
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' | |
; | |
--Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections: | |
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username; | |
--If you're using Postgres 8.4-9.1 use procpid instead of pid | |
SELECT | |
pg_terminate_backend(procpid) | |
FROM | |
pg_stat_activity | |
WHERE | |
-- don't kill my own connection! | |
procpid <> pg_backend_pid() | |
-- don't kill the connections to other databases | |
AND datname = 'database_name' | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment