Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Pg_Terminate_Backend() To Kill A Connection In Postgresql

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'
   ;



Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Sequence