From rjessil@yahoo.com Mon Oct 15 12:56:11 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C201B9F9A8D for ; Mon, 15 Oct 2007 09:56:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76022-02 for ; Mon, 15 Oct 2007 09:56:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56403.mail.re3.yahoo.com (web56403.mail.re3.yahoo.com [216.252.111.82]) by postgresql.org (Postfix) with SMTP id 9F5B49F9A9A for ; Mon, 15 Oct 2007 09:56:07 -0300 (ADT) Received: (qmail 65525 invoked by uid 60001); 15 Oct 2007 12:56:06 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=W/NP0hH5jbAObmdBRK5Yf/CFiz6E3UraPG26ZQVIyBV5vqW9Bt3txH6yLL8wXdnEmtXdBELp9g538q21SF9KFAnbAgmm7igeARBCHQkgRgz4U5Dtx2/6iRZl51j7aYVRSz8w/vAPUYnR91vNhG/pHC63PWGDR4ooMJCEgr+4Z5k=; X-YMail-OSG: HPn4CwYVM1loh8savAQfbZ53gnG0Fd2wEKxVSNF9ViJeeyoudDHs2Xk2xKLUoOo6Jg-- Received: from [198.119.134.32] by web56403.mail.re3.yahoo.com via HTTP; Mon, 15 Oct 2007 05:56:06 PDT Date: Mon, 15 Oct 2007 05:56:06 -0700 (PDT) From: Jessica Richard Subject: Is there a way to kill a connection from the pg_stat_activitly list? To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1558268327-1192452966=:65196" Content-Transfer-Encoding: 8bit Message-ID: <23085.65196.qm@web56403.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/115 X-Sequence-Number: 27029 --0-1558268327-1192452966=:65196 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit When you see a hanging Postgres connection (or a job running so long and you don't want to continue any more) from select * from pg_stat_activity and you want to disconnect it, how do you do it? thanks, Jessica --------------------------------- Check out the hottest 2008 models today at Yahoo! Autos. --0-1558268327-1192452966=:65196 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit When you see a hanging Postgres connection (or a job running so long and you don't want to continue any more) from

select * from pg_stat_activity

and you want to disconnect it,

how do you do it?

thanks,
Jessica


