2

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:

  1. download the dump from the cloud

  2. drop the active connections to the DB

  3. revoke any connections to the DB

  4. drop the DB

  5. 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.

  • 4
    Have you tried by swapping 2. and 3.? backends can be very eager to reconnect... – dezso Nov 5 '19 at 8:36
  • See it like dezso - first prevent new connections - then close existing ones – eagle275 Nov 5 '19 at 9:15
  • Hmm, good idea! I'll share the outcome after a few tests of nightly runs. – Milkncookiez Nov 5 '19 at 10:23
1

In a same context, I was prefering to change the password of the applicative login, during the drop/restoration process. Is it possible for you?

Make a query to list active connection from pg_stat_activity table to identify the culprit(s).

Hope this helps, Thomas

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.