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.

In order not to compromise transaction semantics for connection pooling, PgBouncer supports several types of pooling when rotating connections:

• Session pooling - Most polite method. When a client connects, a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.

• Transaction pooling - A server connection is assigned to a client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.

• Statement pooling - Most aggressive method. The server connection will be put back into the pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.

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, kill 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 currently use the built-in PAM LDAP integration, you may choose to migrate to the new native LDAP PgBouncer integration introduced in Greenplum Database version 6.22; refer to Configuring LDAP-based Authentication for PgBouncer for configuration information.

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" ...
"username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"

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 password, an MD5-encoded password, or or a SCRAM secret. 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.

PostgreSQL SCRAM secret format:

SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>

See the PostgreSQL documentation and RFC 5803 for details on this.

The passwords or secrets stored in the authentication file serve two purposes. First, they are used to verify the passwords of incoming client connections, if a password-based authentication method is configured. Second, they are used as the passwords for outgoing connections to the backend server, if the backend server requires password-based authentication (unless the password is specified directly in the database’s connection string). The latter works if the password is stored in plain text or MD5-hashed.

SCRAM secrets can only be used for logging into a server if the client authentication also uses SCRAM, the PgBouncer database definition does not specify a user name, and the SCRAM secrets are identical in PgBouncer and the PostgreSQL server (same salt and iterations, not merely the same password). This is due to an inherent security property of SCRAM: The stored SCRAM secret cannot by itself be used for deriving login credentials.

The authentication file can be written by hand, but it’s also useful to generate it from some other list of users and passwords. See ./etc/mkauth.py for a sample script to generate the authentication file from the pg_shadow system table. Alternatively, use

auth_query

instead of auth_file to avoid having to maintain a separate authentication file.

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.

Configuring LDAP-based Authentication for PgBouncer

Starting in Greenplum Database version 6.22, PgBouncer supports native LDAP authentication between the psql client and the pgbouncer process. Configuring this LDAP-based authentication is similar to configuring HBA-based authentication for PgBouncer:

• Specify auth-type=hba in the pgbouncer.ini configuration file.
• Provide the file name of an HBA-format file in the auth_hba_file parameter of the pgbouncer.ini file, and specify the LDAP parameters (server address, base DN, bind DN, bind password, search attribute, etc.) in the file.

Note

You may, but are not required to, specify LDAP user names and passwords in the auth-file. When you do not specify these strings in the auth-file, LDAP user password changes require no PgBouncer configuration changes.

If you enable LDAP authentication between psql and pgbouncer and you use md5, password, or scram-sha-256 for authentication between PgBouncer and Greenplum Database, ensure that you configure the latter password independently.

Excerpt of an example PgBouncer HBA file named hba_bouncer_for_ldap.conf that specifies LDAP authentication follows:

host all user1 0.0.0.0/0 ldap ldapserver=<ldap-server-address> ldapbasedn="CN=Users,DC=greenplum,DC=org" ldapbinddn="CN=Administrator,CN=Users,DC=greenplum,DC=org" ldapbindpasswd="ChangeMe1!!" ldapsearchattribute="SomeAttrName"

Refer to the Greenplum Database LDAP Authentication discussion for more information on configuring an HBA file for LDAP.

Example excerpt from the related pgbouncer.ini configuration file:

[databases]
* = port = 6000 host=127.0.0.1
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = hba
auth_hba_file = hba_bouncer_for_ldap.conf
...

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-admin 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|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION|...
       SET key = arg
       RELOAD
       PAUSE
       SUSPEND
       RESUME
       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.