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?
Best Answer
This will drop existing connections except for yours:
Query
pg_stat_activity
and get the pid values you want to kill, then issueSELECT pg_terminate_backend(pid int)
to them.PostgreSQL 9.2 and above:
PostgreSQL 9.1 and below:
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 topid
. See this mailing list thread.