Using the PgBouncer Connection Pooler

The PgBouncer utility manages connection pools for PostgreSQL and Greenplum Database connections.

The following topics describe how to set up and use PgBouncer with Greenplum Database. Refer to the PgBouncer web site for information about using PgBouncer with PostgreSQL.

• Overview
• Migrating PgBouncer
• Configuring PgBouncer
• Starting PgBouncer
• Managing PgBouncer

Parent topic: Accessing the Database

Overview

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

The PgBouncer connection pooler, from the PostgreSQL community, is included in your Greenplum Database installation. PgBouncer is a light-weight connection pool manager for Greenplum and PostgreSQL. PgBouncer maintains a pool for connections for each database and user combination. PgBouncer either creates a new database connection for a client or reuses an existing connection for the same user and database. When the client disconnects, PgBouncer returns the connection to the pool for re-use.

PgBouncer shares connections in one of three pool modes:

• Session pooling – When a client connects, a connection is assigned to it as long as it remains connected. When the client disconnects, the connection is placed back into the pool.
• Transaction pooling – A connection is assigned to a client for the duration of a transaction. When PgBouncer notices the transaction is done, the connection is placed back into the pool. This mode can be used only with applications that do not use features that depend upon a session.
• Statement pooling – Statement pooling is like transaction pooling, but multi-statement transactions are not allowed. This mode is intended to enforce autocommit mode on the client and is targeted for PL/Proxy on PostgreSQL.

You can set a default pool mode for the PgBouncer instance. You can override this mode for individual databases and users.

PgBouncer supports the standard connection interface shared by PostgreSQL and Greenplum Database. The Greenplum Database client application (for example, psql) connects to the host and port on which PgBouncer is running rather than the Greenplum Database master host and port.

PgBouncer includes a psql-like administration console. Authorized users can connect to a virtual database to monitor and manage PgBouncer. You can manage a PgBouncer daemon process via the admin console. You can also use the console to update and reload PgBouncer configuration at runtime without stopping and restarting the process.

PgBouncer natively supports TLS.

Migrating PgBouncer

When you migrate to a new Greenplum Database version, you must migrate your PgBouncer instance to that in the new Greenplum Database installation.

• If you are migrating to a Greenplum Database version 5.8.x or earlier, you can migrate PgBouncer without dropping connections. Launch the new PgBouncer process with the -R option and the configuration file that you started the process with:

  $ pgbouncer -R -d pgbouncer.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 detects 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, terminate the new process and the old process will resume.

• If you are migrating to a Greenplum Database version 5.9.0 or later, you must shut down the PgBouncer instance in your old installation and reconfigure and restart PgBouncer in your new installation.

• If you used stunnel to secure PgBouncer connections in your old installation, you must configure SSL/TLS in your new installation using the built-in TLS capabilities of PgBouncer 1.8.1 and later.

• If you used LDAP authentication in your old installation, you must configure LDAP in your new installation using the built-in PAM integration capabilities of PgBouncer 1.8.1 and later. You must also remove or replace any ldap://-prefixed password strings in the auth_file.

Configuring PgBouncer

You configure PgBouncer and its access to Greenplum Database via a configuration file. This configuration file, commonly named pgbouncer.ini, provides location information for Greenplum databases. The pgbouncer.ini file also specifies process, connection pool, authorized users, and authentication configuration for PgBouncer.

Sample pgbouncer.ini file contents:

