PostgreSQL – How to Detach All Other Users from a Database

postgresql

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?

Best Answer

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( );
Related Question