163

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.

0
238

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:

  1. 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; */
    
  2. 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'; */
    
  3. 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.

1
  • This mehtod works even if you have pgAdmin opened which could be important while developing... – M46 Feb 24 at 15:36
15

Using @filiprem's answer in a my case and simplifying it:

-- Connecting to the current user localhost's postgres instance
psql

-- Making sure the database exists
SELECT * from pg_database where datname = 'my_database_name'

-- Disallow new connections
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'my_database_name';
ALTER DATABASE my_database_name CONNECTION LIMIT 1;

-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'my_database_name';

-- Drop database
DROP DATABASE my_database_name
1
  • 1
    on the UPDATE line it should be false not 'false'. – Scott P. Apr 16 '20 at 19:56
11

Postgres 13 adds the FORCE option for DROP DATABASE. See filiprem's answer.

The shell utility dropdb is basically just a wrapper around the SQL command and inherits the same option. So it's simple and reliable from the shell now, too:

dropdb mydb --force

Or short:

dropdb mydb -f

For Postgres 12 and older, there is a way with the shell utilities dropdb & pg_ctl (or pg_ctlcluster in Debian and derivates), too. But filiprem's method is superior there 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

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.

1
  • 4
    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. – Jim Mitchener Oct 22 '14 at 5:28
3

For those finding this question now, 8 years later, PostgreSQL 13 finally adds the --force flag this question asks for:

DROP DATABASE database_to_drop WITH (FORCE)

or

dropdb [-f/--force] database_to_drop

will

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.

https://www.postgresql.org/docs/13/sql-dropdatabase.html

0
0

If you're on something like RDS where connections without a database selected put you into the DB you asked to be created by default you can do this variant to get around yourself being the last open connection.

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist; 

 CREATE DATABASE temporary_db_that_shouldnt_exist with OWNER your_user; 

 \connect temporary_db_that_shouldnt_exist 
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'the_db_you_want_removed'; 


 DROP DATABASE IF EXISTS the_db_you_want_removed; 
 -- 
 -- Name: the_db_you_want_removed; Type: DATABASE; Schema: -; Owner: your_user 
 -- 

 CREATE DATABASE savings_champion WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; 


 ALTER DATABASE the_db_you_want_removed OWNER TO your_user; 

 \connect the_db_you_want_removed 

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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