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.
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 thepgbouncer.ini
configuration file. - Provide the file name of an HBA-format file in the
auth_hba_file
parameter of thepgbouncer.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 theauth-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.
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.
Create an authentication file. The filename should be the name you specified for the
auth_file
parameter of thepgbouncer.ini
file,users.txt
. Each line contains a user name and password. The format of the password string matches theauth_type
you configured in the PgBouncer configuration file. If theauth_type
parameter isplain
, the password string is a clear text password, for example:"gpadmin" "gpadmin1234"
If the
auth_type
in the following example ismd5
, the authentication field must be MD5-encoded. The format for an MD5-encoded password is:"md5" + MD5_encoded(<password><username>)
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 thepgbouncer
command syntax and options.Update your client applications to connect to
pgbouncer
instead of directly to Greenplum Database server. For example, to connect to the Greenplum database namedmydb
configured above, runpsql
as follows:$ psql -p 6543 -U someuser pgb_mydb
The
-p
option value is thelisten_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.
Use
psql
to log in to thepgbouncer
virtual database:$ psql -p 6543 -U username pgbouncer
The username that you specify must be listed in the
admin_users
parameter in thepgbouncer.ini
configuration file. You can also log in to the PgBouncer Administration Console with the current Unix username if thepgbouncer
process is running under that user’s UID.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
[...]
If you update PgBouncer configuration by editing the
pgbouncer.ini
configuration file, you use theRELOAD
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:
- Use
ptr
andlink
to map the local client connection to the server connection. - Use the
addr
and theport
of the client connection to identify the TCP connection from the client. - Use
local_addr
andlocal_port
to identify the TCP connection to the server.