A database connection pool is a cache of database connections. Once a pool of connections is established, connection pooling eliminates the overhead of creating database connections, so clients connect much faster and the server load is reduced.

The PgBouncer connection pooler, from the PostgreSQL community, is included with Greenplum Database. PgBouncer can manage connection pools for multiple databases, and databases may be on different Greenplum Database clusters or PostgreSQL backends. PgBouncer creates a pool for each database user and database combination. A pooled connection can only be reused for another connection request for the same user and database.

The client application requires no software changes, but connects to the connection pool's host and port instead of the Greenplum Database master host and port. PgBouncer either creates a new database connection or reuses an existing connection. When the client disconnects, the connection is returned to the pool for re-use.

PgBouncer supports the standard connection interface that PostgreSQL and Greenplum Database share. A client requesting a database connection provides the host name and port where PgBouncer is running, as well as the database name, username, and password. PgBouncer looks up the requested database (which may be an alias for the actual database) in its configuration file to find the host name, port, and database name for the database connection. The configuration file entry also determines how to authenticate the user and what database role will be used for the connection—a "forced user" can override the username provided with the client's connection request.

PgBouncer requires an authentication file, a text file that contains a list of users and passwords. Passwords may be either clear text, MD5-encoded, or an LDAP/AD lookup string. You can also set up PgBouncer to query the destination database for users that are not in the authentication file.

A default pool mode can be set for the PgBouncer instance and the mode can be overridden for individual databases and users.

By connecting to a virtual pgbouncer database, you can monitor and manage PgBouncer using SQL-like commands. Configuration parameters can be changed without having to restart PgBouncer, and the configuration file can be reloaded to pick up changes.

PgBouncer does not yet support SSL connections. If you want to encrypt traffic between clients and PgBouncer, you can use stunnel, a free software utility that creates TLS-encrypted tunnels using the OpenSSL cryptography library. See Securing PgBouncer Connections with stunnel for directions.

PgBouncer can be run on the Greenplum Database master or on another server. If you install PgBouncer on a separate server, you can easily switch clients to the standby master by updating the PgBouncer configuration file and reloading the configuration using the PgBouncer Administration Console.

Follow these steps to set up PgBouncer.

1. Create a PgBouncer configuration file, for example pgbouncer.ini. Here is a simple configuration file:

   [databases]
   postgres = host=127.0.0.1 port=5432 dbname=postgres
   mydb = host=127.0.0.1 port=5432 dbname=mydb
   
   [pgbouncer]
   pool_mode = session
   listen_port = 6543
   listen_addr = 127.0.0.1
   auth_type = md5
   auth_file = users.txt
   logfile = pgbouncer.log
   pidfile = pgbouncer.pid
   admin_users = gpadmin

   The file is in the .ini file format and has three sections: databases, pgbouncer, and users. The databases section lists databases with their connection details. The pgbouncer section configures the PgBouncer instance.

2. Create an authentication file. The name of the file must match the auth_file parameter in the pgbouncer.ini file, users.txt in this example. Each line contains a user name and password. The format of the password string matches the auth_type parameter in the PgBouncer configuration file. If the auth_type parameter is plain, the password string is a clear text password, for example:

   "gpadmin" "gpadmin1234"

   The auth_type in the following example is md5, so the authentication field must be MD5-encoded. The format for an MD5-encoded password is:

   "md5" + MD5(<password><username>)

   You can use the Linux md5sum command to calculate the MD5 string. For example, if the gpadmin password is admin1234 the following command prints the string for the password field:

   $ user=gpadmin; passwd=admin1234; echo -n md5; echo $passwd$user | md5sum
   md53ce96652dedd8226c498e09ae2d26220

   And here is the MD5-encoded entry for the gpadmin user in the PgBouncer authentication file:

   "gpadmin" "md53ce96652dedd8226c498e09ae2d26220"

   To authenticate users against an LDAP or Active Directory server, the password field contains an LDAP lookup string. For example:

   "gpdbuser1" "ldap://10.0.0.11:10389/uid=gpdbuser1,ou=users,ou=system"

   For Active Directory, a user entry looks like this example:

   "gpdbuser2" "ldap://10.0.0.12:389/gpdbuser2"

   See Setting up LDAP Authentication with PgBouncer for the steps to configure PgBouncer to authenticate users with an LDAP server.

   For details about the authentication file, see the "Authentication File Format" section of the PgBouncer reference in the Greenplum Database Utility Guide.

