Installing PgBouncer and it’s Features

PgBouncer is a lightweight PostgreSQL connection pooler. PgBouncer is used to lower the performance impact of opening new connections to PostgreSQL. It is a lightweight connection pooler for Postgres and it reduces the processing time and resources for maintaining a large number of client connections to one or more databases. PgBouncer is a pre-bundled enterprise module which increases the number of user connections that can be handled in a high performance environment. If any target application can be connected to PgBouncer, it will create a connection to the actual server, or it will reuse one of its existing connections.

 

PgBouncer supports three types of pooling when rotating connections:

1) Session pooling: It is the politest and default method. When a client connects, a server connection will be assigned to it for the life of client get connected. The server connection will be put back into the pool after client is disconnected.

2) Transaction pooling: In this pooling a server connection is assigned to a client only during a transaction. When PgBouncer notices that the transaction is over, the server connection will be put back into the pool.

3) Statement pooling: It is the most aggressive method. The server connection will be put back into the pool immediately after a query completes. This is transaction pooling with Multi-statement transactions are disallowed.

 

PgBouncer is used to reduce the impact of opening new client connections to Postgres Plus databases. It is done by maintaining and using a connection pool. Connection pool is a cache of database. When an application connects to PgBouncer as if it was a Postgres Plus database. Then it creates a connection to the actual database server, or it reuses one of the existing connections from the pool. A connection pool is established for a unique combination of the PgBouncer database alias name and the database server user name connecting to the database. The database server user name used to connect to the database may be either the user name supplied by the client application, or it may be a user name configured with PgBouncer that overrides the client supplied user name. PgBouncer administration is done through the PgBouncer Admin Console. This Admin Console is accessed through the psql utility program by connecting to PgBouncer with a special “virtual” database called pgbouncer. You can use the SHOW command to display information and statistics on connection pool usage after logged into the Admin console.

PgBouncer requires an authentication file which contains a list of users and passwords. Passwords in the file may be either clear text, MD5-encoded, or an LDAP/AD lookup string. We can also set up PgBouncer to query the destination database for users that are not in the authentication file. PgBouncer does not support SSL, so the connections between database clients and PgBouncer are not encrypted. You can use stunnel to encrypt these connections. Stunnel is a free software utility and it is a proxy that uses the OpenSSL cryptographic library to add TLS encryption to network services.

 

Command line switches of PgBouncer

-d – It is used to run in background. Without using -d switch the process will run in foreground.

-R – Do an online restart. That means connecting to the running process, loading the open sockets from it, and then using them. If there is no active process, boot normally.

-u user – Switch to the given user on startup.

-v – Increase verbosity. Can be used multiple times.

-q – Be quiet – do not log to stdout. Note this does not affect logging verbosity, only that stdout is not to be used. For use in init.d scripts.

-V – Show version.

-h- Show short help.

 

Installing pgbouncer

1) # yum install pgbouncer

2) To edit the configuration file open file pgbouncer.ini

# vi /etc/pgbouncer/pgbouncer.ini

3) To start the pgbouncer

# service pgbouncer start

 

Features

1) Low memory requirements: PgBouncer does not need to see full packet at once. By default it requires only 2k per connection.

2) The destination databases can reside on different hosts so it is not tied to one back-end server.

3) It supports online reconfiguration for most of the settings.

4) It supports online restart/upgrade without dropping client connections.

5) It supports protocol V3 only, so back-end version must be 7.4 or greater.

 

If you need any further assistance please contact our support department.