How to drop a PostgreSQL database if there are active connections to it?

I need to write a script that will drop a PostgreSQL database. There may be a lot of connections to it, but the script should ignore that.

The standard DROP DATABASE db_name query doesn't work when there are open connections.

How can I solve the problem?

Solution 1:

This will drop existing connections except for yours:

Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND procpid <> pg_backend_pid();

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.

Note the renaming of the procpid column to pid. See this mailing list thread.

Solution 2:

In PostgreSQL 9.2 and above, to disconnect everything except your session from the database you are connected to:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();

In older versions it's the same, just change pid to procpid. To disconnect from a different database just change current_database() to the name of the database you want to disconnect users from.

You may want to REVOKE the CONNECT right from users of the database before disconnecting users, otherwise users will just keep on reconnecting and you'll never get the chance to drop the DB. See this comment and the question it's associated with, How do I detach all other users from the database.

If you just want to disconnect idle users, see this question.

Solution 3:

PostgreSQL 13 introduced FORCE option.

DROP DATABASE

DROP DATABASE drops a database ... Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.

FORCE

Attempt to terminate all existing connections to the target database. It doesn't terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.

DROP DATABASE db_name WITH (FORCE);

Solution 4:

You could kill all connections before dropping the database using the pg_terminate_backend(int) function.

You can get all running backends using the system view pg_stat_activity

I'm not entirely sure, but the following would probably kill all sessions:

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'

Of course you may not be connected yourself to that database