Check out the hottest 2008 models today at Yahoo! Autos. --0-1558268327-1192452966=:65196-- From tommy.gildseth@usit.uio.no Mon Oct 15 13:57:20 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E52EB9FA35A for ; Mon, 15 Oct 2007 10:57:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 31980-09 for ; Mon, 15 Oct 2007 10:57:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pat.uio.no (pat.uio.no [129.240.10.15]) by postgresql.org (Postfix) with ESMTP id 3BDE09FA195 for ; Mon, 15 Oct 2007 10:57:16 -0300 (ADT) Received: from mail-mx9.uio.no ([129.240.10.39]) by pat.uio.no with esmtp (Exim 4.67) (envelope-from ) id 1IhQ8q-0004Uf-AG; Mon, 15 Oct 2007 15:37:56 +0200 Received: from inhaca.uio.no ([129.240.202.141]) by mail-mx9.uio.no with esmtp (Exim 4.67) (envelope-from ) id 1IhPuj-0000dy-UJ; Mon, 15 Oct 2007 15:23:22 +0200 Message-ID: <471369C9.1060301@usit.uio.no> Date: Mon, 15 Oct 2007 15:23:21 +0200 From: Tommy Gildseth User-Agent: Thunderbird 2.0.0.6 (X11/20070728) MIME-Version: 1.0 To: Jessica Richard CC: pgsql-admin@postgresql.org Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? References: <23085.65196.qm@web56403.mail.re3.yahoo.com> In-Reply-To: <23085.65196.qm@web56403.mail.re3.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-UiO-ClamAV-Virus: No X-UiO-Spam-info: not spam, SpamAssassin (score=-4.8, required=12.0, autolearn=disabled, AWL=0.186, UIO_MAIL_IS_INTERNAL=-5) X-UiO-Scanned: 21A9652E8D3912C412C079BBAF95DB4C93517438 X-UiO-SPAM-Test: remote_host: 129.240.202.141 spam_score: -47 maxlevel 200 minaction 2 bait 0 mail/h: 2 total 523 max/h 38 blacklist 0 greylist 0 ratelimit 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/118 X-Sequence-Number: 27032 Jessica Richard wrote: > When you see a hanging Postgres connection (or a job running so long > and you don't want to continue any more) from > > select * from pg_stat_activity > > and you want to disconnect it, > > how do you do it? |pg_cancel_backend() |http://www.postgresql.org/docs/8.1/interactive/functions-admin.html -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 From jonah.harris@gmail.com Mon Oct 15 13:56:48 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7EB839F9B7D for ; Mon, 15 Oct 2007 10:56:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 45318-06 for ; Mon, 15 Oct 2007 10:56:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.243]) by postgresql.org (Postfix) with ESMTP id 57B4F9F9B13 for ; Mon, 15 Oct 2007 10:56:44 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so80098ana for ; Mon, 15 Oct 2007 06:56:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=NuZdKbolOJkKkL+YvWX2NIrfFYCrB+5HsEiLDt31YBY=; b=RJhHh1CLlo8yEEDaJw2VUtKerTVB2e7EYInHlTM0MxuuWGds2p3X/NAhL39m5GAUHtdrgVarJ4CyEL7fnk/UPPndXS+WJwMK1vPCCZTZQLY94naTLhiUEtTk0rYuEblIqSgWXzgMHGMcE26Gu8B0XVs4Fgp1LpcTYJ2Ya8OgBg0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=slKykXs9lzo3MqyKltNhnSQKzeo+Q0CffWmmCx0d0tm7JJVBBm1HBnXqMEhUc2C9IVFkHn/bb7i1MYLDWnRU6qWudOaTqXyNRkSdOfPvAXx4WOKEdVMm1f6fGZRuGqznMqhhY67E4X8loD4Qb3nYjueLJ1eLmASeNh3hmVLK5Uc= Received: by 10.142.102.5 with SMTP id z5mr1417869wfb.1192456215813; Mon, 15 Oct 2007 06:50:15 -0700 (PDT) Received: by 10.143.8.5 with HTTP; Mon, 15 Oct 2007 06:50:15 -0700 (PDT) Message-ID: <36e682920710150650i1ba6b1bfm2376f38537220547@mail.gmail.com> Date: Mon, 15 Oct 2007 09:50:15 -0400 From: "Jonah H. Harris" To: "Jessica Richard" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: pgsql-admin@postgresql.org In-Reply-To: <23085.65196.qm@web56403.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <23085.65196.qm@web56403.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/117 X-Sequence-Number: 27031 On 10/15/07, Jessica Richard wrote: > When you see a hanging Postgres connection (or a job running so long and you > don't want to continue any more) from > > select * from pg_stat_activity > > and you want to disconnect it, > > how do you do it? See pg_cancel_backend You should also look at using statement_timeout if this is a regular occurrence. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/ From rjessil@yahoo.com Mon Oct 15 17:18:29 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8C0539FA183 for ; Mon, 15 Oct 2007 14:18:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34860-07 for ; Mon, 15 Oct 2007 14:18:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56406.mail.re3.yahoo.com (web56406.mail.re3.yahoo.com [216.252.111.85]) by postgresql.org (Postfix) with SMTP id 2FBBB9FA153 for ; Mon, 15 Oct 2007 14:18:25 -0300 (ADT) Received: (qmail 70872 invoked by uid 60001); 15 Oct 2007 17:18:24 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=1r8CLZRO3In64k6el9XYBxi7/Q8+CPnAuSSk6thlXcvsa6vDAk+s848Nrapk3V0Q6kwc8r5oXADVunZPy/HAnx5qM5u9y1AQXFxYjo2ueE05Te5Aa5BSPZU6tIvrKWqJQme076wLQft3i9LCKpZ/hqiEc3xpAtEHIq4Bb0HuGe4=; X-YMail-OSG: ZVqvifEVM1m.9trD.4NUc30v9_YXemdLDFDLa8HH Received: from [198.119.134.32] by web56406.mail.re3.yahoo.com via HTTP; Mon, 15 Oct 2007 10:18:24 PDT Date: Mon, 15 Oct 2007 10:18:24 -0700 (PDT) From: Jessica Richard Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? To: Tommy Gildseth Cc: pgsql-admin@postgresql.org In-Reply-To: <471369C9.1060301@usit.uio.no> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1711225073-1192468704=:68410" Content-Transfer-Encoding: 8bit Message-ID: <382292.68410.qm@web56406.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/124 X-Sequence-Number: 27038 --0-1711225073-1192468704=:68410 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks a lot! "select pg_cancel_backend(procpid) " can end the current query for that user, but then this connection becomes IDLE, still connected. Is there a command for me to totally disconnect a user by procpid? Some times, I need to kick out a particular Postgres user completely. thanks Tommy Gildseth wrote: Jessica Richard wrote: > When you see a hanging Postgres connection (or a job running so long > and you don't want to continue any more) from > > select * from pg_stat_activity > > and you want to disconnect it, > > how do you do it? |pg_cancel_backend() |http://www.postgresql.org/docs/8.1/interactive/functions-admin.html -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend --------------------------------- Need a vacation? Get great deals to amazing places on Yahoo! Travel. --0-1711225073-1192468704=:68410 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks a lot!

