How to Setup PgBouncer Connection Pooling with PostgreSQL 8.4

PgBouncer is a PostgreSQL connection pooler. Any target application can be connected to PgBouncer as if it were a PostgreSQL server, and PgBouncer will create a connection to the actual server, or it will reuse one of its existing connections. Inturn lowering the performance impact of making new connections to PostgreSQL.

Types of Connection Pooling:

Pgbouncer supports several types of connection pooling while clients request server access.

Session pooling::

Session pooling is the most polite method. When client connects, a server connection will be assigned to it for the whole duration till client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.

Transaction pooling::

A server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.

Statement pooling::

Most aggressive method. The server connection will be put back into pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.

Installation::

1. Download the latest pgbouncer installer using below link

wget http://pgfoundry.org/frs/download.php/2797/pgbouncer-1.3.4.tgz

2. Download the libevent binary using below link

wget http://www.monkey.org/~provos/libevent-1.4.12-stable.tar.gz

3. Unzip the libevent installer

gunzip -c libevent-1.4.12-stable.tar.gz | tar -xv

4. Install the libevent binary

cd libevent-1.4.12-stable.tar.gz

./configure

make

make install

5. Unzip the pgbouncer installer

cd pgbouncer-1.3.4

./configure --prefix=/opt/PostgreSQL/8.4/bin

make

make install

6. Create a libevent-i386.conf file in /etc/ld.so.conf.d directory

vi /etc/ld.so.conf.d/libevent-i386.conf

/usr/local/lib

:wq!

7. Run the ldconfig to affect the new changes

#ldconfig

8. Check the version of the pgbouncer in PostgreSQL binary location:

chown -R postgres:postgres /opt/PostgreSQL/8.4/bin/bin/pgbouncer

cd /opt/PostgreSQL/8.4/bin/bin

[postgres@supportrhel5 bin]$ ./pgbouncer -V

pgbouncer version 1.3.4

9. Locate the pgbouncer.ini file location on installed directory

/opt/PostgreSQL/8.4/bin/share/doc/pgbouncer/pgbouncer.ini

Configuration::

1. Copy the pgbouncer.ini file to /etc directory & give required permissions to postgres user.

cp /opt/PostgreSQL/8.4/bin/share/doc/pgbouncer/pgbouncer.ini /etc

cd /etc

chown -R postgres:postgres pgbouncer.ini

2. Edit the pgbouncer.ini file

[databases]

* = host=127.0.0.1 port=5432

[pgbouncer]

logfile = /tmp/pgbouncer.log

pidfile = /tmp/pgbouncer.pid

listen_addr = *

listen_port = 6432

unix_socket_dir = /tmp

auth_type = trust

auth_file = /opt/postgreSQL/8.4/pgdata/global/pg_auth

admin_users = postgres

stats_users = postgres

pool_mode = transaction

server_reset_query = DISCARD ALL;

server_check_query = select 1

server_check_delay = 10

max_client_conn = 1000

default_pool_size = 20

log_connections = 1

log_disconnections = 1

log_pooler_errors = 1

3. Start pgbouncer

[postgres@supportrhel5 bin]$ ./pgbouncer -d /etc/pgbouncer.ini

2010-08-20 22:58:20.572 11633 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 500, max fds possible: 560

4. Connect databases using pgbouncer

[postgres@supportrhel5 bin]$ ./psql -p 6432 -U postgres -d postgres

psql (8.4.4)

Type "help" for help.

postgres=# \l

5. Getting help: Connect to pgbouncer database and get helped.


$ psql -p 6432 -U postgres pgbouncer

pgbouncer=# show help;

NOTICE: Console usage

DETAIL:

SHOW [HELP|CONFIG|DATABASES|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION]

SET key = arg

RELOAD

PAUSE

SUSPEND

SHUTDOWN

RESUME 


6. Reload pgbouncer.ini file if modified:


pgbouncer=# RELOAD;