Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
In addition to filiprem's answer: for Postgres 8.3 you should even use pg_cancel_backend instead of pg_terminate_backend. For Postgres 8.3: SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb'; – Leonard May 12 '14 at 13:26
up vote 54 down vote accepted

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

  1. From shell, connect to your server as superuser. Do not use the database you want to drop.

    psql -h localhost postgres postgres

  2. Make sure noone can connect to this database

    UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

  3. 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';

  4. Drop it


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

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

share|improve this answer
is there a way to do this with dropdb? – Alex Jan 30 '12 at 12:41
no, see my update. – filiprem Jan 30 '12 at 12:49
What permissions I need in order to perform this operation? – Alex Jan 30 '12 at 13:09
Let me suggest doing the first step like ALTER DATABASE mydb CONNECTION LIMIT 1. I would not touch the catalogs directly unless absolutely necessary. – dezso May 13 '14 at 8:22
@Dadani: These are SQL commands; You run them from PgAdmin, or from psql, or from any other SQL-capable client. – filiprem May 15 at 14:24

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 need 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


pg_ctl restart -D datadir -m fast


pg_ctl restart -D datadir -m immediate

immediately followed by:

dropdb mydb

Possibly in a script for immediate succession.

share|improve this answer
Not only is this less than ideal as it kicks the full postgres instance but it is not guaranteed to work. It is possible for a client to connect between the time you restart the server and attempt to run dropdb again. @filiprem 's answer above disables all connections to the database prior to disconnecting and will keep other databases up. – jcm Oct 22 '14 at 5:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.