3. Launch pgbouncer:

   $ $GPHOME/bin/pgbouncer -d pgbouncer.ini

   The -d or --daemon option runs PgBouncer as a background process. See the PgBouncer reference in the Greenplum Database Utility Guide for the pgbouncer command syntax and its options.

4. Update client applications to connect to pgbouncer instead of directly to Greenplum Database server. To start psql, for example:

   $ psql -p 6543 -U someuser postgres

When a connection is returned to the pool, it must be reset to the state of a newly created connection. PgBouncer accomplishes this by issuing a query before returning a connection to the pool. PostgreSQL 8.3 and later have the DISCARD ALL command for this purpose and it is the default reset query for the standard PgBouncer distribution. Greenplum Database does not support DISCARD ALL and if it is used an error is logged.

RESET ALL; SET SESSION AUTHORIZATION DEFAULT

This is the default server reset query in the modified version of PgBouncer included with Greenplum Database 4.7.0 and later. Although the default differs from PgBouncer, it helps to ensure that the connection pool is transparent to Greenplum Database users and client applications do not have to be modified to use a connection pool.

For more information about the server_reset_query parameter and the PgBouncer configuration file, see the PgBouncer reference in the Greenplum Database Utility Guide.

PgBouncer has an administrative console, which is accessed by logging into the pgbouncer virtual database. The console accepts SQL-like commands that allow you to monitor, reconfigure, and manage PgBouncer.

Follow these steps to get started with the PgBouncer administrative console.

1. Log in to the pgbouncer virtual database with psql:

   $ psql -p 6543 -U username pgbouncer

   The username must be set in the admin_users parameter in the pgbouncer.ini configuration file. You can also log in with the current Unix username if the pgbouncer process is running with that user's UID.

2. To see the available commands, run the show help command:

   pgbouncer=# show help;
   NOTICE:  Console usage
   DETAIL:
          SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
   	SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
   	SHOW DNS_HOSTS|DNS_ZONES
   	SET key = arg
   	RELOAD
   	PAUSE [<db>]
   	RESUME [<db>]
   	DISABLE <db>
   	ENABLE <db>
   	KILL <db>
   	SUSPEND
   	SHUTDOWN

3. If you make changes to the pgbouncer.ini file, you can reload it with the RELOAD command:

   pgbouncer=# RELOAD;

For complete documentation for all of the administration console commands, see the "PgBouncer Administration Console Commands" section of the PgBouncer reference in the Greenplum Database Utility Guide.

You can upgrade PgBouncer without dropping connections. Just launch the new PgBouncer process with the -R option and the same configuration file:

$ pgbouncer -R -d config.ini

The -R (reboot) option causes the new process to connect to the console of the old process through a Unix socket and issue the following commands:

SUSPEND;
SHOW FDS;
SHUTDOWN;

When the new process sees that the old process is gone, it resumes the work with the old connections. This is possible because the SHOW FDS command sends actual file descriptors to the new process. If the transition fails for any reason, kill the new process and the old process will resume.

You can authenticate Greenplum Database users against your LDAP or Active Directory service when using the PgBouncer connection pooler. When you have LDAP authentication working, you can secure client connections to PgBouncer by setting up stunnel, as described in Securing PgBouncer Connections with stunnel.

PgBouncer does not have SSL support, so connections between database clients and PgBouncer are not encrypted. To encrypt these connections, you can use stunnel, a free software utility. stunnel is a proxy that uses the OpenSSL cryptographic library to add TLS encryption to network services. Newer versions of stunnel support the PostgreSQL libpq protocol, so Greenplum and PostgreSQL clients can use libpq SSL support to connect securely over the network to a stunnel instance set up as a proxy for PgBouncer. If you use a version of stunnel without libpq support, you need to set up a stunnel instance on both the client and PgBouncer host to create a secure tunnel over the network.

On most platforms, you can install stunnel using the system package manager. For Windows, you can download an installer from https://www.stunnel.org/index.html. You can also download and install stunnel from source if a packaged version is unavailable, if you want to upgrade to a newer version, or if you want to upgrade to a newer OpenSSL version. The next section provides steps to download, compile, and install OpenSSL and stunnel.

For complete stunnel documentation, visit the stunnel documentation web site.