PostgreSQL: Temporarily disable connections

Question

I have a script in PostgreSQL which restores test database from dump every night. The database is accessed by app servers and processes with connection pool which keeps a few connections alive at all times.

So the script restores dump into my_temp_database. Then it should rename my_database to my_old_database, my_temp_database to my_database, and eventually drop my_old_database.

How can I disconnect all clients, superuser or not, from my_database, so that it can be renamed? How I can I temporarily prevent them from reconnecting?

Is there a better way to do what I need?

Answer 1

To mark database 'applogs' as not accepting new connections:

update pg_database set datallowconn = false where datname = 'applogs';

Another possibility would be to revoke 'connect' access on the database for the client role(s).

Disconnect users from database = kill backend. So to disconnect all other users from "applogs" database, for example:

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'applogs' and procpid <> pg_backend_pid();

Once you've done both of those, you are the only user connected to 'applogs'. Although there might actually be a delay before the backends actually finish disconnecting?

Answer 2

After you have terminated the current active connections you can also issue this command that will only allow super users to login. This assumes you are ok with all super users having access still. Hopefully you don't hand out super user rights to just anyone.

ALTER DATABASE your_db CONNECTION LIMIT 0;

Answer 3

Starting PostgreSQL 9.5 we finally can:

ALTER DATABASE db WITH ALLOW_CONNECTIONS false;

Answer 4

If you are connected in session with the DB that you want to dissallow connecting to, and to stay connected in the same session after you disallow connections, then use this :

UPDATE pg_database SET datallowconn = false WHERE datname = '_db_name_' ;

... which allows you to do stuff without any other connection happening until you re-enable

But if you use this, it must be from in a session in another DB :

ALTER DATABASE _db_name_ WITH ALLOW_CONNECTIONS false ;

(( i realise this is almost a summary of the answers above ))

Answer 5

I had a different usage scenario, in which I wanted to disable a DB for everyone (including superuser) and forever, but not plainly drop it just yet, to be able to reactivate it quickly if needed.

This worked fine on an old 8.3 Postgres:

UPDATE pg_database SET datallowconn=false WHERE datname='my_db_name';