PgBouncer acts as a Postgres server, so simply point your client to the PgBouncer port.
PgBouncer does not have an internal multi-host configuration. It is possible via external tools:
DNS round-robin. Use several IPs behind one DNS name. PgBouncer does not look up DNS each time a new connection is launched. Instead, it caches all IPs and does round-robin internally. Note: if there are more than 8 IPs behind one name, the DNS backend must support the EDNS0 protocol. See README for details.
Use a TCP connection load-balancer. Either
LVS or
HAProxy seem to be good choices. On the
PgBouncer side it may be a good idea to make server_lifetime smaller
and also turn server_round_robin on: by default, idle connections
are reused by a LIFO algorithm, which may work not so well when
load-balancing is needed.
PgBouncer does not have internal failover-host configuration nor detection. It is possible with external tools:
DNS reconfiguration: When the IP address behind a DNS name is
reconfigured, PgBouncer will reconnect to the new server. This
behaviour can be tuned by two configuration parameters:
dns_max_ttl tunes the lifetime for one host name, and
dns_zone_check_period tunes how often a zone SOA will be queried
for changes. If a zone SOA record has changed, PgBouncer will
re-query all host names under that zone.
Write a new host to the configuration and let PgBouncer reload it:
send SIGHUP or use the RELOAD command on the console. PgBouncer
will detect a changed host configuration and reconnect to the new
server.
Use the RECONNECT command. This is meant for situations where
neither of the two options above are applicable, for example when
you use the aforementioned HAProxy to route connections downstream
from PgBouncer. RECONNECT simply causes all server connections
to be reopened. So run that after that other component has changed
its connection routing information.
In session pooling mode, the reset query must clean old prepared
statements. This can be achieved by server_reset_query = DISCARD ALL;
or at least to DEALLOCATE ALL;
Since version 1.21.0 PgBouncer can track prepared statements in transaction
pooling mode and make sure they get prepared on-the-fly on the linked server
connection. To enable this feature, max_prepared_statements needs to be
set to a non-zero value. See the docs for
max_prepared_statements
for more details.
Due to the way PHP/PDO uses prepared statements (#991) the prepared statement support in PgBouncer 1.21.0 does not work for PHP/PDO. So for PHP/PDO and PgBouncer versions before 1.21.0 the only work-around is to disable prepared statements in the client side.
The proper way to do it for JDBC is adding the prepareThreshold=0
parameter to the connection string.
To disable use of server-side prepared statements, the PDO attribute
PDO::ATTR_EMULATE_PREPARES must be set to true. Either at
connect-time:
$db = new PDO("dsn", "user", "pass", array(PDO::ATTR_EMULATE_PREPARES => true));
or later:
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
DEPRECATED: Instead of this option use a rolling restart with multiple pgbouncer processes listening on the same port using so_reuseport instead
This is as easy as launching a new PgBouncer process with the -R
switch and the same configuration:
$ pgbouncer -R -d config.ini
The -R (reboot) switch makes the new process connect to the console
of the old process (dbname=pgbouncer) via the Unix socket and issue
the following commands:
SUSPEND;
SHOW FDS;
SHUTDOWN;
After that, if the new one notices that the old one is gone, it
resumes work with the old connections. The magic happens during the
SHOW FDS command which transports the actual file descriptors to new
process.
If the takeover does not work for whatever reason, the new process can be simply killed. The old one notices this and resumes work.
Use the SHOW CLIENTS and SHOW SERVERS commands on the console.
Use ptr and link to map local client connection to server
connection.
Use addr and port of client connection to identify TCP
connection from client.
Use local_addr and local_port to identify TCP connection to
server.
It depends.
Installing PgBouncer on the web server is good when short-lived connections are used. Then the connection setup latency is minimised. (TCP requires a couple of packet roundtrips before a connection is usable.) Installing PgBouncer on the database server is good when there are many different hosts (e.g., web servers) connecting to it. Then their connections can be optimised together.
It is also possible to install PgBouncer on both web server and database server. One negative aspect of that is that each PgBouncer hop adds a small amount of latency to each query.
In the end, you will need to test which model works best for your performance needs. You should also consider how installing PgBouncer will affect the failover of your applications in the event of a web server vs. database server going away.