Обсуждение: How to force disconnections from a Perl script?
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
On Mon, Feb 19, 2007 at 11:10:55AM -0500, Kynn Jones wrote: > 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". Why irrecoverably? If the command fails, you just wait and try it again. You could use the pg_stat tables to work out who is connected and use pg_cancel_backend() to kill them. You could "kill -INT" them yourself. You could change the pg_hba.conf to forbid logging in and then bouncing the server. Hope this gives you some ideas. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
> Why irrecoverably? If the command fails, you just wait and try it > again. > You could use the pg_stat tables to work out who is connected and use > pg_cancel_backend() to kill them. You could "kill -INT" them yourself. > You could change the pg_hba.conf to forbid logging in and then bouncing > the server. I was going to suggest the same things you did, but then I thought better... the OP is running the thing at night from a client box, not on the DB, so restart and process listing is probably off limits... There's 0 chance somebody will close at midnight it's open connection forgotten when he left office, so wait and retry would not do any good ;-) And pg_stat will only show you running queries, not the idle connections. If you only could list all the connection's pids in a client you could loop and kill them all. Of course the loop would kill itself too if not careful enough ;-) Cheers, Csaba.
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
Вложения
Csaba Nagy <nagy@ecircle-ag.com> writes:
> And pg_stat will only show you running queries, not the idle
> connections.
Nonsense. pg_stat_activity + "kill -TERM" should solve this problem
reasonably well. Some of us don't trust kill -TERM 100%, which is why
it's not currently exposed as a standard function, but if you're using
a reasonably recent PG release it's probably safe.
regards, tom lane
On Tue, 2007-02-20 at 03:43, Tom Lane wrote: > Nonsense. pg_stat_activity + "kill -TERM" should solve this problem > reasonably well. Some of us don't trust kill -TERM 100%, which is why > it's not currently exposed as a standard function, but if you're using > a reasonably recent PG release it's probably safe. Oh, OK... so pg_stat_activity shows all connections now ? Or it was always like that ? For some reason I thought it will only show connections where a transaction is in progress. Idle can mean there is nothing executing at all... are those shown too ? Cheers, Csaba.