How to Setup PgBouncer Connection Pooling with Postgres Plus Standard Server

Platform: 
All Platforms

 

PgBouncer is a lightweight connection pooler for Postgres Plus that dramatically reduces the processing time and resources for maintaining a large number of client connections to one or more databases. PgBouncer is typically used to increase the number of user connections that can be handled in a high performance environment. This tutorial shows how to effectively setup and configure a pgBouncer environment for Postgres PlusStandard Server.

Introduction

This EnterpriseDB Quick Tutorial helps you get started with the Postgres Plus Standard Server database product in a Linux or Windows environment. It is assumed that you have already downloaded and installed Standard Server on your desktop or laptop computer.

This Quick Tutorial is designed to help you expedite your Technical Evaluation of Postgres Plus Standard Server. For more informational assets on conducting your evaluation of Postgres Plus, visit the self-service portion of the EnterpriseDB web site: Postgres Plus Open Source Adoption center.

In this Quick Tutorial you will learn how to do the following:

  • key concepts of the client connection process
  • how to configure PgBouncer for Linux or Windows
  • set global configuration parameters
  • set up authentication through PgBouncer
  • use the admin console to monitor connection pools

Feature Description

PgBouncer is a pre-bundled enterprise module installed by default with Postgres Plus Standard Server.

PgBouncer Concepts

PgBouncer reduces the impact of opening new client connections to Postgres Plus databases by maintaining and using a cache of database connections called a connection pool. An application connects to PgBouncer as if it were a Postgres Plus database. PgBouncer then 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 (typically equates to a Postgres Plus database 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.

The client connection process occurs in the following sequence:

Step 1: The client application attempts to connect to PgBouncer using the same database connection interface that it uses to connect to any Postgres Plus database. However the client application supplies the IP address of the host running PgBouncer and the port number on which PgBouncer is listening for connections (default port number is 6432) instead of the respective values for the Postgres Plus database server.

Step 2: The database name supplied by the client application during the connection attempt must match one of a list of PgBouncer database alias names that are maintained in a plain-text configuration file accessed by PgBouncer. If there is no match, the client connection is rejected with an error message.

Step 3: The user name and password supplied by the client application during the connection attempt must match one of a list of user name and password pairs that are maintained in a plain-text authentication file accessed by PgBouncer. If there is no match, the client connection is rejected with an error message.

Step 4: If there is already an existing connection pool for the combination of database alias name passed by the client application and the database server user name to be used for the connection, then an available connection from this pool is assigned to the client application. The client can now access the database.

If there is no available connection in the pool, then one is created (see Step 6) provided the pool's connection limit has not been reached. If the pool has reached its connection limit, the client must wait until a connection in the pool becomes available.

Step 5: If there is no existing pool, then one is created for the combination of database alias name and database server user name.

Step 6: For each database alias name in the PgBouncer configuration file, there is a connection string containing parameter/value pairs PgBouncer uses these pairs to connect to a Postgres Plus database. If certain parameters are omitted from the connection string, the values supplied by the client application are used instead. The database server user name and password, and the database name, itself, are examples of connection parameters that may come from the client application if they are not supplied in the connection string.

PgBouncer attempts to establish a connection to the database using the parameters in the connection string, possibly supplemented by values passed by the client application. The usual Postgres Plus connection authentication process occurs. Checks are made using the pg_hba.conf file and the database server's user names and passwords. If Postgres Plus authentication fails, then the client connection is rejected with an error message. If authentication succeeds, then a database connection is established, becomes part of the pool, and is assigned to the client application. The client can now access the database.

Step 7: Once PgBouncer determines a client is finished with a connection, the connection is returned to the pool and becomes available for use by other clients without the additional overhead of establishing a new connection.

Note: Once a pool of database server connections is established, a change to the pg_hba.conf file or the database server's user names and passwords may not prevent a client from connecting using what may now be an invalid user name and password combination. If the authentication described in steps 2 and 3 succeed, and the condition described in Step 4 is true, PgBouncer connects the client using an existing connection from the pool. Stop and restart PgBouncer to remove all connection pools so new clients are forced to go through the Postgres Plus authentication process as well as the PgBouncer authentication steps.

PgBouncer supports three types of pooling when rotating connections:

  • Session pooling. A server connection is assigned to the client application for the life of the client connection. PgBouncer releases the server connection back into the pool once the client application disconnects. This is the default method.
  • Transaction pooling. A server connection is assigned to the client application for the duration of a transaction. When PgBouncer detects the completion of the transaction, it releases the server connection back into the pool.
  • Statement pooling. A server connection is assigned to the client application for each statement. When the statement completes, the server connection is returned back into the pool. Multi-statement transactions are not permitted for this mode.

Administration of PgBouncer is done through the PgBouncer Admin Console. The Admin Console is accessed through the psql utility program by connecting to PgBouncer with a special "virtual" database named pgbouncer. Once logged into the Admin Console, use the SHOW command to display information and statistics on connection pool usage.

The PgBouncer Index page is an index to the complete PgBouncer documentation.

 

Administration of PgBouncer is done through the PgBouncer Admin Console. The Admin Console is accessed through the psql utility program by connecting to PgBouncer with a special "virtual" database named pgbouncer. Once logged into the Admin Console, use the SHOW command to display information and statistics on connection pool usage.

The PgBouncer Index page is an index to the complete PgBouncer documentation.

Additional information about PgBouncer and the PgBouncer project can be found on the Postgres Community Projects page of the EnterpriseDB web site.

Tutorial Steps

For a detailed description of the steps that show how to set up and configure PgBouncer with Postgres Plus Standard Server, please download the PDF:

How to Set Up PgBouncer Connection Pooling with Postgres Plus Standard Server