Install & Configure PgBouncer | Pooling in PostgreSQL #1

This is the first post among the series of posts which demonstrates various tools to set up database pooling in a PostgreSQL database server. In this post we will learn how to install, configure and connect to a database pool using the popular tool – PgBouncer.

Table of Contents

1. What is Database Pooling & Why should I use it?
2. Installing PgBouncer
3. Configuring PgBouncer
4. Connecting to PgBouncer
5. Monitoring PgBouncer

What is Database Pooling & Why should I use it?

A typical production level web application involves hundreds of servers eager to get their bite in the database server. This generates thousands of database queries to our poor database server which has only limited resources to serve with. Every query requires resources such as memory, CPU time, port allocation and so on.

We need some ways to avoid flooding the database servers with thousands of connections, at the same time fulfilling all the requests from all clients. As a remedial solution, we inserts a pool between database and servers and redirect all the connection requests to the Pool. Instead of servers directly connecting to the database, they now only connects to the pool.

Database pooling limits the number of connections to the database, and it reuse the existing connections as soon as the current transaction is complete. Reusing the existing connections avoid the overhead of creating new connections from scratch and allocating all kind of resources needed by them. It puts the overwhelming connection request in to a queue, and serve them as soon as the currently running transactions completes.  In this way, pooling maintains a limited, but active, number of connection instead of creating a large number of idle connections.

Installing PgBouncer

PgBouncer is a popular connection pool for PostgreSQL which, based on the configuration, creates connections and assign incoming connections requests to it in a first-come-first served manner. Before installing PgBouncer, make sure that you have the PostgreSQL Server installed correctly.

If not, go through the following post and install it.

How to: Install PostgreSQL 9.3 on Ubuntu – Complete Guide

Also, install the PostgreSQL development  libraries and libevent libraries:

sudo apt-get install postgresql-server-dev-9.3
sudo apt-get install libevent-dev

Now, install PgBouncer using the following command:

sudo apt-get install pgbouncer

Thats it!

Configuring PgBouncer

PgBouncer has a large set of configurations. Most of them are nicely explained in the example configuration file. Though, we will discuss the most important parameters here.

1. Creating Database Authentication File for PgBouncer
   PgBouncer is not part of PostgreSQL server. Being a third party software we need to provide the list of database users along with encrypted passwords to it, so that PgBouncer can authenticate the clients requesting for a pooled resource. This also implies that that whenever a new database user is created the user list also must be updated.
   Run the following query in a query browser or terminal as “postgres” user to generate the user list for PgBouncer:

   COPY (
   SELECT '"' || rolname || '" "' ||
   CASE WHEN rolpassword IS null THEN '' ELSE rolpassword END || '"'
   FROM pg_authid
   )
   TO '/etc/pgbouncer/userlist.txt';

2. Configuring pgbouncer.ini
   PgBouncer uses the pgbouncer.ini file located in the /etc/pgbouncer/ folder to store it’s settings.
   Open that file for editing as the postgres unix user:

   sudo su postgres
   vi /etc/pgbouncer/pgbouncer.ini

   1. Configuring the Database
      In the [database] section of the pgbouncer.ini file we have to map our database for which we want to enable pooling. As an example, add the below line under [database] to map the default database – postgres:
      

      postgres = host=localhost

   2. Changing the Listen Address
      Under the section [pgbouncer], we have specify the IP address to which the PgBouncer daemon should listen to. Following line will make it listen to all IP address currently assigned to the server.

      listen_addr = *

   3. Setting the auth_type
      This setting is similar to the pg_hba.conf file of PostgreSQL server. Setting this to md5, will force the client to use passwords, and PgBouncer will use the userlist.txt we created in step 1.

      auth_type = md5

   4. Setting the admin_users
      We have to provide one or more database users for PgBouncer to use with whenever the admin console of PgBouncer is accessed. The following will suffice for now:

      admin_users = postgres

   5. Setting the max_client_conn
      max_client_conn is the number of clients that can connect to the pool at any instance. The default is 100, but it is application specific, it depends upon the nature of your application. Here I am setting it to 500. Feel free to experiment with different numbers.

      max_client_conn = 500

   6. Setting the default_pool_size and reserve_pool_size
      These two setting are per user/database pair. default_pool_size determines how many server connections to allow per each user/database combination.
      reserve_pool_size is the number of additional connections to allow in case of any trouble. Again, these settings are the core of the PgBouncer and you should come up with an optimum figure to be used based on the nature of your application.

      default_pool_size = 20
      reserve_pool_size = 5

   7. Save the file and exit vi.

 For complete list of all parameters with description, go to this link.

Start the PgBouncer service using the following command:

sudo service pgbouncer start

Use the following command to check if it actually started running:

sudo service pgbouncer status
  * pgbouncer is running

If and only if the sudo service pgbouncer start command return no message and status command returns:  * pgbouncer is not running, there is something wrong with the init.d file and we should perform a few additional fixes:

1. Run the command to open pgbouncer initialization script:

   sudo gedit /etc/init.d/pgbouncer

2. Replace the following part:

   case "$1" in
     start)
       # Check if we are still disabled in /etc/default/pgbouncer
       [ "${START:-}" = "0" ] && exit 0
       log_daemon_msg "Starting PgBouncer" $NAME
       test -d $PIDDIR || install -d -o postgres -g postgres -m 2775 $PIDDIR
       $SSD --start --chuid $RUNASUSER --oknodo -- $OPTS 2> /dev/null
       log_e
       ;;

   To:

   case "$1" in
    start)
    log_daemon_msg "Starting PgBouncer" $NAME
    test -d $PIDDIR || install -d -o postgres -g postgres -m 2775 $PIDDIR
    $SSD --start --chuid $RUNASUSER --oknodo -- $OPTS 2> /dev/null
    log_end_msg $?
    ;;

3. Save it and try to start the service again

Connecting to PgBouncer

Now that, we have started PgBouncer Daemon successfully, next we need to connect our clients to Pool. Doing so will make all server connections to only interact with PgBouncer, not with PostgreSQL server. The commands are similar but the only difference is that, we have to use the port 6432 instead of the PostgreSQL default 5432. With psql, to connect to postgres database use the following query

psql -p 6432 -h localhost postgres

With PgAdmin, we can connect like shown below:

Furthermore, you should use the 6432 port for all applications you intended to enable pooling.

Monitoring PgBouncer

PgBouncer provides an administration console through which we can list the server connections and clients, and also evaluate the health of Pool.

For this we have to connect to a special pseudo database named pgbouncer as follows:

psql -p 6432 -h localhost -d pgbouncer

Once we connect to it, we can issue various commands as follows:

• Listing server connections
  

SHOW SERVERS;

This command will list all server connections maintained by the PgBouncer. It will provide a number of additional informations such as  db user name, port, connection time etc.

• Listing client connections
  

SHOW CLIENTS;

This command will list all clients using or waiting for a PostgreSQL connection. Again, various additional details are available here.

• Monitoring pool health

  SHOW POOLS;
  SHOW STATS;

  These two queries gives useful informations about PgBouncer Pools and the statistics for Pools.

Now, that is it about PgBouncer and pooling. Any Questions or Queries are most welcomed.

References: PostgreSQL 9 High Availability Cookbook