Home ยป Force drop db while others may be connected

Force drop db while others may be connected

Solutons:


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.

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

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.

Related Solutions

Split string into Array of Arrays [closed]

If I got correct what you want to receive as a result, then this code would make what you want: extension Array { func chunked(into size: Int) -> [[Element]] { return stride(from: 0, to: self.count, by: size).map { Array(self[$0 ..< Swift.min($0 + size,...

Retrieving n rows per group

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT...

Don’t understand how my mum’s Gmail account was hacked

IMPORTANT: this is based on data I got from your link, but the server might implement some protection. For example, once it has sent its "silver bullet" against a victim, it might answer with a faked "silver bullet" to the same request, so that anyone...

What is /storage/emulated/0/?

/storage/emulated/0/Download is the actual path to the files. /sdcard/Download is a symlink to the actual path of /storage/emulated/0/Download However, the actual files are located in the filesystem in /data/media, which is then mounted to /storage/emulated/0...

How can I pass a command line argument into a shell script?

The shell command and any arguments to that command appear as numbered shell variables: $0 has the string value of the command itself, something like script, ./script, /home/user/bin/script or whatever. Any arguments appear as "$1", "$2", "$3" and so on. The...

What is pointer to string in C?

argv is an array of pointers pointing to zero terminated c-strings. I painted the following pretty picture to help you visualize something about the pointers. And here is a code example that shows you how an operating system would pass arguments to your...

How do mobile carriers know video resolution over HTTPS connections?

This is an active area of research. I happen to have done some work in this area, so I'll share what I can about the basic idea (this work was with industry partners and I can't share the secret details ๐Ÿ™‚ ). The tl;dr is that it's often possible to identify an...

How do I change the name of my Android device?

To change the hostname (device name) you have to use the terminal (as root): For Eclair (2.1): echo MYNAME > /proc/sys/kernel/hostname For Froyo (2.2): (works also on most 2.3) setprop net.hostname MYNAME Then restart your wi-fi. To see the change, type...

How does reverse SSH tunneling work?

I love explaining this kind of thing through visualization. ๐Ÿ™‚ Think of your SSH connections as tubes. Big tubes. Normally, you'll reach through these tubes to run a shell on a remote computer. The shell runs in a virtual terminal (tty). But you know this part...

Difference between database vs user vs schema

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account. See this post on Stack Overflow:...

What’s the output of this code written in java?

//if you're using Eclipse, press ctrl-shift-f to "beautify" your code and make it easier to read int arr[] = new int[3]; //create a new array containing 3 elements for (int i = 0; i < 3; i++) { arr[i] = i;//assign each successive value of i to an entry in...

How safe are password managers like LastPass?

We should distinguish between offline password managers (like Password Safe) and online password managers (like LastPass). Offline password managers carry relatively little risk. It is true that the saved passwords are a single point of failure. But then, your...

Can anyone tell me why this program go to infinite times?

while (i <= 2) { while (i > 0) { a = a + b; i--; <- out the inner while loop when i = 0 } printf("%d", a); i++; <- at here, the i==0 each time, so infinity loop } Because your nested loop always restores the value of i to 0, And 0 <= 2 is always...

How to conditionally do something if a command succeeded or failed

How to conditionally do something if a command succeeded or failed That's exactly what bash's if statement does: if command ; then echo "Command succeeded" else echo "Command failed" fi Adding information from comments: you don't need to use the [ ... ] syntax...

How to turn JSON array into Postgres array?

Postgres 9.4 or newer Obviously inspired by this post, Postgres 9.4 added the missing function(s): Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing! json_array_elements_text(json) jsonb_array_elements_text(jsonb) To unnest the JSON array....