How to drop a PostgreSQL database if there are active connections to it?

Question

I need to write a script that will drop a PostgreSQL database. There may be a lot of connections to it, but the script should ignore that.

The standard DROP DATABASE db_name query doesn't work when there are open connections.

How can I solve the problem?

Lefthanded

What version of PostgreSQL are you on? – Ravenravening

Problem: Whilst you may kill the sessions connected to the database, they may reconnect so quickly that you still cannot drop the database. Happily this post shows how to lock out new connections, so you can then kill the current connections and drop the database as per plan: dba.stackexchange.com/questions/11893/… – Necrophobia

I found this answer over on dba.stackexchange to be very helpful dba.stackexchange.com/a/11895/163539 -- succinct yet sufficiently explanatory. – Keppel

Answer 1

This will drop existing connections except for yours:

Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND procpid <> pg_backend_pid();

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one your trying to drop.

Note the renaming of the procpid column to pid. See this mailing list thread.

Ravenravening

And of course, be sure to do that from a db connection that is not a connection to 'TARGET_DB', otherwise you get 'ERROR'. A 'postgres' connection works well. – Potshot

Works great, just need to remember that you cannot be connected to this DB! Use Disconnect on the database in pgAdmin. – Gwenore

Actually it would disconnect clients one by one, and if you client is in the middle of the list it will be disconnected too. As a result, some connections will stay alive. So, the right answer is by Craig Ringer (see below). SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pg_stat_activity.pid <> pg_backend_pid(); – Wycliffite

How can I disconnect the connections after they've finished with their current transaction and then drop the table(s) in question? – Gambill

In my case clients would reconnect quickly, so putting this just before ; drop database TARGET_DB; worked well in my case to make sure the db was gone by the time things started retrying. – Eal

datid, not pid for later versions – Eucalyptus

pg_terminate_backend() has ruinous effects on pgpool. Use pg_cancel_backend() if pgpool is in your stack. pgpool.net/mediawiki/index.php/… – Hangdog

I only get function pg_terminate_backend(integer) does not exist. – Jannelle

ERROR: column pg_stat_activity.procpid does not exist – Teethe

I would even pay money for a dropdb --force. – Antiparticle

@TorstenBronger granted! github.com/postgres/postgres/commit/… – Baneberry

See this answer for Postgres 13+. Force added. – Abbreviated

Answer 2

In PostgreSQL 9.2 and above, to disconnect everything except your session from the database you are connected to:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();

In older versions it's the same, just change pid to procpid. To disconnect from a different database just change current_database() to the name of the database you want to disconnect users from.

You may want to REVOKE the CONNECT right from users of the database before disconnecting users, otherwise users will just keep on reconnecting and you'll never get the chance to drop the DB. See this comment and the question it's associated with, How do I detach all other users from the database.

If you just want to disconnect idle users, see this question.

Grassi

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pg_stat_activity.pid <> pg_backend_pid(); – Wycliffite

Answer 3

PostgreSQL 13 introduced FORCE option.

DROP DATABASE

DROP DATABASE drops a database ... Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.

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.

DROP DATABASE db_name WITH (FORCE);

Teenybopper

Any solution for Postgresql 11? – Niela

It's a pitty this FORCE can't be added to pg_dump somehow, it would make restoring easier. – Nielson

Answer 4

Easy Peasy.

I just restart the service in Ubuntu to disconnect connected clients.

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;

Pastelki

This is overengineering. Simply press the reset button. – Pointdevice

dropdb DB_NAME No need to go into postgres. – Cranio

@BicameralMind You would need to use dropdb mydb --force when there are active users. – Pastelki

/usr/lib/postgresql/11/bin/dropdb: unrecognized option '--force' – Niela

@Niela --force becomes available in postgresql 13 and above. – Pastelki

Answer 5

You could kill all connections before dropping the database using the pg_terminate_backend(int) function.

You can get all running backends using the system view pg_stat_activity

I'm not entirely sure, but the following would probably kill all sessions:

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'

Of course you may not be connected yourself to that database

Stratovision

Answer 6

Depending on your version of postgresql you might run into a bug, that makes pg_stat_activity to omit active connections from dropped users. These connections are also not shown inside pgAdminIII.

If you are doing automatic testing (in which you also create users) this might be a probable scenario.

In this case you need to revert to queries like:

 SELECT pg_terminate_backend(procpid) 
 FROM pg_stat_get_activity(NULL::integer) 
 WHERE datid=(SELECT oid from pg_database where datname = 'your_database');

NOTE: In 9.2+ you'll have change procpid to pid.

Neckband

Ths is what I was looking for but for (assuming 9.2 and beyond) you have to remove the reference to pg_stat_activity and change procpid to pid. – Seigneury

After changing procpid to pid this snippet works on 9.3. – Neckband

even without removing pg_stat_activity? I was getting an error on 9.2 – Seigneury

OK. Now I understand, that was a typo. Thanks! – Neckband

From 9.3 and up SELECT pg_terminate_backend(pid) FROM pg_stat_get_activity(NULL::integer) WHERE datid=(SELECT oid from pg_database where datname = 'your_database'); – Instability

Answer 7

I noticed that postgres 9.2 now calls the column pid rather than procpid.

I tend to call it from the shell:

#!/usr/bin/env bash
# kill all connections to the postgres server
if [ -n "$1" ] ; then
  where="where pg_stat_activity.datname = '$1'"
  echo "killing all connections to database '$1'"
else
  echo "killing all connections to database"
fi

cat <<-EOF | psql -U postgres -d postgres 
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
${where}
EOF

Hope that is helpful. Thanks to @JustBob for the sql.

Contractor

Answer 8

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pid)FROM pg_stat_activity WHERE datname = 'YOUR_DATABASE_NAME_HERE'

Shrievalty

Won't that terminate the active connecion too? – Frick

Answer 9

Here's my hack... =D

# Make sure no one can connect to this database except you!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "UPDATE pg_database SET datallowconn=false WHERE datname='<DATABASE_NAME>';"

# Drop all existing connections except for yours!
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<DATABASE_NAME>' AND pid <> pg_backend_pid();"

# Drop database! =D
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "DROP DATABASE <DATABASE_NAME>;"

I put this answer because include a command (above) to block new connections and because any attempt with the command...

REVOKE CONNECT ON DATABASE <DATABASE_NAME> FROM PUBLIC, <USERS_ETC>;

... do not works to block new connections!

Thanks to @araqnid @GoatWalker ! =D

https://mcmap.net/q/54967/-postgresql-temporarily-disable-connections

Exportation

Answer 10

In Linux command Prompt, I would first stop all postgresql processes that are running by tying this command sudo /etc/init.d/postgresql restart

type the command bg to check if other postgresql processes are still running

then followed by dropdb dbname to drop the database

sudo /etc/init.d/postgresql restart
bg
dropdb dbname

This works for me on linux command prompt

Eldridge

This is no good if you have many databases and only want to drop connections for a single DB. This would kill all connections. It's a bit "sledge hammer-y". – Persia

@Persia true but remember we are restarting all the connections and stopping them completely – Eldridge

Answer 11

In my case i had to execute a command to drop all connections including my active administrator connection

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()

which terminated all connections and show me a fatal ''error'' message :

FATAL: terminating connection due to administrator command SQL state: 57P01

After that it was possible to drop the database

Lennox

Answer 12

Nothing worked for me except, I loggined using pgAdmin4 and on the Dashboard I disconnected all connections except pgAdmin4 and then was able to rename by right lick on the database and properties and typed new name.

Zenger

It's better to rely on the command line than a software UI for database management if you need to control the connection or other database level commands / utilities – Oly