Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

Thanks for the quick answer.

superuser_reserved_connections is set to 3

Actually, it's not set (the line is commented) but the default
for superuser_reserved_connections is 3:
https://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-SUPERUSER-RESERVED-CONNECTIONS

2017-02-25 12:17 GMT-03:00 Adrian Klaver-4 [via PostgreSQL] <
ml-node+s1045698n5946254h40(at)n3(dot)nabble(dot)com>:

> On 02/25/2017 04:19 AM, lisandro wrote:
>
> > Hi there! Please tell me if this isn't the place to post my question,
> I'm new
> > in the list.
> >
> > I'm using PostgreSQL 9.3, I have around 150 databases, and I use
> pgBouncer
> > for connection pooling.
> > My server is a VPS with 8cpus and 24gb of RAM.
> >
> > My current postgreSQL configuration (resumed) is this:
> >
> > listen_addresses = '*'
> > port = 6543
> > max_connections = 250
> > shared_buffers = 2GB
> > effective_cache_size = 6GB
> > work_mem = 10485kB
> > maintenance_work_mem = 512MB
> > checkpoint_segments = 32
> > checkpoint_completion_target = 0.7
> > wal_buffers = 16MB
> > default_statistics_target = 100
>
> What is superuser_reserved_connections set to?
>
> >
> >
> > In the other hand, my pgBouncer configuration (resumed) is this:
> >
> > listen_addr = localhost
> > listen_port = 5432
> > pool_mode = transaction
> > server_reset_query = DISCARD ALL
> > max_client_conn = 10000
> > default_pool_size = 10
> > min_pool_size = 2
> > server_idle_timeout = 30
> >
> >
> > However, for the last couple of months (total db number has been
> increasing)
> > I have these sporadic errors where pgbouncer can't connect to
> postgresql.
> > They occurr every day with variable frequency. Every time the error
> appears,
> > it does in a different database. Even in those where the activity is
> almost
> > none.
> >
> > Every time the error is triggered, I check the total connections number
> and
> > it never goes beyond ~130.
> > This is how I check, from psql:
> > select count(*) from pg_stat_activity;
> >
> > Also I check for inactive connections with this:
> > select count(*) from pg_stat_activity where (state = 'idle in
> transaction')
> > and xact_start is not null;
> > ... but this number is always low, ~8 idle connections.
> >
> >
> >
> > When the error triggers, I check the postgresql log and I see this:
> >
> > 2017-02-25 09:13:31 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:31 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:31 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:46 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:46 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:46 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:47 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:48 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> > 2017-02-25 09:13:49 GMT FATAL: remaining connection slots are reserved
> for
> > non-replication superuser connections
> >
> >
> >
> > And if I check the pgbouncer log I see this:
> >
> > 2017-02-25 09:12:37.354 4080 LOG Stats: 24 req/s, in 387979 b/s, out
> 2657772
> > b/s,query 146363 us
> > 2017-02-25 09:13:37.355 4080 LOG Stats: 23 req/s, in 382191 b/s, out
> 2594329
> > b/s,query 144827 us
> > 2017-02-25 09:14:29.687 4080 ERROR S: login failed: FATAL: remaining
> > connection slots are reserved for non-replication superuser connections
> > 2017-02-25 09:14:37.355 4080 LOG Stats: 28 req/s, in 383614 b/s, out
> 2596947
> > b/s,query 124098 us
> > 2017-02-25 09:14:44.985 4080 ERROR S: login failed: FATAL: remaining
> > connection slots are reserved for non-replication superuser connections
> > 2017-02-25 09:14:46.290 4080 ERROR S: login failed: FATAL: remaining
> > connection slots are reserved for non-replication superuser connections
> > 2017-02-25 09:15:37.355 4080 LOG Stats: 26 req/s, in 378113 b/s, out
> 2717657
> > b/s,query 164167 us
> >
> >
> > What am I missing? I will appreciate any tip or suggestion.
> > Thanks in advance!
> >
> >
> >
> > --
> > View this message in context: http://www.postgresql-archive.
> org/GMT-FATAL-remaining-connection-slots-are-reserved-
> for-non-replication-superuser-connections-but-I-m-g-tp5946245.html
> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >
> >
>
>
> --
> Adrian Klaver
> [hidden email] <http:///user/SendEmail.jtp?type=node&node=5946254&i=0>
>
>
> --
> Sent via pgsql-general mailing list ([hidden email]
> <http:///user/SendEmail.jtp?type=node&node=5946254&i=1>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
> http://www.postgresql-archive.org/GMT-FATAL-remaining-
> connection-slots-are-reserved-for-non-replication-superuser-
> connections-but-I-m-g-tp5946245p5946254.html
> To unsubscribe from GMT FATAL: remaining connection slots are reserved for
> non-replication superuser connections, but I'm using pgBouncer for
> connection pooling, click here
> <http://www.postgresql-archive.org/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5946245&code=cm9zdGFnbm9saXNhbmRyb0BnbWFpbC5jb218NTk0NjI0NXwxNjE2NDU4OTY=>
> .
> NAML
> <http://www.postgresql-archive.org/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.