Nightly we dump our Production DB and load it into our Staging and Dev environments.
A bash script is taking care of that and the steps to achieve this are the following:
download the dump from the cloud
drop the active connections to the DB
revoke any connections to the DB
drop the DB
reload (recreate) the DB
This is the query I use for dropping the active conns:
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$DB' AND pid <> pg_backend_pid();
And the one for revoking incoming conns:
REVOKE CONNECT ON DATABASE $DB FROM PUBLIC;
The problem is that almost every night, the script fails with the following:
ERROR: database "my_db" is being accessed by other users
And I'm not sure why is this happening since I reject the existing and then revoke any further connections.
Is it possible that the execution from dropping the connections to dropping the DB is too fast, which doesn't give enough time for some connections to finish getting dropped? I am adding a 2 sec delay after the
REVOKE to test this out tonight, but I was wondering if there is a less hackish, and more sophisticated way of solving this.