PgBouncer Installation

Getting binaries

# apt-get install pgbouncer

Binary we got is located @ /usr/sbin/pgbouncer

Configuration

Postgres = host=127.0.0.1 port=6432 dbname=postgres connect_query='SELECT 1'

Egov= host=127.0.0.1 port=6432 dbname=egov connect_query='SELECT 1'

Above are the DB list, which pgbouncer only connects. If we give any other DB information to pgbouncer, it won't accept that DB.

logfile = /var/log/postgresql/pgbouncer.log

Log fie location for pgbouner

pidfile = /var/run/postgresql/pgbouncer.pid

Pid file location for pgbouncer

listen_addr = *

Here we configured the pgbouncer to allow all the remote IPs. Which we configure as '127.0.0.1' in postgresql.conf

listen_port = 5432

Default postgres port for pgbouncer.

max_client_conn = 5000

Total maximum client connections from app to pgBouncer.

default_pool_size = 2500

Total maximum connections from pgBouncer to Database.

That means, at any moment we have only 2500 active connections from application to DB side, and 2500 in waiting state.

In future, if we want to increase the

Active connections to DB, we have to change this parameter, as well max_connections in postgresql.conf file.auth_file = /etc/pgbouncer/userlist.txt

List of users and their md5 password (from pg_authid table). In future, if we want to give access to another user, we have add that

user list in above file.