Connection pooling, Why we go for connection pooling in PostgreSQL, When
your application demands for very good number of concurrent connection
hits then you need to approach it, because Connection pool sits between
your application and the database.
Idea behind connection pool is that you have enough connections to use
of all the available resources and any incoming requests are re-used
without dropping the database connection and keeping ready for a new
connection to use.
pgbouncer is lightweight connection pooler. pgBouncer runs as a single
process, not spawning a process per connection, which relies on library
named libevent for connection pooling.
pgbouncer setup on PostgreSQL 9.0 is very simple, however there is small
change with the latest version you need to create manual pg_auth file.
pgbouncer uses pg_auth file for user authentication. Earlier verion of
PostgreSQL 9.0, you can find the pg_auth file under
$PGDATA/global/pg_auth, now in the latest version that file has been
removed and placed in pg_catalog as table ‘pg_auth’.
1. First, download libevent library for pgbouncer.
Download link for libevent:
tar -xvf libevent-2.0.12-stable.tar.gz
2. Download the latest pgbouncer tar and configure to your
tar -xvf pgbouncer-1.4
3. Create a libevent-i386.conf file in /etc/ld.so.conf.d directory
4. Run the ldconfig to apply new changes.
5. Change the ownership of pgbouncer utility in PostgreSQL binary to
chown -R postgres:postgres /opt/PostgreSQL/9.0/bin/bin/pgbouncer
6. Create the pgbouncer_auth file for users authentication.
7. Create pgbouncer.ini file with postgres user permission under
8. Start pgbouncer
-bash-4.1$ ./pgbouncer -d /etc/pgbouncer.ini
2011-08-14 11:42:00.925 1949 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 1000, max fds possible: 1010
9. Connect to the databases using pgbouncer
10. Getting help: Connect to pgbouncer database and get helped.
$ psql -p 6432 -U postgres pgbouncer
pgbouncer=# show help;
For better understanding on pg_auth you can find in below link by
Do post your comments which are highly appreciated.