Skip to content

Instantly share code, notes, and snippets.

@anozimada

anozimada/pg-kill-session.sql

Created February 9, 2017 03:55
Show Gist options
  • Save anozimada/8a4dec4403dda018a154a04182c1ac31 to your computer and use it in GitHub Desktop.
Save anozimada/8a4dec4403dda018a154a04182c1ac31 to your computer and use it in GitHub Desktop.
Download ZIP
Kill postgresql session/connection
Raw
pg-kill-session.sql
--Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections:
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
--PostgreSQL 9.2 and above:
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'
;
--PostgreSQL 9.1 and below:
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'
;
--ref: http://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment