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?
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?