"select pg_cancel_backend(procpid) " can end the current query for that user, but then this connection becomes IDLE, still connected.

Is there a command for me to totally disconnect a user by procpid? Some times, I need to kick out a particular Postgres user completely.

thanks


Tommy Gildseth <tommy.gildseth@usit.uio.no> wrote:
Jessica Richard wrote:
> When you see a hanging Postgres connection (or a job running so long
> and you don't want to continue any more) from
>
> select * from pg_stat_activity
>
> and you want to disconnect it,
>
> how do you do it?

|pg_cancel_backend()


|http://www.postgresql.org/docs/8.1/interactive/functions-admin.html

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


Need a vacation? Get great deals to amazing places on Yahoo! Travel. --0-1711225073-1192468704=:68410-- From scott.marlowe@gmail.com Mon Oct 15 17:34:34 2007 Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5FC409F9ABF for ; Mon, 15 Oct 2007 14:34:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 85489-10 for ; Mon, 15 Oct 2007 14:34:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 5C1AE9F9AB7 for ; Mon, 15 Oct 2007 14:34:29 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so922784ugf for ; Mon, 15 Oct 2007 10:34:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=9wq2EG/V2YjSNl2fW6gkiZtHmeK3IuVorPcmSF1F7yY=; b=W2NEmyJ1akeyASmNnrQZYVVvplGqRldMnZ6L4a1lVGuEv6+ggNSdV4CpxhCKO5wH0BjTUzGcbTCZ7200tPvq1wm4gXxRFz6ThnzOpANpqG5YcT57JVFtbk95kXbHaDtpNdh6TiD27cYlNN4SW5QlozW6K5zzrdkmONQSwmk3ufQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=tkmhh7GmetECWn81Nfph0PXXxuHX5U4gSD8LPxPCEXck2wZ6vhBelVXVLubCpKOi1HOprdoGPzYXRdW4qW71aLXpX9NS9SqfXuFRT4sP2N9UHh3VulbpiMBC2fM3FZjsg5CdEGz4Ip92wZ7aS5NEd2RPQfW1xmrMNRyh3o6YvhM= Received: by 10.67.115.1 with SMTP id s1mr8107880ugm.1192469667750; Mon, 15 Oct 2007 10:34:27 -0700 (PDT) Received: by 10.67.29.6 with HTTP; Mon, 15 Oct 2007 10:34:27 -0700 (PDT) Message-ID: Date: Mon, 15 Oct 2007 12:34:27 -0500 From: "Scott Marlowe" To: "Jessica Richard" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Tommy Gildseth" , pgsql-admin@postgresql.org In-Reply-To: <382292.68410.qm@web56406.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <471369C9.1060301@usit.uio.no> <382292.68410.qm@web56406.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/125 X-Sequence-Number: 27039 On 10/15/07, Jessica Richard wrote: > Thanks a lot! > > "select pg_cancel_backend(procpid) " can end the current query for that > user, but then this connection becomes IDLE, still connected. > > Is there a command for me to totally disconnect a user by procpid? Some > times, I need to kick out a particular Postgres user completely. From jonah.harris@gmail.com Mon Oct 15 17:35:04 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7FD169FA499 for ; Mon, 15 Oct 2007 14:35:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 36032-08 for ; Mon, 15 Oct 2007 14:34:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.181]) by postgresql.org (Postfix) with ESMTP id 6DDC19F9AB7 for ; Mon, 15 Oct 2007 14:34:52 -0300 (ADT) Received: by el-out-1112.google.com with SMTP id r27so356951ele for ; Mon, 15 Oct 2007 10:34:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=ZjWim4hvqKESt+J7TJ+uBSBMGpkmcm81QJfREsaVcFU=; b=FPRu24+HCdmQ1ac8CCktQDi4YCWBowyRxQhOtJawQb2vISWjqKWq5C2AqJNaAFpz73dpm4oD5V39c6+1H2zqU5Oq18E2yGcGNqNyQ1f35K4Df6TRqeTiWhCVl807anxiDNXKiQOznja57Yc53WS3YpzwEonhN3WLZQqcyReDwEI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=uLu1ikp+jSjr4inbYLL9eZTdHyXjbbvr9speDConm0Qq6UsXGejAedEpar8jrhfRKSMFeXsymxVzxTSK6BxyKn9exFNnv89sSiPzegjpeIHGjSSH5R7zke6PeIlF7+/SvXLSXXmtAxC6osQe3BnFl1HVllCsB2Tf4H8GGSb4ezc= Received: by 10.142.99.21 with SMTP id w21mr1620539wfb.1192469690527; Mon, 15 Oct 2007 10:34:50 -0700 (PDT) Received: by 10.143.8.5 with HTTP; Mon, 15 Oct 2007 10:34:50 -0700 (PDT) Message-ID: <36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com> Date: Mon, 15 Oct 2007 13:34:50 -0400 From: "Jonah H. Harris" To: "Jessica Richard" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Tommy Gildseth" , pgsql-admin@postgresql.org In-Reply-To: <382292.68410.qm@web56406.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <471369C9.1060301@usit.uio.no> <382292.68410.qm@web56406.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/126 X-Sequence-Number: 27040 On 10/15/07, Jessica Richard wrote: > Thanks a lot! > > "select pg_cancel_backend(procpid) " can end the current query for that > user, but then this connection becomes IDLE, still connected. > > Is there a command for me to totally disconnect a user by procpid? Some > times, I need to kick out a particular Postgres user completely. There used to be a pg_terminate_backend, but it was #ifdef'd out due to corruption concerns. Basically, all it did was: kill -TERM pid I'm not sure whether anyone has completed the research required to know if anything remains corrupted, but it is used occasionally. Best to do pg_cancel_backend and then kill -TERM. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/ From Kevin.Grittner@wicourts.gov Mon Oct 15 18:13:41 2007 Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 104FD9F9DB0 for ; Mon, 15 Oct 2007 15:13:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 71204-06 for ; Mon, 15 Oct 2007 15:13:36 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 043B29F9B65 for ; Mon, 15 Oct 2007 15:13:37 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 15 Oct 2007 13:13:35 -0500 Message-Id: <47136772.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Mon, 15 Oct 2007 13:13:23 -0500 From: "Kevin Grittner" To: "Jonah H. Harris" , "Jessica Richard" Cc: , "Tommy Gildseth" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? References: <471369C9.1060301@usit.uio.no> <382292.68410.qm@web56406.mail.re3.yahoo.com> <36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com> In-Reply-To: <36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/127 X-Sequence-Number: 27041 >>> On Mon, Oct 15, 2007 at 12:34 PM, in message <36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H. Harris" wrote:=20 >=20 > There used to be a pg_terminate_backend, but it was #ifdef'd out due > to corruption concerns. Basically, all it did was: >=20 > kill -TERM pid >=20 > I'm not sure whether anyone has completed the research required to > know if anything remains corrupted, but it is used occasionally. Best > to do pg_cancel_backend and then kill -TERM. =20 Where does pg_ctl kill fit in? =20 Is TERM the normal signal to use there, too? =20 Should the pg_ctl docs give some guidelines on the signals? =20 -Kevin =20 From rjessil@yahoo.com Tue Oct 16 11:56:08 2007 Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A18389FB25F for ; Tue, 16 Oct 2007 08:56:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 70081-01 for ; Tue, 16 Oct 2007 08:55:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56402.mail.re3.yahoo.com (web56402.mail.re3.yahoo.com [216.252.111.81]) by postgresql.org (Postfix) with SMTP id EC9789FB256 for ; Tue, 16 Oct 2007 08:56:01 -0300 (ADT) Received: (qmail 87507 invoked by uid 60001); 16 Oct 2007 11:56:00 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=R/lwPQYZE909GpPefFabhEZhHn8r+y6T1+iTO5hEvooju6bDJ/kTWkFqwLiXcB85pUw8F1BtnQKPy5zFQCRywdJZM+PNoOqbkE58D9/lJk+pGiBIR3feu/6GlhvYN67DzqoLqjXlXq55nlwY58WHsyoUO9+3+p3CXwJ5ziT8yzY=; X-YMail-OSG: JHKo0wwVM1lqkn_EhdIDo6qCmAaDAnNBOhte6UxT05I9eYnNWPlNLEEF7wp8SE2D0LuvxYrDzIvJYoiGrs5qymQSqz0LKyYnXxNzqe9PgNqpzvvCiZeIfGJJLB6PXjkxH2pCVycab2fDoPoNwCv7aj6N Received: from [198.119.134.32] by web56402.mail.re3.yahoo.com via HTTP; Tue, 16 Oct 2007 04:56:00 PDT Date: Tue, 16 Oct 2007 04:56:00 -0700 (PDT) From: Jessica Richard Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? To: Kevin Grittner , "Jonah H. Harris" Cc: pgsql-admin@postgresql.org, Tommy Gildseth In-Reply-To: <47136772.EE98.0025.0@wicourts.gov> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-122730445-1192535760=:86032" Content-Transfer-Encoding: 8bit Message-ID: <561035.86032.qm@web56402.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/138 X-Sequence-Number: 27052 --0-122730445-1192535760=:86032 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit If the connection is from the local machine, I can find it with "ps -ef | grep procpid", then kill it with Unix command "kill" outside Postgres... But I have many remote connections coming from different machines...it is hard to kill on the OS level outside Postgres on the postgres host... I am looking for something to kill a Postgres user connection within Postgres... Some thing like, you find the user connection with select * from pg_stat_activity...then you pick a procpid and kill right there... Thanks, Kevin Grittner wrote: >>> On Mon, Oct 15, 2007 at 12:34 PM, in message <36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H. Harris" wrote: > > There used to be a pg_terminate_backend, but it was #ifdef'd out due > to corruption concerns. Basically, all it did was: > > kill -TERM pid > > I'm not sure whether anyone has completed the research required to > know if anything remains corrupted, but it is used occasionally. Best > to do pg_cancel_backend and then kill -TERM. Where does pg_ctl kill fit in? Is TERM the normal signal to use there, too? Should the pg_ctl docs give some guidelines on the signals? -Kevin ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings --------------------------------- Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. --0-122730445-1192535760=:86032 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit If the connection is from the local machine, I can find it with "ps -ef | grep procpid", then kill it with Unix command "kill" outside Postgres...

But I have many remote connections coming from different machines...it is hard to kill on the OS level outside Postgres on the postgres host...

I am looking for something to kill a Postgres user connection within Postgres...
Some thing like, you find the user connection with select * from pg_stat_activity...then you pick a procpid and kill right there...

Thanks,


Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>>> On Mon, Oct 15, 2007 at 12:34 PM, in message
<36e682920710151034r2aaef401m5429e460ee0ac209@mail.gmail.com>, "Jonah H.
Harris" wrote:
>
> There used to be a pg_terminate_backend, but it was #ifdef'd out due
> to corruption concerns. Basically, all it did was:
>
> kill -TERM pid
>
> I'm not sure whether anyone has completed the research required to
> know if anything remains corrupted, but it is used occasionally. Best
> to do pg_cancel_backend and then kill -TERM.

Where does pg_ctl kill fit in?

Is TERM the normal signal to use there, too?

Should the pg_ctl docs give some guidelines on the signals?

-Kevin




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out. --0-122730445-1192535760=:86032-- From jonah.harris@gmail.com Tue Oct 16 12:00:21 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7A6FF9FB34B for ; Tue, 16 Oct 2007 09:00:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 76092-02 for ; Tue, 16 Oct 2007 08:59:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.224]) by postgresql.org (Postfix) with ESMTP id B61499FB33A for ; Tue, 16 Oct 2007 09:00:09 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so1144485nzf for ; Tue, 16 Oct 2007 05:00:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=3eoE8cVZ8kYk3bB+Y58XKsgJE1oEJVV6am0UYQKmnU0=; b=dzrCnu6LujDlWtolsnN1Q1fk8nvfLXLlELsJsZHnjfAx9FhPwuyG96305exXpCdYKALh7tbatL8uTwBlHGutw2qP6H9ng9vCa6AnopiE6NrqlNvavSeGTq+KF4pBftY1vlhobQtDbUssKtUQkTdoTaN3tE9OwohGp0e4JNiZ0BY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=iNpRxDaSOFHnXff3MGdIFur2FiTIXkUjK8CbqtU4oJzp/mFNsMExSF/2aCjFEzgM7eAjrlL+6Hr7i+ntS58GTYfdWb05n3iHAuIAEKli6gWBTWaefY/18dymN4anngR5LAjsXfM81lhhrzSa06OWNzOEvh/RwISgfKV7Sj+GHas= Received: by 10.142.141.5 with SMTP id o5mr1935571wfd.1192536007691; Tue, 16 Oct 2007 05:00:07 -0700 (PDT) Received: by 10.143.8.5 with HTTP; Tue, 16 Oct 2007 05:00:07 -0700 (PDT) Message-ID: <36e682920710160500u260077fbm981e12538eea2827@mail.gmail.com> Date: Tue, 16 Oct 2007 08:00:07 -0400 From: "Jonah H. Harris" To: "Jessica Richard" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Kevin Grittner" , pgsql-admin@postgresql.org, "Tommy Gildseth" In-Reply-To: <561035.86032.qm@web56402.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47136772.EE98.0025.0@wicourts.gov> <561035.86032.qm@web56402.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/139 X-Sequence-Number: 27053 On 10/16/07, Jessica Richard wrote: > If the connection is from the local machine, I can find it with "ps -ef | > grep procpid", then kill it with Unix command "kill" outside Postgres... > > But I have many remote connections coming from different machines...it is > hard to kill on the OS level outside Postgres on the postgres host... > > I am looking for something to kill a Postgres user connection within > Postgres... > Some thing like, you find the user connection with select * from > pg_stat_activity...then you pick a procpid and kill right there... Write a C stored procedure that takes a pid and calls kill(2), install it on the server, and call it from SQL. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/ From rjessil@yahoo.com Tue Oct 16 12:03:34 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7107F9FB3DA for ; Tue, 16 Oct 2007 09:03:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 06903-06 for ; Tue, 16 Oct 2007 09:03:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56402.mail.re3.yahoo.com (web56402.mail.re3.yahoo.com [216.252.111.81]) by postgresql.org (Postfix) with SMTP id 78EDF9FB33A for ; Tue, 16 Oct 2007 09:03:31 -0300 (ADT) Received: (qmail 90235 invoked by uid 60001); 16 Oct 2007 12:03:30 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=dboYuIvRrHdwWcC4Rwvj3vkkFiuvxlgEVJdgHQdmR5V1WXWjW2kqKRCBGi3FAHB1XoteF0oOFVut8ncvruvUGqGJgUvQLaHeZHlfVxfw0vT6ure//jj8TfwjVJcQdvB4a7dgV3ylvu/7OpG4PJ1+5bpS1vkXzW2YBwAJb0Lvd/Q=; X-YMail-OSG: x.DS0KYVM1m.NF8Sn_jcprPfwM7cwo.p8J_lu0jFqjK9FcNTRnkJ18.CvznhAd.0K9KB4fI3DdTuHVsjnvYl8KVBtyX_7m85I8vYd_NSWq1RWbB4SttpsN6faU7Faoq1nGA0Ipptm45uuxyFTSPBjypXvQ-- Received: from [198.119.134.32] by web56402.mail.re3.yahoo.com via HTTP; Tue, 16 Oct 2007 05:03:30 PDT Date: Tue, 16 Oct 2007 05:03:30 -0700 (PDT) From: Jessica Richard Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? To: Scott Marlowe Cc: Tommy Gildseth , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-454119703-1192536210=:90208" Content-Transfer-Encoding: 8bit Message-ID: <379974.90208.qm@web56402.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/140 X-Sequence-Number: 27054 --0-454119703-1192536210=:90208 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit select pg_cancel_backend(procpid) solved half of my problem...at least it terminated the query for that user... but it is still holding a user connection in IDLE state....If I have too many of those, Postgres may run of out of user connections.... I already knew how to kill a connection if the connection is from the local host. But I have many remote connections coming from different machines... hard to kill with unix command "kill"... One time, I was testing to kill a particular connection on a testing machine, the entrie Postgres was brought down.... I need to find a safer, cleaner way to disconnect a user from Postgres when needed. Thanks a lot, Scott Marlowe wrote: On 10/15/07, Jessica Richard wrote: > Thanks a lot! > > "select pg_cancel_backend(procpid) " can end the current query for that > user, but then this connection becomes IDLE, still connected. > > Is there a command for me to totally disconnect a user by procpid? Some > times, I need to kick out a particular Postgres user completely. From jonah.harris@gmail.com Tue Oct 16 13:05:57 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5178E9FB2D7 for ; Tue, 16 Oct 2007 10:05:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 10492-04 for ; Tue, 16 Oct 2007 10:05:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.224]) by postgresql.org (Postfix) with ESMTP id 8C7B29FB358 for ; Tue, 16 Oct 2007 10:05:50 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so1157835nzf for ; Tue, 16 Oct 2007 06:05:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=rFtXfD33vYbcEUq2OZfUdEysviEhutZqWrX7Fnnh3kg=; b=cNE8aYAxdB55dfQ4W1kQfkh8OBJOEo26ehFNBQKquiAFnk0Dj8HotydZ6O08L9L3y12t1t4N+sC/ljCZKeWPmQDfXCNDe8HHLkaSO536QSgWvaylIjZvxpoVA9d42CkmMc8+LPMxk1pUzd3lQ0HLJ70Tb7JrvSPTvRcnViJBkLc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=EYDiCr0C24iC3Bw8PIPZL+WkeF4X38MnjusB1Ali2fDPAKhXZwrmnjqQgWWMFfLUKgfp2nr3hLEx6CfC0lRpjfKNEtgZAFhy1014ernFuCrgMg57PgVCVo5Emm00H9g6mnKK7bJKWx7D7n7UY2vULWmDPHsAWlDPKVOiDSlmHjo= Received: by 10.142.213.9 with SMTP id l9mr1969161wfg.1192539948821; Tue, 16 Oct 2007 06:05:48 -0700 (PDT) Received: by 10.143.8.5 with HTTP; Tue, 16 Oct 2007 06:05:48 -0700 (PDT) Message-ID: <36e682920710160605n338de99fk300cd3062bd0417e@mail.gmail.com> Date: Tue, 16 Oct 2007 09:05:48 -0400 From: "Jonah H. Harris" To: "Jessica Richard" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Scott Marlowe" , "Tommy Gildseth" , pgsql-admin@postgresql.org In-Reply-To: <379974.90208.qm@web56402.mail.re3.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <379974.90208.qm@web56402.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/143 X-Sequence-Number: 27057 On 10/16/07, Jessica Richard wrote: > select pg_cancel_backend(procpid) solved half of my problem...at least it > terminated the query for that user... but it is still holding a user > connection in IDLE state....If I have too many of those, Postgres may run of > out of user connections.... > > I already knew how to kill a connection if the connection is from the local > host. But I have many remote connections coming from different machines... > hard to kill with unix command "kill"... One time, I was testing to kill a > particular connection on a testing machine, the entrie Postgres was brought > down.... That's why I said to write a C stored procedure to do it and install it on the server. That way you could call it the same way as pg_cancel_backend. > I need to find a safer, cleaner way to disconnect a user from Postgres when > needed. At this point in time, there isn't one. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/ From moizpostgres@gmail.com Wed Oct 17 11:16:12 2007 Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E13CB9FBB9F for ; Wed, 17 Oct 2007 08:16:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 73029-10 for ; Wed, 17 Oct 2007 08:15:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.191]) by postgresql.org (Postfix) with ESMTP id 887B79FBBE8 for ; Wed, 17 Oct 2007 08:16:07 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so1507184rvb for ; Wed, 17 Oct 2007 04:16:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=FnIFjr9T7HjszYqkRoZVPWiy7nr5gBnZkETWCXqCsmo=; b=UxDG7t6+Rya4PlxZZVCKwxulwApH9WiJqrw0OjIYWK8K8JnCGUnzp5UojdCd1lbcOt5MrvRoQD9LRQ5SqQkTh4vX/QRh0Dja5f7EcMtNeDMALcvzopPou1A0IxC0jOirN78R5aROFvzmVN8eCelnSkQxJZ+EoeqPm3INg1MFz8U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=SpjaMn6HkXwaXHqZDICKomXJu+0r5BNCdrQ+XmPvkntmtqJkTh6YEpkXlNMiLQ60VX1GUSqKTlHd+xQmG8WLoFEI2XUKFwnCZ4nndEvCsmQ8duCyPGEc8yogVdPrBDbOtiG+OKjvOgslJslksku81iL4sagjKBX3UT77ELt3Exc= Received: by 10.141.107.13 with SMTP id j13mr4096139rvm.1192619765855; Wed, 17 Oct 2007 04:16:05 -0700 (PDT) Received: by 10.141.90.15 with HTTP; Wed, 17 Oct 2007 04:16:05 -0700 (PDT) Message-ID: <82e1a9bd0710170416p77d67fc2sd28aa07f531450f0@mail.gmail.com> Date: Wed, 17 Oct 2007 16:46:05 +0530 From: "Moiz Kothari" To: "Jonah H. Harris" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Jessica Richard" , "Scott Marlowe" , "Tommy Gildseth" , pgsql-admin@postgresql.org In-Reply-To: <36e682920710160605n338de99fk300cd3062bd0417e@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_25344_32202908.1192619765859" References: <379974.90208.qm@web56402.mail.re3.yahoo.com> <36e682920710160605n338de99fk300cd3062bd0417e@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/152 X-Sequence-Number: 27066 ------=_Part_25344_32202908.1192619765859 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, Please do not use kill, it sometimes resets all the connection on your db, you might want to use pkill -f to kill the connection you want to. Regards, Moiz Kothari On 10/16/07, Jonah H. Harris wrote: > > On 10/16/07, Jessica Richard wrote: > > select pg_cancel_backend(procpid) solved half of my problem...at least > it > > terminated the query for that user... but it is still holding a user > > connection in IDLE state....If I have too many of those, Postgres may > run of > > out of user connections.... > > > > I already knew how to kill a connection if the connection is from the > local > > host. But I have many remote connections coming from different > machines... > > hard to kill with unix command "kill"... One time, I was testing to > kill a > > particular connection on a testing machine, the entrie Postgres was > brought > > down.... > > That's why I said to write a C stored procedure to do it and install > it on the server. That way you could call it the same way as > pg_cancel_backend. > > > I need to find a safer, cleaner way to disconnect a user from Postgres > when > > needed. > > At this point in time, there isn't one. > > -- > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > EnterpriseDB Corporation | fax: 732.331.1301 > 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com > Edison, NJ 08837 | http://www.enterprisedb.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Hobby Site : http://dailyhealthtips.blogspot.com ------=_Part_25344_32202908.1192619765859 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi,

