Configuring Connection Pooling With Postgres Using PGPOOL-II

Overview of PGPOOL-II

By default, Postgres doesn’t come with a database connection pooling feature. This functionality is provided by PGPOOL-II, an open source project. For high traffic databases, you typically use database connection pooling to reduce the amount of time it takes to get a database connection and speed up access to your database.

PGPOOL-II provides database connection pooling, but also provides replication, load balancing & parallel query features too.

Download  PGPOOL-II

Download PGPOOL-II from here.

You should have a file called pgpool-II-2.2.2.tar.gz or similiar name depending on the current version. Untar & unzip the file.

gzip -d pgpool-II-2.2.2.tar.gz
tar xvf pgpool-II-2.2.2.tar

Install PGPOOL-II

Change to the untarred directory and make, configure & install it. The –prefix is the directory where you want to install pgpool and the –with-pgsql is the directory where Postgres SQL’s client libraries are installed.

cd pgpool-II-2.2.2
./configure --prefix=/opt/PostgresPlus/8.3R2AS/pgpool --with-pgsql=/opt/PostgresPlus/8.3R2AS/dbserver
make
make install

Configure Management Access To PGPOOL-II

You can manage PGPOOL-II from the command line and to do that you need to setup a username & password. In the install directory/etc there is a pcp.sample.conf file. You need to add a username & password to this file. The username can be anything you want, the password has to be an md5 hash which you can create by the supplied command pg_md5. Here’s how:

cd /opt/PostgresPlus/8.3R2AS/pgpool/etc
cp pcp.conf.sample pcp.conf
pg_md5 -p
password: <enter your password>
vi pcp.conf and add your username & password like this:
admin:e8a48653851e28c69d0506508fb27fc5
Save the file.

Configuring PGPOOL-II For Database Connection Pooling

PGPOOL can do a lot of things so you can refer to the manual for advanced features like replication. Here, we’ll just cover the basics of setting up connection pooling. Copy the pgpool.conf.sample to pgpool.conf and set the following parameters:

cd /opt/PostgresPlus/8.3R2AS/pgpool/etc
cp pgpool.conf.sample pgpool.conf
vi pgpool.conf

port - the port pgpool will listen on default is 9999
max_pool - the maximum number of database connections.
connection_life_time -  Default is 0 which means they are not disconnected.
backend_hostname0 = '' - name of host, empty is localhost.
backend_port0 = 5444 - port where Postgres is listening.
backend_weight0 = 1  - 1 for running a single server with no load balancing.

Running PGPOOL-II

Change to the directory where you installed PGPOOL-II and issue the command:

cd /opt/PostgresPlus/8.3R2AS/pgpool/bin
pgpool

If for any reason it doesn’t startup, you can debug the startup process by starting it with the -d option & the -n (no daemon) options:

pgpool -d -n

Connecting To Postgres

For client applications that connect to the Postgres database, make the connection using port where pgpool is listening on, e.g. port 9999  If you cannot connect via this port, check that pgpool is running and that the firewall is not blocking access.

[root@lvpostgres etc]# ps -ef | grep pgpool
501       3983     1  0 Jun25 ?        00:00:00 ./pgpool
501       3986  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3987  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3988  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3989  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3990  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3992  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3993  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request
501       3995  3983  0 Jun25 ?        00:00:00 pgpool: wait for connection request

For more detailed information, see the PGPOOL-II documentation.