Using the PgBouncer Connection Pooler

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.

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.

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.

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.

  1. Create a PgBouncer configuration file, for example pgbouncer.ini. Here is a simple configuration file:
    [databases]
    template1 = host=127.0.0.1 port=5432 dbname=template1
    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; passw=admin1234; echo -n md5; echo -n $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 template1

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.

A reset query can be specified by setting the server_reset_query parameter in the PgBouncer configuration file. For Greenplum Database with PgBouncer in session pooling mode, the server_reset_query parameter can be set to this query:
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.

  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;

To map clients to server connections, use the SHOW CLIENTS and SHOW SERVERS views:

  1. Use ptr and link to map the local client connection to the server connection.
  2. Use addr and 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.

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.

Before you begin, you must have an LDAP or Active Directory server and a Greenplum Database cluster.
  1. Create Greenplum Database users in the LDAP/AD server, for example gpdbuser1 and gpdbuser2.
  2. Create the corresponding Greenplum Database users in the database:
    createuser gpdbuser1
    createuser gpdbuser2
  3. 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
    
  4. 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
    
  5. 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"
  6. 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://stunnel.org. 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://stunnel.org and http://openssl.org for the latest versions.

  1. 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
  2. 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 ..
  3. 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.

  1. On the server running PgBouncer, create a directory for stunnel configuration files, for example /home/gpadmin/stunnel.
  2. 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.

  3. 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.

  4. Run stunnel on the server:
    stunnel /path/to/stunnel-srv.conf
  5. On the client, connect to stunnel on its host and accept port. For example:
    psql -h pgbouncer-host -p stunnel-accept-port database-name