Using the PgBouncer Connection Pooler
A newer version of this documentation is available. Use the version menu above to view the most up-to-date release of the Greenplum 5.x documentation.
Using the PgBouncer Connection Pooler
The PgBouncer utility manages connection pools for PostgreSQL and Greenplum Database connections.
The Greenplum Database installation includes the PgBouncer connection pooling software. The following topics describe how to set up and use PgBouncer with Greenplum Database. See the PgBouncer Web site for information about using PgBouncer with PostgreSQL.
- Overview
- Configuring and Starting PgBouncer
- Managing PgBouncer
- Setting up LDAP Authentication with PgBouncer
- Securing PgBouncer Connections with stunnel
Also, see reference information for PgBouncer in the Greenplum Database Utility Guide.
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 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.
- 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.
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.
Configuring and Starting PgBouncer
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.
- 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.
- 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.
- 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.
- 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
Server Reset Query
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.
Managing PgBouncer
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.
- 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.
- 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
- If you make changes to the pgbouncer.ini file, you can reload it with
the RELOAD command:
pgbouncer=# RELOAD;
To map clients to server connections, use the SHOW CLIENTS and SHOW SERVERS views:
- Use ptr and link to map the local client connection to the server connection.
- Use addr and port of the client connection to identify the TCP connection from the client.
- Use local_addr and local_port to identify the TCP connection to the server.
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.
Upgrading PgBouncer
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.
Setting up LDAP Authentication with PgBouncer
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.
- Create Greenplum Database users in the LDAP/AD server, for example gpdbuser1 and gpdbuser2.
- Create the corresponding Greenplum Database users in the
database:
createuser gpdbuser1 createuser gpdbuser2
- Add the users to the Greenplum Database pg_hba.conf
file:
host all gpdbuser1 0.0.0.0/0 trust host all gpdbuser2 0.0.0.0/0 trust
- Create a PgBouncer config.ini file with the following
contents:
[databases] * = host=GPDB_host_addr port=GPDB_port [pgbouncer] listen_port = 6432 listen_addr = 0.0.0.0 auth_type = plain auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid ignore_startup_parameters=options
- Create the users.txt PgBouncer authentication file. The first field
is the user name and the second is the LDAP or Active Directory lookup string for the
user. For OpenLDAP or ApacheDS, for example, a user entry in the
users.txt looks like
this:
"gpdbuser1" "ldap://10.0.0.11:10389/uid=gpdbuser1,ou=users,ou=system"
For Active Directory, a user entry looks like this example:"gpdbuser1" "ldap://10.0.0.12:389/gpdbuser1"
- Start PgBouncer with the config.ini file you
created:
$ pgbouncer -d config.ini
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.
Installing stunnel From Source
stunnel requires a version of the OpenSSL development package greater than 1.0. The following instructions download and build OpenSSL and stunnel for Red Hat or CentOS. Be sure to check https://www.stunnel.org/index.html and http://openssl.org for the latest versions.
- Set the PREFIX environment variable to the directory where you want
to install stunnel, for example /usr/local/stunnel or
/home/gpadmin/stunnel. If you choose to install stunnel in a
system directory, you may need to run the make install commands as
root.
export PREFIX=/path/to/install_stunnel
- The following commands download, build, and install OpenSSL version
1.0.2c:
wget ftp://ftp.openssl.org/source/openssl-1.0.2c.tar.gz tar xzf openssl-1.0.2c.tar.gz cd openssl-1.0.2c ./Configure linux-x86_64 --prefix=$PREFIX shared no-asm make make install_sw cd ..
- Download, build, and install stunnel with the following
commands:
wget --no-check-certificate https://www.stunnel.org/downloads/stunnel-5.22.tar.gz tar xzf stunnel-5.22.tar.gz cd stunnel-5.22 LDFLAGS="-Wl,-rpath,'"'$$'"ORIGIN/../lib'" ./configure --prefix=$PREFIX --with-ssl=$PREFIX make make install
Setting up Stunnel for PgBouncer
Set up a stunnel instance as a proxy for PgBouncer. The stunnel instance accepts secure connections from database clients on its own port, decrypts the packets and forwards them to the PgBouncer instance on the same host (or on another host in a protected local network). PgBouncer results, including database results from connections managed by PgBouncer, are encrypted and returned over the network to the client.
- On the server running PgBouncer, create a directory for stunnel configuration files, for example /home/gpadmin/stunnel.
- Create a certificate and key file to use for authenticating. You can also use an
existing certificate and key, or create a new pair with the following
openssl
command:
$ openssl req -new -x509 -days 3650 -nodes -out stunnel.pem -keyout stunnel.key
Move the stunnel.key and stunnel.pem files into your stunnel configuration directory.
- Create a stunnel configuration file, for example, stunnel.conf,
with the following
contents:
debug = info socket = l:TCP_NODELAY=1 socket = r:TCP_NODELAY=1 debug = 7 cert = /PATH/TO/stunnel.pem key = /PATH/TO/stunnel.key [pg-server] client=no accept = 0.0.0.0:5433 # This is the SSL listening port connect = PGHOST:PGPORT # This is the PgBouncer listen port protocol = pgsql
See the stunnel configuration file reference for additional configuration options.
- Run stunnel on the
server:
stunnel /path/to/stunnel-srv.conf
- On the client, connect to stunnel on its host and accept port. For
example:
psql -h pgbouncer-host -p stunnel-accept-port database-name