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.
DROP DATABASE db_name query doesn’t work when there are open connections.
How can I solve the problem?
This will drop existing connections except for yours:
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.