Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -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.

PostgreSQL 13 added:

DROP DATABASE mydb WITH (FORCE);

The manual:

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.

This will fail if the current user has no permissions to terminate
other connections. Required permissions are the same as with
pg_terminate_backend, described in Section 9.27.2. This will
also fail if we are not able to terminate connections.

In PostgreSQL 12 and earlier, you cannot drop a database while clients are connected to it.

At least, not with the dropdb utility - which is only a simple wrapper around DROP DATABASE server query.

Quite robust workaround follows:

Connect to your server as superuser, using psql or other client. Do not use the database you want to drop.

psql -h localhost postgres postgres

Now using plain database client you can force drop database using three simple steps:

-
Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).

/* Method 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

/* Method 2: use ALTER DATABASE. Superusers still can connect!
ALTER DATABASE mydb CONNECTION LIMIT 0; */

-
Force disconnection of all clients connected to this database, using pg_terminate_backend.

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

 /* For old versions of PostgreSQL (up to 9.1), change pid to procpid:

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

-
Drop the database.

DROP DATABASE mydb;

Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.

StackExchange Database Administrators Q#11893, answer score: 355