X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 66DC0D1BB72 for ; Thu, 5 Feb 2004 21:53:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05528-10 for ; Thu, 5 Feb 2004 17:53:22 -0400 (AST) Received: from busybox.pixar.com (busybox.pixar.com [138.72.18.213]) by svr1.postgresql.org (Postfix) with ESMTP id 62C41D1D120 for ; Thu, 5 Feb 2004 17:53:16 -0400 (AST) Received: from pixar.com (dreadnok.pixar.com [138.72.16.110]) by busybox.pixar.com (8.12.8/8.12.8) with ESMTP id i15Lr2Hw019619 for ; Thu, 5 Feb 2004 13:53:02 -0800 Message-ID: <4022BB3E.4040802@pixar.com> Date: Thu, 05 Feb 2004 13:53:02 -0800 From: Mark Harrison User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: pg-watchdog Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/65 X-Sequence-Number: 12239 Here is the final version of my script that will watch for disconnected client machines and kill their associated server side processes. If I'm doing anything really stupid I would appreciate hearing about it. If it's useful to anyone, feel free to pass it along. Thanks, Mark #!/bin/sh # the next line restarts using wish \ exec tclsh8.3 "$0" "$@" # pg-watchdog -- make sure postgres clients are reachable. # # This program is a watchdog for postgresql. It monitors all # postgresql client processes and kills them if the host upon # which they are running becomes inaccessible over the network. # # This is necessary because if a machine goes down "hard" or # if a network cable becomes disconnected, it can be several # hours before postgres will be informed of this (for details, # see chapter 23 of W. Richard Steven's TCP/IP Illustrated, # volume 1). If a postgres client on that machine has a # transaction lock, all other clients will be blocked waiting # for that lock to be released. # # This program uses the Scotty package available at: # http://wwwhome.cs.utwente.nl/~schoenw/scotty # If you do not wish to install Scotty you can change # the icmp command to instead use whatever flavor of # ping is available on your system. # # Please send feedback, improvements, etc, to # Mark Harrison, mh@pixar.com package require Tnm proc doit {} { after 60000 doit set pids("") "" unset pids("") set lines [exec ps auxww | awk {$11=="postgres:" {print $2 "/" $14}}] foreach s [split $lines \n] { set x [split $s /] set pid [lindex $x 0] set ip [lindex $x 1] if {[regexp {[0-9]+[.][0-9]+[.][0-9]+[.][0-9]+} $ip]} { lappend pids($ip) $pid } } if {[array size pids] > 0} { set times [icmp -timeout 20 echo [lsort [array names pids]]] foreach pair $times { set ip [lindex $pair 0] set time [lindex $pair 1] if {$time == -1} { foreach pid $pids($ip) { puts "killing $pid from $ip" exec kill $pid } } } } } after 0 doit vwait forever -- Mark Harrison Pixar Animation Studios