Postgres ERROR: database “test” is being accessed by other users

Question

Getting the following error when trying to drop a PostgreSQL DB say "test"

postgres=# DROP DATABASE test;
ERROR:  database "test" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Solution

You can use pg_terminate_backend to kill open connections with a query:

PostgresVersion >=9.2

SELECT 
   pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE
   pg_stat_activity.datname = 'test'
AND pid <> pg_backend_pid()

PostgresVersion <9.2

SELECT 
   pg_terminate_backend(pg_stat_activity.procpid)
FROM 
   pg_stat_activity
WHERE 
   pg_stat_activity.datname = 'test'
AND procpid <> pg_backend_pid();

where 'test' is your databasename

Comments

[Leo Antunes]

this is a really bad answer: it will very likely cause data loss or even corruption to unrelated databases on your node. You should never "kill -9" any DB process unless you know exactly what you are doing. As pointed out by @FatFreddy, there are postgres-specific ways of achieving this without breaking any safety guarantees.

[Antony Hatchkins]

@LeoAntunes What pg_terminate_backend actually does is kill -15 8481 so this answer isn't that much wrong (it does the same, just in a more peremptory manner), but it is more explicit about what goes on under the hood.

[Leo Antunes]

@AntonyHatchkins no. There's a world of difference between kill -15 and kill -9, starting with the fact that one can be caught (and even ignored) while the other cannot. My comment stands: suggesting SIGKILL in the context of DBs is reckless at best.

[Antony Hatchkins]

@LeoAntunes I absolutely agree on this particular point. SIGKILL is definitely evil when applied to an process with a db connection.