Multiple hosts for the same DB

[ltagliamonte-dd]

Dear community, we would like to implement a smart discovery mechanism for our pgbouncer installation. We would like the community input on what is the best way to use N endpoints for the same database.

From the documentation we understand that for the host config:

If DNS returns several results, they are used in round-robin manner.

We currently have 2 ideas:

• implement a custom DNS daemon that for the replicas myReplicasDBEndpoints will reply with N A records, something like:

[databases]
mydatabase = host=myWriterDBEndpoint port=15432 dbname=mydatabase
mydatabase_replica = host=myReplicasDBEndpoints port=25432 dbname=mydatabase

• generate a config file with N entries for the same DB and reload pgbouncer with HUP, something like:

[databases]
mydatabase = host=10.4.0.1 port=15432 dbname=mydatabase
mydatabase_replica = host=10.4.0.2 port=25432 dbname=mydatabase
mydatabase_replica = host=10.4.0.3 port=25432 dbname=mydatabase
mydatabase_replica = host=10.4.0.4 port=25432 dbname=mydatabase

I didn't try this last config setup yet, I'm just wondering if something like this could work in some way.

The generation of the config file looks way easier that play with DNS, but we are also wondering how pgbouncer will gracefully react to each solutions.

Looking forward to your feedbacks. Regards L.