Drop database from remote that has connected users

Question

I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent version. For that I need to drop the db. But when I kill the connections to the DB, the clients immediately reconnect within a second. :-(

So I try to disallow new connections, kill the connections and then drop the DB. For that I write a small script. The essential part is that:

SQL="UPDATE pg_database set datallowconn = 'false' where datname = '$DB_NAME'; 
     SELECT pg_terminate_backend(procpid) FROM pg_stat_activity where datname = '$DB_NAME' and current_query 
     NOT LIKE '%SELECT pg_terminate_backend(procpid)%'; DROP DATABASE $DB_NAME;"

psql -h $DB_HOST -W -U $DB_USER "$DB_NAME" -c "$SQL"

But psql is not able to perform the drop database in a multiline statement...

So I tried (just for the sake of trying it out) to do the drop db in a second statement. But as I expected that complains that it can't drop the DB because the DB does not allow new connections...

Isn't there a reliable way to perform an action like this?

I can't simply stop all clients that are connected, because I don't know in my script which clients are connected from where. So that option is not possible.

score 3 · Accepted Answer · answered Apr 16 '13 at 12:21

First, you can issue DROP DATABASE; from a multiline script, but it has to be the only statement of its transaction:

CREATE DATABASE droptest;

\c droptest

CREATE TABLE a (id integer);

Then run the script:

SELECT id
FROM a;

\c postgres

DROP DATABASE droptest;

It results in:

 id 
----
(0 rows)

psql (9.1.9, server 9.0.6)
WARNING: psql version 9.1, server version 9.0.
         Some psql features might not work.
You are now connected to database "postgres" as user "postgres".
DROP DATABASE

And the documentation says:

DROP DATABASE cannot be executed inside a transaction block.

This means it can't be run like psql -1 -f the_above_script.

So, now my solution (hopefully):

First disable connections on the DB:

REVOKE CONNECT ON database_name FROM ALL;

Then do what you do now:

SELECT pg_terminate_backend(procpid) 
FROM pg_stat_activity 
WHERE 
    datname = '$DB_NAME' and 
    current_query NOT LIKE '%SELECT pg_terminate_backend(procpid)%'
;

DROP DATABASE database_name;

Drop database from remote that has connected users

1 Answers1