Search Postgresql Archives

Re: How to force disconnections from a Perl script?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux