pgBouncer says nodb/ nouser. no such database exist despite there is one active and running

[Rammurthy5]

Hello, here is my pgbouncer.ini config.
pgBouncer version = v1.16.1

[databases]
db1 = dbname=demo host=10.1.5.10 port=5432 dbname=demo user=admin password=abc
db2 = dbname=demo host=10.1.5.11 port=5432 dbname=demo user=admin password=abc

[users]

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = any

when i try to connect to either of the DB1, DB2 (psql -h 10.1.5.10 -p 5432 -d demo -U admin) mentioned above from pgBouncer instance (10.1.5.14), i am able to connect and execute the queries i want to.

But when i use pgBouncer IP and port to connect to the DB psql -h 10.1.5.14 -p 6432 -d demo -U admin
it throws error psql: error: connection to server at "10.1.5.14", port 6432 failed: FATAL: no such database: demo

pgbouncer.log

OG C-0x55c722257510: (nodb)/(nouser)@10.1.5.14:54562 closing because: no such database: demo (age=0s)
2023-08-08 09:51:10.506 UTC [28793] WARNING C-0x55c722257510: (nodb)/(nouser)@10.1.5.14:54562 pooler error: no such database: demo
2023-08-08 09:51:10.506 UTC [28793] LOG C-0x55c722257510: (nodb)/(nouser)@10.1.5.14:54562 login failed: db=demo user=admin

can you please help me understand what am i missing here, please?

[JelteF]

You'd need to use:

psql -h 10.1.5.14 -p 5432 -d db1 -U admin

or

psql -h 10.1.5.14 -p 5432 -d db2 -U admin

You're mapping the db1/db2 names to a host + database combination.

[Rammurthy5]

It works. thanks. But i dont get this why?! if i use db1 or db2 explicitly, does not it mean i always want to talk to that particular DB host?! I thought PgBouncer will decide that for me since i have 2 instances.

[JelteF]

I'm not sure what you want it to do. Do you want it to load balance across both hosts? Then you'll need to use:

demo = dbname=demo host=10.1.5.10,10.1.5.11 port=5432 dbname=demo user=admin password=abc

With your original settings pgbouncer would translate db1 to connecting to the demo database on 10.1.5.10 and translate db2 to connecting to the demo database on 10.1.5.11.

[Rammurthy5]

yes, thats what i want to do. I want to hit pgBouncer IP and port but let the pgBouncer choose which one it wants to hit based on load_balancing algorithm i set - i have round-robin.

Reg your suggestion to use both host IPs host=10.1.5.10,10.1.5.11 in the same line --- Sorry, this throws DNS lookup error as it considers both IPs as one string. is there a better way to do this, please?

[JelteF]

That sounds like you're using an old PgBouncer version then. Can you try with the latest? Load balancing of host names was introduced in 1.17.0

[Rammurthy5]

v1.16.1 is the newest version available in ubuntu apt package manager, unfortunately. i did try to upgrade pgbouncer after apt update, apt upgrade.

[JelteF]

If you add the Postgres Ubuntu repo you should be able to get the newest version: https://www.postgresql.org/download/linux/ubuntu/

[Rammurthy5]

Fantastic. It works now

[Rammurthy5]

@JelteF , A suggestion / question -- pgBouncer to get support sticky_sessions. I understand there is no support until the new release (v1.20), will there be ever in the future?