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

Question

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?

Answer 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.