I have an Azure postgresql flexible server running a General Purpose, D2s_v3, 2 vCores, 8 GiB RAM, 32 GiB storage instance and using pg_bouncer for connection pooling.
At all time there are 100 active connections and when I try to connection (not using the pgbouncer) I get the error Remaining connection slots are reserved. I can also see that there are sporadic errors on connecting that looks to be from pgbouncer as there are not failed connections on the postgresql server.
The server is configured with:
max_connections = 100
pgbouncer.default_pool_size = 50
pgbouncer.max_client_conn = 5000
pgbouncer.min_pool_size = 0
pgbouncer.pool_mode = TRANSACTION
Should the max connections be increased or is there some other configuration that should be adjusted such that pgbouncer don't allocate all connections?
So in general the only solution was to limit the pgbouncer.default_pool_size to a number that was low enough to not take up all connections. For example if:
max_connections = 400
default_pool_size = 50
With a total of 7 databases and one user connecting to them the max number of connections created by pgbouncer would be 7 * 1 * 50 = 350 which is less than the max_connections.
Unfortunately the few pgbouncer parameters exposed in Azure don't allow for a better configuration other than setting the same configuration for all databases on the server.