PgBouncer "no more connections allowed (max_client_conn)"

I am using PostgreSQL with PgBouncer, and receive the following error in my application:


08P01: no more connections allowed (max_client_conn)

pgbouncer.ini has the following configuration:

max_client_conn = 100
default_pool_size = 20
pool_mode = session

postgresql.conf has:

max_connections = 100

The PostgreSQL server has 5 databases that are accessed by a single user.

My understanding is that max_connections should be equal to (default_pool_size * # databases), and that max_client_conn should be at least this much. I have also seen the comment in the pgbouncer.ini that the default_pool_size should be equal to the max_client_conn if using session pooling.

So to resolve this error I am encountering, should I simply set default_pool_size = 100? Or increase just the max_client_conn? Or something else?

The point of using PGBouncer is to let it pool connections to the PG server. If your PG max_connections = 100 and PGBouncer max_client_conn = 100 then you aren't pooling anything. You should set your PGBouncer max_client_conn to a much higher number to allow it to marshal actual PG connections to service the clients. I've seen max_client_conn set to default_pool_size # databases user count * some arbitrary number (10) to allow enough PGBouncer connections to service all the databases connected to by all the users with an arbitrary (10) connection multiplier. For example, I have a PG server with one database, max_connections = 100, a PGBouncer default_pool_size of 20 and a PGBouncer max_client_conn = 1000. That effectively allows PGBouncer to pool 20 connections to my single database while allowing my client 1000 connections to process it's data. (I'm capturing a lot of data real time from a web api).

StackExchange Database Administrators Q#216591, answer score: 16