Please do not use kill, it sometimes resets all the connection on your db, you might want to use

pkill -f <string in ps -ef> to kill the connection you want to.

Regards,
Moiz Kothari

On 10/16/07, Jonah H. Harris <jonah.harris@gmail.com> wrote:
On 10/16/07, Jessica Richard <rjessil@yahoo.com> wrote:
> select pg_cancel_backend(procpid) solved half of my problem...at least it
> terminated the query for that user... but it is still holding a user
> connection in IDLE state....If I have too many of those, Postgres may run of
> out of user connections....
>
> I already knew how to kill a connection if the connection is from the local
> host. But I have many remote connections coming from different machines...
> hard to kill with unix command "kill"...  One time, I was testing to kill a
> particular connection on a testing machine, the entrie Postgres was brought
> down....

That's why I said to write a C stored procedure to do it and install
it on the server.  That way you could call it the same way as
pg_cancel_backend.

> I need to find a safer, cleaner way to disconnect a user from Postgres when
> needed.

At this point in time, there isn't one.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly



--
Hobby Site : http://dailyhealthtips.blogspot.com ------=_Part_25344_32202908.1192619765859-- From jonah.harris@gmail.com Wed Oct 17 12:00:48 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 17DB49FBBF5 for ; Wed, 17 Oct 2007 09:00:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 64925-06 for ; Wed, 17 Oct 2007 09:00:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.179]) by postgresql.org (Postfix) with ESMTP id 1C4189FBBF1 for ; Wed, 17 Oct 2007 09:00:37 -0300 (ADT) Received: by el-out-1112.google.com with SMTP id m34so555240ele for ; Wed, 17 Oct 2007 05:00:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=8jfakdauOM/JNqczhjnY0i8DpMN+CnNvZEL1+7aE+H8=; b=Xx3gqkzCs+rWjXH4mvqBRmZoW/VqHYNig6DK8yx4Z8sDV2X2+8+EJMlbUlMvUubRlCqEc7J5ILjmYG1EuP5McqHUIgZ5mnwEn6mnz4xBM2Vv+IstzCb9dgD47Rc7zr3XwW0SaVOVdEtzCt0Y603PdKLt72Cg9OC3TX7rM/koRhI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=EryJ7Yb1mN7sTw/dCabnoFL3xIL06RwiCl6zz42WRbhKUPMNUWnlNaQmKHe5sooVYj7bRvxAKF2x5vtNnbJoRZeTxtnLZo6JCjw3LItKVLmzFEQWmwy3WWVEo6E93RYB+SLm+WIeTuH/uc6WrFonjemj3dxZkgpd/WsQFz2FeeE= Received: by 10.142.239.11 with SMTP id m11mr2419571wfh.1192622435147; Wed, 17 Oct 2007 05:00:35 -0700 (PDT) Received: by 10.143.8.5 with HTTP; Wed, 17 Oct 2007 05:00:35 -0700 (PDT) Message-ID: <36e682920710170500m6f4f946el7358d30ce4289494@mail.gmail.com> Date: Wed, 17 Oct 2007 08:00:35 -0400 From: "Jonah H. Harris" To: "Moiz Kothari" Subject: Re: Is there a way to kill a connection from the pg_stat_activitly list? Cc: "Jessica Richard" , "Scott Marlowe" , "Tommy Gildseth" , pgsql-admin@postgresql.org In-Reply-To: <82e1a9bd0710170416p77d67fc2sd28aa07f531450f0@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <379974.90208.qm@web56402.mail.re3.yahoo.com> <36e682920710160605n338de99fk300cd3062bd0417e@mail.gmail.com> <82e1a9bd0710170416p77d67fc2sd28aa07f531450f0@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/148 X-Sequence-Number: 27062 On 10/17/07, Moiz Kothari wrote: > Please do not use kill More like, please do not take advice from people who don't know what they're talking about. > it sometimes resets all the connection on your db, > you might want to use Honestly, was this supposed to be a joke? Have you RTFM? Have you looked at the code to pkill? pkill calls kill and frankly, pkill is more dangerous if you only want to kill a single session. Grr, I'm surprised to see such an uninformed statement on this list. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/