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
