Postgresql – Automatically reload DB nightly

pg-restorepostgresqlpostgresql-9.6

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.

Best Answer

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

Related Question