Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 502272E0376 for ; Fri, 30 Nov 2007 17:24:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 81434-02 for ; Fri, 30 Nov 2007 17:24:44 -0400 (AST) Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id A77992E035B for ; Fri, 30 Nov 2007 17:24:43 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id h2so1352682ugf for ; Fri, 30 Nov 2007 13:24:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=oaIszKxE48qCL4sSWxgby5xdgEdHjBsXJVn0LNVOEjU=; b=JH5Ir/ZU0bekGD1Sqkg4Daqf7C5wwbVioRKuvffRrGlnW6IWnSnvfVjnMbF1crfi/MRTXe8gk/1Ru1xYFHey062boUfDDv4h1tCwq66lTaoOr3ag9ASw7w1Yk3ijsE41hJNcXZ+YtrXAMht6wnNWlXLtyyKDDIE3CHJ1d76WZ0g= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=q4sQWoNmD1jAsJRCVYSXFauDDuk33tPQe+Ke7jPNJv00P70DuabRhWSO7zt01fPFe9smUWsChf1J1VFd5SmWAPLTKKI8pjtq3r1pGHCqXIjxbiqehBX/PiRuLwwqKRnXEUVHrzQ9nJCaCERccUbdtEw0DXN23e+ZcHZjCGE/wdE= Received: by 10.78.122.16 with SMTP id u16mr9481832huc.1196457881782; Fri, 30 Nov 2007 13:24:41 -0800 (PST) Received: by 10.78.151.9 with HTTP; Fri, 30 Nov 2007 13:24:41 -0800 (PST) Message-ID: Date: Sat, 1 Dec 2007 02:24:41 +0500 From: "Usama Dar" To: "Tena Sakai" Subject: Re: connection limit exceeded Cc: "Jonah H. Harris" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_45_17730221.1196457881768" References: <36e682920711301203g7fdb1b81vc7d2bbb2e4018827@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/260 X-Sequence-Number: 27503 ------=_Part_45_17730221.1196457881768 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Dec 1, 2007 1:34 AM, Tena Sakai wrote: > Hi Jonah, > > Many thanks! > > >> 2) how would I look at the "open/used" connections? > > select * from pg_stat_activity; > > This is a wonderful view! How would I properly terminate > these (of course there are 99 of them!) these query process? > Should I do this from unix or is there any postgres tool I > can use? > try pg_cancel_backend(pid) > > > Thank you. > > Regards, > > Tena Sakai > tsakai@gallo.ucsf.edu > > > > -----Original Message----- > From: Jonah H. Harris [mailto:jonah.harris@gmail.com > ] > Sent: Fri 11/30/2007 12:03 PM > To: Tena Sakai > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] connection limit exceeded > > On Nov 30, 2007 2:55 PM, Tena Sakai wrote: > > On my postgres 8.1.1, I get a response > > psql: FATAL: connection limit exceeded for non-superusers > > > 1) Is this to do with max_connections setting in the postgres > > configuration file? (my setting is 100 (I think this is the > > default).) > > Yep. > > > 2) how would I look at the "open/used" connections? > > select * from pg_stat_activity; > > > 3) should I try restarting postgres without changing the setting? > > Has to be restarted for change to take effect. > > > 4) what repercussion would there be if I increase this number to, > > say, 200? (and would that be a good idea?) > > A little overhead, nothing bad. > > > 5) finally, what's the best fix? (Do I need to go to pgpool?) > > see pgbouncer > > -- > 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/ > > -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar ------=_Part_45_17730221.1196457881768 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On Dec 1, 2007 1:34 AM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:

Hi Jonah,

Many thanks!



>>  2) how would I look at the "open/used" connections?
> select * from pg_stat_activity;

This is a wonderful view!  How would I properly terminate
these (of course there are 99 of them!) these query process?
Should I do this from unix or is there any postgres tool I
can use?


try pg_cancel_backend(pid)



Thank you.

Regards,



Tena Sakai
tsakai@gallo.ucsf.edu



-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Fri 11/30/2007 12:03 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] connection limit exceeded

On Nov 30, 2007 2:55 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>  On my postgres 8.1.1, I get a response
>    psql: FATAL:  connection limit exceeded for non-superusers

>  1) Is this to do with max_connections setting in the postgres
>     configuration file?  (my setting is 100 (I think this is the
>     default).)

Yep.

>  2) how would I look at the "open/used" connections?

select * from pg_stat_activity;

>  3) should I try restarting postgres without changing the setting?

Has to be restarted for change to take effect.

>  4) what repercussion would there be if I increase this number to,
>     say, 200? (and would that be a good idea?)

A little overhead, nothing bad.

>  5) finally, what's the best fix?  (Do I need to go to pgpool?)

see pgbouncer

--
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/




--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
------=_Part_45_17730221.1196457881768--