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


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

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 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:
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.
There are lot more parameters, read the pgpool.conf file & docs for more info.


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

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

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.

  1. Pett says:

    Hi, Super post, Need to mark it on Digg

  2. Linsentit says:

    Thanks for writing, I very much liked

  3. streaming says:

    I found your weblog on google and check several of the early posts. Preserve up the excellent operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading through additional from you later on!…

  4. Coolpage Thank You for posting.

  5. Yes! This is really cool, thanks for sharing. Is your RSS feed updated on a weekly or daily basis?

  6. Of course, what a splendid website and educative posts, I definitely will bookmark your website and get your RSS feed (if it works), most of the blogs I go to always have a problem with their RSS…:-)

  7. Nice post. I was checking continuously this blog and I am impressed! Very helpful information specifically the last part 🙂 I care for such info a lot. I was looking for this particular info for a long time. Thank you and good luck.

  8. you could have an amazing weblog right here! would you prefer to make some invite posts on my blog?

  9. It’s really a nice and helpful piece of information. I’m glad that you shared this helpful info with us. Please keep us informed like this. Thanks for sharing…

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>