I have a Perl script that runs every night and updates a local Pg
database, sitting on a Linux server. (I'll refer to this database as
"mydb" in the following.)
The update process takes about 1 hour, so the script first builds a
temporary database called mydb_tmp. Once mydb_tmp is built and passes
a battery of tests, the script deletes mydb and renames mydb_tmp to
mydb.
The script is quite solid and has been performing flawlessly for
several months now, with one exception: it fails irrecoverably
whenever some user forgets to disconnect from mydb at the time that
the script attempts to delete it (or rename it, for that matter). The
error is "ERROR: database "mydb" is being accessed by other users".
Now, we, the users of mydb, know very well that we should disconnect
from it at the end of the day, but inevitably one of us forgets
(including myself on occasion, I'm sorry to admit).
My question is, how can I make the script handle this situation more
robustly? (At the moment I do get an email message alerting to this
failure when it happens, but I'd like to eliminate this type of
failure altogether. It is, after all, a pretty silly reason for this
script to fail.)
The ideal solution, from my point of view, would be for the script to
forcibly disconnect everyone from mydb at the time of updating it,
maybe sending a warning a minute or so beforehand, but I have not hit
upon a way to do this. (I should point out that, in the case of this
particular database, mydb, such forcible disconnections would cause no
major disruption to anyone.)
I would greatly appreciate your ideas and suggestions.
FWIW, the script is currently run by my uid, but I could have it run
by the postgres user, if that's of any help here.
Thanks in advance!
kj