In the previous chapter we have taken a deep look at pgbouncer and learned how to use it to optimize replicated setups as much as possible. In this chapter we will take a look at a tool that is often referred to as counterpart of pgbouncer. The idea of pgpool is pretty similar to that of pgbouncer—however, it has been designed to do a lot more than pgbouncer. While pgbouncer is more lightweight and optimized to do exactly one thing, pgpool offers a lot more features and promises additional functionality.
Depending on your needs you can decide freely which tool is better for your specific setup.
Just as we have seen for PostgreSQL, pgbouncer and most other tools covered in this book, we can either install pgpool from source or just use a binary. Again, we will describe how the code can be compiled from source.
To install pgpool we have to download it first:
http://www.pgpool.net/mediawiki/images/pgpool-II-3.2.4.tar.gz
Once this has been done, we can extract the tarball:
$ tar xvfz pgpool-II-3.2.4.tar.gz
The installation procedure is just like we have seen already. The first thing we have to call is configure along with some parameters. In our case the main parameter is --with-pgsql, which tells the build process where to find our PostgreSQL installation.
$ ./configure --with-pgsql=/usr/local/pgsql/
Now we can compile and install the software easily:
make make install
What you have just seen is a basic pgpool installation. But to make things work really nicely it can be beneficial to install additional modules such as pgpool-regclass and
insert_lock. Installing pgpool-regclass is important to handle DDL replication. insert_lock is important to handle distributed writes. It is highly recommended to install this module because otherwise handling DDLs won't work. Up to now we have not seen a practical setup where using this module did not make sense.
Let us install pgpool-regclass first:
cd sql/pgpool-regclass/ make make install
To enable the module we have to deploy the pgpool-regclass.sql file. The module must be present in all databases we are going to use. The easiest way to achieve that is to simply load the SQL file into template1. Whenever a new database is created template1 will be cloned so all new databases will automatically have this module.
The same applies to insert_lock.sql, which can be found in the sql directory of the pgpool source code. The easiest solution is to load this into template1 directly:
psql -f insert_lock.sql template1
Once the code has been installed we can move forward and see how we can use pgpool.