Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer
    
Thanks for the hint. I'll try to get this apporach to work. Currently I'm facing the problem that the "\c postgres" does not work: ERROR: syntax error at or near "\" ZEILE 1: ...OT LIKE '%SELECT pg_terminate_backend(procpid)%'; \c postgre... ^ –  Patrick Cornelissen Apr 16 '13 at 12:42
1  
Simply don't use psql as you do - -c is supposed to take a single command only. Use a proper script file with -f instead. –  dezso Apr 16 '13 at 12:47
    
yes, but the problem is that I had to substitute a few variables before. Now I just create a temp file and use that as script. Now it works. Thanks! –  Patrick Cornelissen Apr 16 '13 at 13:16
1  
You can pass variables to psql like psql -v var=1 and then reference them as :var from the script. –  dezso Apr 16 '13 at 13:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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