Hello.
We discovered some time ago that pgbouncer is NOT a balancer, because it cannot spread connections/queries to the same database to multiple servers. It's unbeliveable, but it's a fact! So, database name in the config MUST be unique.
E.g. if we write
bardb = host=
192.168.0.1 dbname=bardb
bardb = host=
192.168.0.2 dbname=bardb
bardb = host=
192.168.0.3 dbname=bardb
in the config, pgbouncer always uses the first connection, and others are ignored. Here is the part of the source code:
== loader.c:
void parse_database(char *name, char *connstr) {
...
db = add_database(name);
...
}
== objects.c:
PgDatabase *add_database(const char *name)
{
PgDatabase *db = find_database(name);
/* create new object if needed */
if (db == NULL) {
db = zmalloc(sizeof(*db));
...
}
return db;
}
In these functions "name" is a key from the config ("bardb" in our example). We see that it's useless to create duplicate keys in config elements in [databases] sections, because only the first one is accepted.
So, it's completely magical for me why "Session pooling", "Transaction pooling" and "Statement pooling" options are exist (see
https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer). If pgbouncer is not a balancer, what purpose is to use "Statement pooling" - if we sent 100 queries (e.g.) in the same connection, they will always be directed to the SAME MACHINE in its different connections, no balancing optimization at all.