Kill a postgresql session/connection

Question

How can I kill all my postgresql connections?

I'm trying a rake db:drop but I get:

ERROR:  database "database_name" is being accessed by other users DETAIL:  There are 1 other session(s) using the database. 

I've tried shutting down the processes I see from a ps -ef | grep postgres but this doesn't work either:

kill: kill 2358 failed: operation not permitted 

Answer 1

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