While pgpool works well as an abstraction layer above PostgreSQL, its handling of excess client connection attempts is less than ideal. If the maximum number of clients per pool was twenty, for instance, any connections over twenty with the same login credentials and target database will simply wait indefinitely. Further, there is no concept of transaction-level connection reuse.
PgBouncer can allow prospective client connections to number in the thousands and still maintain high throughput. We can also tell it to reuse connections after any client completes a transaction so that clients do not have to disconnect between operations. Yet, it cannot balance connections across multiple PostgreSQL servers, and it certainly has no concept of primary server or replica. In this respect, it really is a bouncer, holding users at the door with minimal knowledge of what's inside the building.
Until there's a product that combines the best elements of these two services, we can do so manually. This way, we get the best of both utilities, while still maintaining high availability and isolation of the PostgreSQL cluster from the outside world.
Install pgpool according to the instructions in the Installing pgpool recipe. Then, install pgbouncer according to the instructions in the Installing PgBouncer recipe. Then, configure both as described in the Configuring pgpool for master/slave mode and Configuring PgBouncer safely recipes.
With that done, we simply need to change a few configuration settings to gain full integration.
Assuming PgBouncer and pgpool are installed on the same node as the primary server at 192.168.56.10, we can combine PgBouncer and pgpool with one change. Follow these steps:
- Open the
/etc/pgbouncer/pgbouncer.iniconfiguration file, and add the following line under the[databases]section:* = host=192.168.56.10 port=9999
- Then, reload PgBouncer with the following command:
sudo service pgbouncer reload
We did much of the really hard work in all the previous installation and configuration instructions. By adding a single line in the pgbouncer.ini configuration file and reloading Pgbouncer, every connection to PgBouncer will automatically pass through pgpool as well.
We now have automatic server load balancing and robust connection pooling.
When adding final touches to the configuration files, pay close attention to default_pool_size in pgbouncer.ini and num_init_children in pgpool.conf. Since pgpool doesn't like having more connections than num_init_children, no PgBouncer pool should exceed this number of connections. Thus, the value of default_pool_size added to reserve_pool_size should always be equal to or less than num_init_children in PgBouncer.