Force drop db while others may be connected in postgresql

Question

I need to remove a database in postgresql DB. How can I do it even if there are active connections? I need a sort of -force flag, that will drop all connections and then the DB.

How can I implement it?

I'm using dropdb currently, but other tools are possible.

Answer 1

You can't drop postgres database while clients are connected to it. Quite robust way to work around it, is

1. Make sure noone can connect to this database

   update pg_database set datallowconn = 'false' where datname = 'mydb';

2. Force disconnection of all clients connected to this database.

   For postgres < 9.2:

   SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb';

   for postgres versions >= 9.2 change procpid to pid:

   SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

3. Drop it

   DROP DATABASE mydb;

Steps 1 and 2 require superuser privileges, step 3 requires database owner privilege.

You can't do it all using only dropdb utility - which is a simple wrapper around DROP DATABASE server query.

Answer 2

There is a way to do this with the shell utilities dropdb & pg_ctl (or pg_ctlcluster in Debian and derivates). But @filiprem's method is more accurate for several reasons:

• It only disconnects users from the database in question.
• It does not to restart the whole cluster.
• It prevents immediate reconnects, possibly spoiling the dropdb command.

I quote man pg_ctlcluster:

With the --force option the "fast" mode is used which rolls back all active transactions, disconnects clients immediately and thus shuts down cleanly. If that does not work, shutdown is attempted again in "immediate" mode, which can leave the cluster in an inconsistent state and thus will lead to a recovery run at the next start. If this still does not help, the postmaster process is killed. Exits with 0 on success, with 2 if the server is not running, and with 1 on other failure conditions. This mode should only be used when the machine is about to be shut down.

pg_ctlcluster 9.1 main restart --force

or

pg_ctl restart -D datadir -m fast

or

pg_ctl restart -D datadir -m immediate

immediately followed by:

dropdb mydb

Possibly in a script for immediate succession.