Hi, you could let the script look into the output of "ps aux". Open idle connections are usually show like this: postgres 18383 0.0 0.6 18596 4900 ? Ss 16:38 0:00 postgres: dbuser database hostname(39784) idle in transaction Then you can simply collect the PIDs and kill these processes (just kill, not with "-9"). If there are no demons lurking behind them reestablishing the connections, this is a quite reliable way to get rid of connections in a graceful way. Regards, Frank. On Mon, 19 Feb 2007 11:10:55 -0500 "Kynn Jones" <kynnjo@xxxxxxxxx> thought long, then sat down and wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606 Mail: frank.finner@xxxxxxxxxxx Telefax: 0271 231 8608 Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651
Attachment:
pgpJ9aMOw48Fz.pgp
Description: PGP signature
