Clash Royale CLAN TAG#URR8PPP

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

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.

DROP DATABASE db_name

How can I solve the problem?

10 Answers
10

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.

pg_stat_activity

SELECT pg_terminate_backend(pid int)

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.

procpid

pid

; drop database TARGET_DB;

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.

pid

procpid

current_database()

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.

REVOKE

CONNECT

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

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

pg_terminate_backend(int)

You can get all running backends using the system view pg_stat_activity

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

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.

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.

pg_stat_activity

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.

procpid

pid

procpid

pid

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

PostgreSQL 9.2 and above:

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

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

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

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;


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://stackoverflow.com/a/3185413/3223785

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

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

After that it was possible to drop the database

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.