The next pooling resource we will explore is named pgpool-II, but we'll refer to it simply as pgpool. This is another popular connection proxy, but it predates PgBouncer by almost a year, having been available since late 2006. The scope of pgpool is also much larger, providing functionality such as query-based replication, connection pooling, load balancing, parallel-query, and more.
Perhaps surprisingly, we won't discuss most of these features in this book. Interesting as they may be, these advanced features don't directly apply to building a highly available PostgreSQL cluster. Of course, we always encourage experimentation.
One feature pgpool exposes, which is directly relevant to this book, is server pooling. What does this mean? If we have two PostgreSQL servers, we can make use of a virtual IP address so that clients need not modify configuration files when we switch the primary database server. However, in order to move the IP address between servers, it must first be removed from one server and recreated on the other. This disconnects all active clients and causes a small disruption in availability.
However, pgpool can pool servers so that the active primary server is hidden from database clients. We can promote the secondary within pgpool, and it will handle failover internally. From the application or client's perspective, the database was never offline.
The first step to gain this ability is installation. The pgpool proxy is so popular that many Linux systems package it along with other PostgreSQL tools, so we will cover some of the more popular distributions. For completeness, we also intend to cover pure source installs since that means we can utilize the latest release, regardless of distribution.
For the sake of completeness, obtain a copy of the latest pgpool source code. At the time of writing this book, the latest version is 3.3.2, released on November 29, 2013.
In order to properly compile the source code, we need PostgreSQL development libraries in addition to the normally installed system binaries. For example, to build properly on a Debian- or Ubuntu-based system, we need to install libraries by executing this at the command line:
sudo apt-get install postgresql-server-dev-9.3
Later, we simply need a root-capable user to install PgBouncer as a system-wide service.
To install in a Debian- or Ubuntu-based system, execute this command:
sudo apt-get install pgpool2
To install in a CentOS, Fedora, or other RHEL-based systems, execute this command:
sudo yum install pgpool-II-93
Otherwise, follow these steps to complete a full source-based installation:
- Use these commands to extract the pgpool source and enter the source directory:
tar -xzf pgpool-II-3.3.2.tar.gz cd pgpool-II-3.3.2
- Next, build and install the actual software with these commands:
./configure --prefix=/usr --sysconfdir=/etc/pgpool/ make sudo make install
- Create a location where pgpool can maintain activity logs with these commands:
sudo mkdir /var/log/pgpool sudo chown postgres /var/log/pgpool
- Create a directory where pgpool can keep its service lock file with these commands:
sudo mkdir /var/run/pgpool sudo chown postgres /var/run/pgpool
- Copy the
init/pgpoolinitialization script from this chapter's provided source code into the/etc/init.ddirectory on the server. - Change the copied initialization script to make it executable with this command:
sudo chmod a+x /etc/init.d/pgpool - Finally, add the service to system startup and shutdown:
- For Debian or Ubuntu systems, use this command:
sudo update-rc.d pgpool defaults - For CentOS, Fedora, or RHEL systems, use this command:
sudo chkconfig --add pgpool
- For Debian or Ubuntu systems, use this command:
It's very likely that any system with vendor-supplied PostgreSQL packages also provides packages for pgpool. These versions are likely to install to expected directories, including initialization scripts and basic working configuration files. This is definitely not the case with the source distribution.
If, for any reason, we would rather install the source package, we have a lot of work ahead. Assuming that we downloaded a version from the pgpool project page, we start the process by extracting the source from the archive, and then enter the resulting directory to perform the necessary installation steps.
The first of these steps is to compile the source into binaries and libraries. The pgpool configure script is fairly standard, so we can change the location of the configuration files with the sysconfdir flag. For the purposes of these instructions, we do not need to alter any other installation or compilation settings.
Later, regular make and make install commands as a root-capable user, distributes the software to all expected locations within the operating system so that they match the PostgreSQL installation.
When we launch pgpool, it will try to log connection and service activity to /var/log/pgpool, so we need to create that location and ensure it's writable by the postgres user. Similarly, pgpool keeps track of its process ID by saving information in /var/run/pgpool. Again, this location should exist and be writable by postgres.
The pgpool source code provides a fairly robust initialization script to start and stop the service, but it only works properly in Red Hat derivatives such as Fedora, CentOS, or Scientific Linux. Also, it doesn't account for the location flags defined by the source configure script, so it would require quite a bit of manual modification to be functional.
Thus, we wrote a generic initialization script that should work on any Linux distribution. This script is included in the code accompanying this chapter, so feel free to use it instead of attempting to locate or build one from scratch. If we move it into the /etc/init.d directory and mark it as executable, standard operating system tools will be able to manage pgpool.
Finally, we add the service to the list of other services that start or stop when the server is shut down or booted up. This ensures pgpool is always available, and we don't have to remember to start or stop it ourselves. Depending on our Linux distribution, the command that registers the script will vary, so we supplied two very common samples.
As with PgBouncer, we provided a very similar initialization script for pgpool. While the pgpool-supplied script is very capable, it does not account for operating systems based on Debian, SUSE, Slackware, and several others. While distributions often supply their own control scripts, anyone compiling from source is simply out of luck.
Thankfully, the initialization script that we supplied should support most major Linux distributions. As usual, it is fully LSB compliant as well. We suggest using our script if at all possible as it is specifically designed to facilitate other recipes in this book. Feel free to examine its contents to see how and why we can make such a bold claim.
- The pgpool website is currently written as a large informative wiki. This makes finding downloads a little more difficult than usual. We've listed the proper download location so that you can easily obtain the software at http://pgpool.net/mediawiki/index.php/Downloads.