PgBouncer acts as Postgres server, so simply point your client to PgBouncer port.
PgBouncer does not have internal multi-host configuration. It is possible via some external tools:
DNS round-robin. Use several IPs behind one DNS name. PgBouncer does not look up DNS each time new connection is launched. Instead it caches all IPs and does round-robin internally. Note: if there is more than 8 IPs behind one name, the DNS backend must support EDNS0 protocol. See README for details.
Use a TCP connection load-balancer. Either
LVS or
HAProxy seem to be good choices. On
PgBouncer side it may be good idea to make server_lifetime smaller
and also turn server_round_robin on - by default idle connections
are reused by 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 via some external tools:
DNS reconfiguration - when ip behind DNS name is reconfigured, pgbouncer will reconnect to new server. This behaviour can be tuned via 2 config parameters - dns_max_ttl tunes lifetime for one hostname, and dns_zone_check_period tunes how often zone SOA will be queried for changes. If zone SOA record has changed, pgbouncer will re-query all hostnames under that zone.
Write new host to config and let PgBouncer reload it - send SIGHUP or use RELOAD; command on console. PgBouncer will detect changed host config and reconnect to new server.
Since version 1.7, PgBouncer has built-in support for TLS. Just configure it.
[ Old answer for older PgBouncer versions. ]
Use Stunnel. Since version 4.27 it supports
PostgreSQL protocol for both client and server side. It is activated by
setting protocol=pgsql.
Alternative is to use Stunnel on both sides of connection, then the protocol support is not needed.
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;
To make prepared statements work in this mode would need PgBouncer to keep track of them internally, which it does not do. So only way to keep using PgBouncer in this mode is to disable prepared statements in the client.
The proper way to do it for JDBC is adding prepareThreshold=0
parameter to connect 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);
[ This cannot be done with TLS connections. ]
This is as easy as launching new PgBouncer process with -R switch and
same config:
$ pgbouncer -R -d config.ini
The -R (reboot) switch makes new process connect to console of the old
process (dbname=pgbouncer) via unix socket and issue following commands:
SUSPEND;
SHOW FDS;
SHUTDOWN;
After that if new one notices old one gone it resumes work with old
connections. The magic happens during SHOW FDS command which
transports actual file descriptors to new process.
If the takeover does not work for whatever reason, the new process can be simply killed, old one notices this and resumes work.
Use SHOW CLIENTS and SHOW SERVERS views on 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 on webserver is good when short-connections are used, then the connection setup latency is minimised - TCP requires couple of packet roundtrips before connection is usable. Installing on database server is good when there are many different hosts (eg. webservers) connecting to it, then their connections can be optimised together.
It is also possible to install PgBouncer on both webserver and database servers. Only negative aspect of that is that each PgBouncer hop adds small amount of latency to each query. So it’s probably best to simply test whether the payoff is worth the cost.