Обсуждение: Connection limit exceeded for non-superusers when there are plenty of available slots

We have a number of applications using a production database under a super
user account. I've created a new (non-super) user account and if I try to
login using this account I get the
"Non-superuser connection limit exceeded" error. I'm aware of the
postgresql.conf settings for max user connections etc. Here are the queries
I ran (under super user) when the login exception occurs.


 SELECT usesuper, count(*) FROM pg_stat_activity JOIN pg_user USING
 (usesysid) GROUP BY usesuper;

62

  SHOW superuser_reserved_connections;

480

  SHOW max_connections;
 500

Is there something I'm missing here?   Thanks for your help.

Here's my  OS + Postgres

Ubuntu x86_64 , Postgres 8.4.8

On Fri, Mar 1, 2013 at 7:38 AM, G B <g.b.coder@gmail.com> wrote:
>   SHOW superuser_reserved_connections;
>
> 480
>
>   SHOW max_connections;
>  500
>
> Is there something I'm missing here?   Thanks for your help.

This leaves just 20 connections for non-root users. Did you intend to
set superuser_reserved_connections to 20, thus leaving 480 for normal
use?

ChrisA

My intention was to have 480 max connections for super user and 20
connections for non super user. (I created the non super user account for
 an external user to log into PgAdmin and look at the schema) Is there a
problem with 20 connections in such a scenario?

 When I select from pg_stat_activity, I only see 62 connections from the
super user (postgres) and that  number is less than 480.
I don't see any connections from non super user. Yet , when I try to login
as non super user, it claims it ran out of non super user reserved
connections.









On Thu, Feb 28, 2013 at 3:49 PM, Chris Angelico <rosuav@gmail.com> wrote:

> On Fri, Mar 1, 2013 at 7:38 AM, G B <g.b.coder@gmail.com> wrote:
> >   SHOW superuser_reserved_connections;
> >
> > 480
> >
> >   SHOW max_connections;
> >  500
> >
> > Is there something I'm missing here?   Thanks for your help.
>
> This leaves just 20 connections for non-root users. Did you intend to
> set superuser_reserved_connections to 20, thus leaving 480 for normal
> use?
>
> ChrisA
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

From the docs:

"superuser_reserved_connections: Whenever the number of active concurrent
connections is at least max_connections minus
superuser_reserved_connections, new connections will be accepted only for
superusers, and no new replication connections will be accepted." (
http://www.postgresql.org/docs/9.0/static/runtime-config-connection.html)

So in your case, you have 480 connections for super users out of 50 total.
Its not that 20 of them are reserved for non-superusers.... so since you
have less then 480 connections available, only super users can use them.  I
don't believe postgresql cares who is currently using a connection...
rather if the number of available connections is less then what is set in
'superuser_reserved_connections', only superusers can use it. (Someone can
correct me if I'm wrong here)



On Thu, Feb 28, 2013 at 2:17 PM, G B <g.b.coder@gmail.com> wrote:

> My intention was to have 480 max connections for super user and 20
> connections for non super user. (I created the non super user account for
>  an external user to log into PgAdmin and look at the schema) Is there a
> problem with 20 connections in such a scenario?
>
>  When I select from pg_stat_activity, I only see 62 connections from the
> super user (postgres) and that  number is less than 480.
> I don't see any connections from non super user. Yet , when I try to login
> as non super user, it claims it ran out of non super user reserved
> connections.
>
>
>
>
>
>
>
>
>
> On Thu, Feb 28, 2013 at 3:49 PM, Chris Angelico <rosuav@gmail.com> wrote:
>
>> On Fri, Mar 1, 2013 at 7:38 AM, G B <g.b.coder@gmail.com> wrote:
>> >   SHOW superuser_reserved_connections;
>> >
>> > 480
>> >
>> >   SHOW max_connections;
>> >  500
>> >
>> > Is there something I'm missing here?   Thanks for your help.
>>
>> This leaves just 20 connections for non-root users. Did you intend to
>> set superuser_reserved_connections to 20, thus leaving 480 for normal
>> use?
>>
>> ChrisA
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>


--
Virtually, Ned Wolpert

"Settle thy studies, Faustus, and begin..."   --Marlowe