[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres
pgb_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

Refer to the pgbouncer.ini reference page for the PgBouncer configuration file format and the list of configuration properties it supports.

When a client connects to PgBouncer, the connection pooler looks up the the configuration for the requested database (which may be an alias for the actual database) that was specified in the pgbouncer.ini configuration file to find the host name, port, and database name for the database connection. The configuration file also identifies the authentication mode in effect for the database.

PgBouncer requires an authentication file, a text file that contains a list of Greenplum Database users and passwords. The contents of the file are dependent on the auth_type you configure in the pgbouncer.ini file. Passwords may be either clear text or MD5-encoded strings. You can also configure PgBouncer to query the destination database to obtain password information for users that are not in the authentication file.

PgBouncer Authentication File Format

PgBouncer requires its own user authentication file. You specify the name of this file in the auth_file property of the pgbouncer.ini configuration file. auth_file is a text file in the following format:

"username1" "password" ...
"username2" "md5abcdef012342345" ...

auth_file contains one line per user. Each line must have at least two fields, both of which are enclosed in double quotes (" "). The first field identifies the Greenplum Database user name. The second field is either a plain-text or an MD5-encoded password. PgBouncer ignores the remainder of the line.

(The format of auth_file is similar to that of the pg_auth text file that Greenplum Database uses for authentication information. PgBouncer can work directly with this Greenplum Database authentication file.)

Use an MD5 encoded password. The format of an MD5 encoded password is:

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

You can also obtain the MD5-encoded passwords of all Greenplum Database users from the pg_shadow view.

Configuring HBA-based Authentication for PgBouncer

PgBouncer supports HBA-based authentication. To configure HBA-based authentication for PgBouncer, you set auth_type=hba in the pgbouncer.ini configuration file. You also provide the filename of the HBA-format file in the auth_hba_file parameter of the pgbouncer.ini file.

Contents of an example PgBouncer HBA file named hba_bouncer.conf:

local       all     bouncer             trust
host        all     bouncer      127.0.0.1/32       trust

Example excerpt from the related pgbouncer.ini configuration file:

[databases]
p0 = port=15432 host=127.0.0.1 dbname=p0 user=bouncer pool_size=2
p1 = port=15432 host=127.0.0.1 dbname=p1 user=bouncer
...
[pgbouncer]
...
auth_type = hba
auth_file = userlist.txt
auth_hba_file = hba_bouncer.conf
...

Refer to the HBA file format discussion in the PgBouncer documentation for information about PgBouncer support of the HBA authentication file format.

Starting PgBouncer

You can run PgBouncer 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, add the following text to a file named pgbouncer.ini:

   [databases]
   postgres = host=127.0.0.1 port=5432 dbname=postgres
   pgb_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 lists databases and their connection details. The file also configures the PgBouncer instance. Refer to the pgbouncer.ini reference page for information about the format and content of a PgBouncer configuration file.

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

   "gpadmin" "gpadmin1234"

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

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

3. Launch pgbouncer:

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

   The -d option runs PgBouncer as a background (daemon) process. Refer to the pgbouncer reference page for the pgbouncer command syntax and options.

4. Update your client applications to connect to pgbouncer instead of directly to Greenplum Database server. For example, to connect to the Greenplum database named mydb configured above, run psql as follows:

   $ psql -p 6543 -U <someuser> pgb_mydb

   The -p option value is the listen_port that you configured for the PgBouncer instance.

Managing PgBouncer

PgBouncer provides a psql-like administration console. You log in to the PgBouncer Administration Console by specifying the PgBouncer port number and a virtual database named pgbouncer. The console accepts SQL-like commands that you can use to monitor, reconfigure, and manage PgBouncer.

For complete documentation of PgBouncer Administration Console commands, refer to the PgBouncer Administration Console command reference.

Follow these steps to get started with the PgBouncer Administration Console.

1. Use psql to log in to the pgbouncer virtual database:

   $ psql -p 6543 -U <username> pgbouncer

   The username that you specify must be listed in the admin_users parameter in the pgbouncer.ini configuration file. You can also log in to the PgBouncer Administration Console with the current Unix username if the pgbouncer process is running under that user’s UID.

2. To view the available PgBouncer Administration Console commands, run the SHOW help command:

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

3. If you update PgBouncer configuration by editing the pgbouncer.ini configuration file, you use the RELOAD command to reload the file:

   pgbouncer=# RELOAD;

Mapping PgBouncer Clients to Greenplum Database Server Connections

To map a PgBouncer client to a Greenplum Database server connection, use the PgBouncer Administration Console SHOW CLIENTS and SHOW SERVERS commands:

1. Use ptr and link to map the local client connection to the server connection.
2. Use the addr and the port of the client connection to identify the TCP connection from the client.
3. Use local_addr and local_port to identify the TCP connection to the server.