Re: pgbouncer

On Thu, Jan 17, 2019 at 8:18 AM Nicola Contu <nicola(dot)contu(at)gmail(dot)com> wrote:
>
> Hello,
> I am a bit confused about the settings in pgbouncer
>
> What's exactly the pool_size?

Roughly, the number of open connections pgbouncer will keep to PostgreSQL.

> If I set 3, and I tried to connect from 4 shells, I am still able to connect.

That would be the point - pgbouncer is sharing the 3 connections it
keeps with PostgreSQL between the 4 client connections made to it.

> Same thing for max_db_connections. I set this to 1 and I am able to connect from 2 shells.

Same as pool_size but basically a fail-safe since pools are
per-user/per-database while this limit is per-database only.

> This is kind of confusing and I'm not really cleared reading the documentation.

For each setting you need to understand whether it limits
pgbouncer->PostgreSQL or client->pgbouncer

Configurations in [databases] limit the former; [pgbouncer] options
either provide defaults for the [databases] or limit clients.

> [databases]
> cmdwela = host=127.0.0.1 port=5432 dbname=cmdwela auth_user=pgbouncer pool_size=120
> cicciopasticcio = host=127.0.0.1 port=5432 dbname=cicciopasticcio auth_user=pgbouncer pool_size=2
>
> [pgbouncer]
> listen_port = 6543
> listen_addr = *
> auth_type = md5
> auth_file = /etc/pgbouncer/users.txt
> auth_query = select uname,phash from user_lookup($1)
> logfile = /var/log/pgbouncer.log
> pidfile = /home/postgres/pgbouncer.pid
> admin_users = admin
> user=postgres

> max_db_connections=1
So one open connection max per database/user pool but it is shared -
i.e., actively executing queries running in parallel are limited to
this number.

> max_client_conn=5
> I just want to limit connections from the app etc.

That limit is 5

David J.