Have you ever tried to drop or rename a postgres database or even create another database from one as a template, only to be confronted by the dreaded message:
postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.
So, we kill those sessions off with something like the below SQL that will kill all sessions that are connected to that database:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()pg_stat_activity.datname = ‘TARGET_DB’
AND pg_stat_activity.datname = ‘A_DATABASE’;
Job done, now we can create our database… But wait!
postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.
Someone has beaten us too it and reconnected!
If you are like me, the only logical solution is to try to do the above statements really really fast. Unfortunately, I normally lose, so I need a way to stop sessions from reconnecting to the database that doesn’t involve changing config files etc.
Fortunately, the simple solution is to disallow all connections to that database and we can do it with this line:
update pg_database set datallowconn = false where datname = ‘a_database’;
Once you are finished, you just need to set it back to True and the database will be open for connections again.