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
lurkingbehind 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@gmail.com> 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@invenius.de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651