The first pooling resource we will explore is named PgBouncer. This is a very popular connection pool written by Skype developers in 2007. The project has been maintained by various developers in subsequent years, but its role of lowering the cost of connecting to PostgreSQL has never changed.
PgBouncer allows PostgreSQL to interact with orders of magnitude of clients than is otherwise possible because its connection overhead is much lower. Instead of huge libraries, accounting for temporary tables, query results, and other expensive resources, it essentially just tracks each client connection in a queue. Then, based on configuration settings, it creates several PostgreSQL connections and assigns them to the connections on a first-come, first-served basis.
This means hundreds, or even thousands of database clients, can theoretically share a single PostgreSQL connection. Of course, we will never suggest implementing a ratio that absurd without testing, yet the possibility presents several new opportunities for better resource allocation.
The first step to get this exciting new functionality is installation of the software. PgBouncer is popular enough for most Linux systems to package it along with other PostgreSQL tools, so we will cover some of the most popular distributions. For the sake of completeness, we also intend to cover pure source installs, which means we can utilize the latest release regardless of the distribution.
Obtain a copy of the latest PgBouncer source code to complete the installation. At the time of writing this book, the latest version is 1.5.4, released on November 28, 2012.
In order to compile the source code properly, we need the PostgreSQL development libraries in addition to the normally installed system binaries. For example, to build on a Debian- or Ubuntu-based system, we will need to install libraries by executing this at the command line:
sudo apt-get install postgresql-server-dev-9.3
We also need the libevent development libraries. Install these from the distribution package repository on a Debian- or Ubuntu-based system with this command:
sudo apt-get install libevent-dev
Then, 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 pgbouncer
To install in a CentOS, Fedora, or other RHEL-based system, execute this command:
sudo yum install pgbouncer
Otherwise, follow these steps to complete a full source-based installation:
- Use these commands to extract the PgBouncer source and enter the source directory:
tar -xzf pgbouncer-1.5.4.tar.gz cd pgbouncer-1.5.4/
- Next, build and install the actual software with these commands:
./configure --prefix=/usr make sudo make install
- Create a location where PgBouncer can maintain activity logs with these commands:
sudo mkdir /var/log/pgbouncer sudo chown postgres /var/log/pgbouncer
- Create a directory where PgBouncer can keep its service lock file with these commands:
sudo mkdir /var/run/pgbouncer sudo chown postgres /var/run/pgbouncer
- Create a configuration directory and fill it with a sample configuration file with these commands:
sudo mkdir /etc/pgbouncer sudo cp etc/pgbouncer.ini /etc/pgbouncer sudo chown -R postgres /etc/pgbouncer
- Copy the
init/pgbouncerinitialization 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/pgbouncer - Finally, add the service to system startup and shutdown.
- For Debian or Ubuntu systems, use this command:
sudo update-rc.d pgbouncer defaults - For CentOS, Fedora, or RHEL systems, use this command:
sudo chkconfig --add pgbouncer
- For Debian or Ubuntu systems, use this command:
As we said before, it's very likely that a system with the vendor-supplied PostgreSQL packages provides packages for PgBouncer. These versions are likely to install to the expected directories; they include initialization scripts and basic working configuration files.
In case we want or need to install PgBouncer ourselves, the process is a bit more involved. Assuming that we downloaded a version from the PgBouncer 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. PostgreSQL supplies a tool named pg_config that lists all of the flags and configuration settings used when it was compiled. In order to pass these to the configure script for PgBouncer, we invoke it for these options, and execute them as one single operation. Afterwards, regular make and make install commands as a root-capable user, distribute the software to all expected locations within the operating system so that they match the PostgreSQL installation.
When we launch PgBouncer, it will try to log connection and service activity to /var/log/pgbouncer, so we need to create the location and ensure it's writable by the postgres user. Similarly, PgBouncer keeps track of its process ID by saving information in /var/run/pgbouncer. Again, this location should exist and be writable by the postgres user.
The PgBouncer source code provides a fairly rudimentary initialization script to start and stop the service, but it only works properly in Debian or derivatives such as Ubuntu or Mint. Also, it doesn't account for location flags defined by the source configure script, so it will 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 as 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 PgBouncer.
Finally, we add PgBouncer to the list of other services that start or stop when the server is shut down or booted up. This ensures the service 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.
Why did we provide a separate initialization script instead of simply modifying the one within the source distribution? It turns out that only three changes are required for it to work on a Debian-based system. However, as we said before, this ignores operating systems based on Red Hat, SUSE, Slackware, and several others. We wish the authors of this tool were more inclusive.
Fortunately, the initialization script we supplied should support most major Linux distributions. Further, it is fully Linux Standard Base (LSB) compliant. Some major high-availability tools assume service control scripts and exit with specific codes under various conditions. When we start discussing the more powerful techniques for automated failover and server control, we will be ready.
- The PgBouncer site contains version downloads, documentation, and much more. Feel free to visit the site to learn more about the project at http://pgfoundry.org/projects/pgbouncer..