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.