I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive access.

This is for unit testing, and tests are only run manually, so there is no danger involved. Only old dead connections will be affected.

There are no other users connecting to these unittest databases.

The old dead connections come from developing. This happens all the time when a test that is being written or fails does not exit clean.

If someone also needs to keep locked out other users for a while after disconnecting them in a production scenario, see Scott Marlowe's answer below: https://dba.stackexchange.com/a/6184/2024

See also this similar question on dba: How to drop all connections to a specific database without stopping the server?

2 Answers 2


You could try connecting to the database as the postgres user and running:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )    -- 1. don't terminate your own session
    AND datname =                     -- 2. don't terminate connections to 
    (SELECT datname                   --    other databases in the cluster
       FROM pg_stat_activity
      WHERE procpid = pg_backend_pid( )

update An even better query gets rid of the subselect:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )
    AND datname = current_database( );
  • 2
    don't forget to REVOKE the CONNECT permissions, otherwise the users create new connections before you have exclusive access. Sep 26, 2011 at 18:19
  • @Frank Heikens - Good catch. I had keyed on "manual unit test" but if there are others connecting besides the individual doing the unit test then "revoke connect on <datname> from ..." would be essential.
    – gsiems
    Sep 26, 2011 at 20:53
  • 2
    In PostgreSQL 9.2, procpid was renamed to pid, so watch out for that. Nov 6, 2012 at 5:48
  • Beyond doing a REVOKE with the user in question, I had to also REVOKE ..... public - something to watch out for! Oct 22, 2014 at 8:33
  • 1
    on 9.3 it seems that pg_stat_activity.procpid is now called pg_stat_activity.pid. worked A-OK otherwise.
    – JL Peyret
    Nov 17, 2015 at 4:26

The issue here is twofold, first you need to disconnect those users, and second you need to keep them out of your server. Rather than revoking connect perms, I usually use pg_hba.conf to refuse new connects from certain machines and / or users, then just do a pg_ctl -m fast stop;pg_ctl start to drop all current connections. With slony doing DDL changes this is pretty much a necessity or you'll get deadlocks all over the place.

  • 7
    I always use a single role that allows CONNECT and is inherited by all other roles. REVOKE connect for this single role and you're done. Wrap it in a function with pg_terminate_backend() and you're in control when you have to stop all current connections. Sep 27, 2011 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.