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

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
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@n3.nabble.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&amp;node=5946254&amp;i=0&gt;

--
Sent via pgsql-general mailing list ([hidden email]
<http:///user/SendEmail.jtp?type=node&amp;node=5946254&amp;i=1&gt;)
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&amp;node=5946245&amp;code=cm9zdGFnbm9saXNhbmRyb0BnbWFpbC5jb218NTk0NjI0NXwxNjE2NDU4OTY=&gt;
.
NAML
<http://www.postgresql-archive.org/template/NamlServlet.jtp?macro=macro_viewer&amp;id=instant_html%21nabble%3Aemail.naml&amp;base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&amp;breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml&gt;

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

On 02/25/2017 07:29 AM, lisandro wrote:

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

So much for that idea.

See more comments inline below.

2017-02-25 12:17 GMT-03:00 Adrian Klaver-4 [via PostgreSQL] <[hidden
email] </user/SendEmail.jtp?type=node&node=5946255&i=0>>:

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

Just to be clear all 150 databases are on on one Postgres
server/instance, correct?

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

Well max_connections is server wide so the connection that exceeds that
could come from trying to connect any of the databases

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.

The question is are you looking at a reality that is different then the
one that triggered the FATAL message?

The message is saying at some point the connections are exceeding:

max_connections(250) - superuser_reserved_connections(3) = 247

I would believe Postgres is correct on that, so it is a matter of
finding out what is triggering the message.

Have you logged into the pgBouncer Admin to see what it reports:
http://pgbouncer.github.io/usage.html
Admin console

Are the logs below following the same event?

I ask because the timestamps differ by ~1 minute.

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
<http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245.html&gt;

Sent from the PostgreSQL - general mailing list archive at

Nabble.com.

--
Adrian Klaver
[hidden email] <http:///user/SendEmail.jtp?type=node&amp;node=5946254&amp;i=0&gt;

--
Sent via pgsql-general mailing list ([hidden email]
<http:///user/SendEmail.jtp?type=node&amp;node=5946254&amp;i=1&gt;)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<http://www.postgresql.org/mailpref/pgsql-general&gt;

------------------------------------------------------------------------
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
<http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946254.html&gt;

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.
NAML
<http://www.postgresql-archive.org/template/NamlServlet.jtp?macro=macro_viewer&amp;id=instant_html%21nabble%3Aemail.naml&amp;base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&amp;breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml&gt;

------------------------------------------------------------------------
View this message in context: Re: GMT FATAL: remaining connection slots
are reserved for non-replication superuser connections, but I'm using
pgBouncer for connection pooling
<http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946255.html&gt;
Sent from the PostgreSQL - general mailing list archive
<http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html&gt; at
Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you Adrian.

Yes, I confirm that all the databases are running in one PostgreSQL
server/instance. I'm running this version:
PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit

Let me ask: is there a way to monitor the total connections to postgresql
through time? Or should I make my own script for that? I ask because every
time the error is thrown, I check the total connections with "select
count(*) from pg_stat_activity;" but the number is always far from the
configured max_connections.

Maybe the problem is with pgBouncer, so I'll post this issue in their forum.
I'm not really a sysop, so it's hard for me to fully understand the issue.
The strange part is that the error appears in random hours, I mean, it
doesn't correspond with the hours of higher db activity. Instead, the error
appears in both scenarios: with high db activity and with very low db
activity.

For example, early this morning in postgresql log:
2017-02-28 06:26:33 GMT FATAL: remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:48 GMT FATAL: remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:50 GMT FATAL: remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:50 GMT FATAL: remaining connection slots are reserved for
non-replication superuser connections

And in pgbouncer log:
2017-02-28 06:26:39.035 4080 LOG Stats: 7 req/s, in 322589 b/s, out 2281293
b/s,query 307926 us
2017-02-28 06:27:31.510 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.511 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.511 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.514 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:39.036 4080 LOG Stats: 10 req/s, in 334173 b/s, out 2187475
b/s,query 220037 us
2017-02-28 06:28:39.036 4080 LOG Stats: 7 req/s, in 335683 b/s, out 2287722
b/s,query 370778 us
2017-02-28 06:28:46.595 4080 WARNING C-0x8d56390:
radionew/medios@127.0.0.1:40910 Pooler Error: pgbouncer cannot connect to
server
2017-02-28 06:29:39.037 4080 LOG Stats: 7 req/s, in 275963 b/s, out 1976669
b/s,query 261484 us

Anyway, I'll post a thread in the pgBouncer forum.
I'll search for some tool to monitor the total number of postgresql
connections through time.

Any comment or suggestion will be appreciated.
Thank you very much for your time!

Best regards,
Lisandro.

--
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-tp5946245p5946775.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lisandro <rostagnolisandro@gmail.com> writes:

Let me ask: is there a way to monitor the total connections to postgresql
through time? Or should I make my own script for that? I ask because every
time the error is thrown, I check the total connections with "select
count(*) from pg_stat_activity;" but the number is always far from the
configured max_connections.

You could enable log_connections and log_disconnections.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Feb 25, 2017 at 4:19 AM, lisandro <rostagnolisandro@gmail.com>
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

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

Note that pgBouncer pool size is per user/database pair. With these
settings and 150 databases I'm actually surprised that you aren't running
out of connections more often. Perhaps there are per-database settings that
haven't been shown. We are also missing info on reserve_pool_timeout,
max_db_connections, etc. which could all play a role, here.

Cheers,
Steve

On 02/28/2017 06:01 AM, Lisandro wrote:

Thank you Adrian.

Yes, I confirm that all the databases are running in one PostgreSQL
server/instance. I'm running this version:
PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit

Let me ask: is there a way to monitor the total connections to postgresql
through time? Or should I make my own script for that? I ask because every
time the error is thrown, I check the total connections with "select
count(*) from pg_stat_activity;" but the number is always far from the
configured max_connections.

I have not used any as my needs are fairly simple. All I can do is point
you at:

https://wiki.postgresql.org/wiki/Monitoring

Hopefully someone with more experience with this can help you out.

A question I forgot to ask previously:

Is there any app/client that uses the server that is not going through
pgBouncer?

Maybe the problem is with pgBouncer, so I'll post this issue in their forum.
I'm not really a sysop, so it's hard for me to fully understand the issue.
The strange part is that the error appears in random hours, I mean, it
doesn't correspond with the hours of higher db activity. Instead, the error
appears in both scenarios: with high db activity and with very low db
activity.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Steve, thanks for your help.
Your comment made me realise that maybe the problem is my pgBouncer
configuration, specifically default_pool_size. It took me a while to
understand pgbouncer, and I still had some doubts when I configured it. Now
I undesrtand better.

I connect to all databases with the same user. However, I had set
default_pool_size=10. So with more than 150 databases, it was very probable
that postgresql reach max_connections=250 limit.

I didn't have set reserve_pool_timeout or max_db_connections, but docs say
their default values are reserve_pool_timeout=5 seconds,
max_db_connections=unlimited.

I've reviewed pgBouncer configuration and did some search. I've found this
thread where the first person that responds gives a great explanation abount
how pgbouncer do its maths:
https://github.com/pgbouncer/pgbouncer/issues/174

So, what I did for now was to set this in pgbouncer configuration:
default_pool_size=1
min_pool_size = 0
server_idle_timeout = 30
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 5

I'll keep monitoring with this setup, but I can already tell you that the
total number of connections in postgres has significantly reduced (from ~100
to ~60). So I guess the problem was a bad setup of pgbouncer.

Thank you all for your help!

--
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-tp5946245p5946827.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 28, 2017 at 10:00 AM, Lisandro <rostagnolisandro@gmail.com> wrote:

Hi Steve, thanks for your help.
Your comment made me realise that maybe the problem is my pgBouncer
configuration, specifically default_pool_size. It took me a while to
understand pgbouncer, and I still had some doubts when I configured it. Now
I undesrtand better.

I connect to all databases with the same user. However, I had set
default_pool_size=10. So with more than 150 databases, it was very probable
that postgresql reach max_connections=250 limit.

I didn't have set reserve_pool_timeout or max_db_connections, but docs say
their default values are reserve_pool_timeout=5 seconds,
max_db_connections=unlimited.

I've reviewed pgBouncer configuration and did some search. I've found this
thread where the first person that responds gives a great explanation abount
how pgbouncer do its maths:
https://github.com/pgbouncer/pgbouncer/issues/174

So, what I did for now was to set this in pgbouncer configuration:
default_pool_size=1
min_pool_size = 0
server_idle_timeout = 30
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 5

I'll keep monitoring with this setup, but I can already tell you that the
total number of connections in postgres has significantly reduced (from ~100
to ~60). So I guess the problem was a bad setup of pgbouncer.

Those seem pretty reasonable. Note that if you need to you CAN set
default pool size and other settings per database etc. So if you have
a more active db that needs more connections etc you can adjust these
as needed per db and it will override the default overall settings.

As for monitoring I'd suggest setting up Nagios or Zabbix. They both
can give you some nice pretty graphs of what your system looks like
over time so you can do simple trend analysis and alerting to avoid
these problems in the future.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general