Configure and use PgBouncer

Andrey Aksenov

PgBouncer is a lightweight connection pooler that manages database connection pools for PostgreSQL and Greengage DB.

This guide describes how to set up and use PgBouncer with Greengage DB. For information on using PgBouncer with PostgreSQL, see the PgBouncer website.

Security note

The examples in this topic are for demonstration purposes only. They use hardcoded passwords, encryption keys, and other sensitive information for clarity.

Do not use hardcoded secrets in production. In production environments, always follow secure practices:

Store secrets in dedicated secret management systems (for example, HashiCorp Vault).
Use environment variables or secured configuration files with restricted access.
Implement proper key rotation and strong password policies.
Ensure private keys are never committed to version control or embedded in application code.

Following these practices helps protect sensitive data and prevent accidental exposure.

A database connection pool caches database connections to eliminate the overhead of creating new ones. This improves connection speed and reduces server load.

PgBouncer maintains separate connection pools for each database/user combination and reuses connections whenever possible. When a client disconnects, its connection returns to the pool for reuse.

PgBouncer supports three pooling modes:

Session pooling — assigns a server connection for the entire client session (default).
Transaction pooling — assigns a connection only during a transaction, returning it to the pool afterward.
Statement pooling — returns the connection immediately after a query; multi-statement transactions are disallowed.

You can set a default pool mode globally and override it on a per-database or per-user basis.

PgBouncer supports the standard PostgreSQL/Greengage DB connection interface. Clients connect to PgBouncer’s host and port rather than directly to the master.

PgBouncer provides a psql-like administration console for monitoring, managing, and reloading configuration at runtime.

You can run PgBouncer either on the Greengage DB master host or on a separate server. Running PgBouncer on a separate host can help simplify failover — you can redirect clients to a standby master by updating the PgBouncer configuration and reloading it through the administration console.

The following hosts are used to illustrate the integration of a Greengage DB cluster with PgBouncer:

PgBouncer host: FQDN — pgbouncer.example.com, IP — 192.168.1.5.
Master host: FQDN — mdw.example.com, IP — 192.168.1.10.
Client host: FQDN — client.example.com, IP — 192.168.1.155.
Directory server host (FreeIPA): name — ipa.example.com, FQDN — ipa.example.com.

Software and setup notes:

Ensure general DBMS initialization steps are completed (see Initialize DBMS).
The PgBouncer host must have git and make installed.

Before using PgBouncer, database roles must be created for administrative and client access. In this example, a superuser role (dba) and a regular user role (alice) are created.

Create the dba role using createuser:

$ createuser --pwprompt --superuser --echo dba

When prompted, enter a password for the new role. Example output:

Enter password for new role:
Enter it again:
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE dba PASSWORD 'md556f6af8d612ee0840a32c255091f41a8' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

You will use the MD5 hash shown in the CREATE ROLE statement later in the PgBouncer authentication file.

Create the alice role:

$ createuser alice

You will use this role later to demonstrate how to set up LDAP authentication.

To install PgBouncer from source, follow these steps:

Clone the Greengage DB repository, including all submodules:

$ git clone --recurse-submodules https://github.com/GreengageDB/greengage.git

Change to the greengage directory:
```
$ cd greengage
```
Check out the required tag, for example:
```
$ git checkout 6.30.1
```

Update submodules:

$ git submodule update --init --recursive

Enter the PgBouncer source directory:
```
$ cd gpAux/extensions/pgbouncer/source
```
Prepare the build system with autogen.sh:
```
$ ./autogen.sh
```
Configure the build. The --with-ldap option adds LDAP support:
```
$ ./configure --prefix=/usr/local --with-ldap
```
Compile PgBouncer:
```
$ make -j$(nproc)
```

Install PgBouncer:

$ sudo make install

Example output:

INSTALL  pgbouncer /usr/local/bin
INSTALL  README.md /usr/local/share/doc/pgbouncer
INSTALL  NEWS.md /usr/local/share/doc/pgbouncer
INSTALL  etc/pgbouncer-minimal.ini /usr/local/share/doc/pgbouncer
INSTALL  etc/pgbouncer.ini /usr/local/share/doc/pgbouncer
INSTALL  etc/pgbouncer.service /usr/local/share/doc/pgbouncer
INSTALL  etc/pgbouncer.socket /usr/local/share/doc/pgbouncer
INSTALL  etc/userlist.txt /usr/local/share/doc/pgbouncer

For additional installation options and details, see PgBouncer documentation.

PgBouncer is configured through a configuration file, typically named pgbouncer.ini. This file defines the databases PgBouncer can access, along with connection pool parameters and authentication settings.

This section assumes pgbouncer.ini is located in the home directory. Navigate to it:

$ cd

Create the pgbouncer.ini file:

$ vi pgbouncer.ini

Add the following lines to the file:

[databases]
postgres = host=192.168.1.10 port=5432 dbname=postgres

[pgbouncer]
pool_mode = session
listen_addr = 192.168.1.5
listen_port = 6432
auth_type = md5
auth_file = userlist.txt
admin_users = dba
logfile = pgbouncer.log
pidfile = pgbouncer.pid

where:

[databases]

Maps client-facing database names (keys) to their actual connection details (values) using libpq-style connection strings:

host — the hostname or IP address of the master host.
port — the master port.
dbname — the destination database name.

[pgbouncer]

pool_mode: Determines how client connections are pooled (see Overview). Possible values are session, transaction, and statement.
listen_addr/listen_port: The IP address and port where PgBouncer listens for incoming client connections.
auth_type: The authentication method. md5 requires a password hashed with MD5.
auth_file: The file containing users and their passwords; works in conjunction with auth_type.
admin_users: A comma-separated list of users allowed to execute administrative commands.
logfile/pidfile: The paths to the PgBouncer log and PID files.

PgBouncer requires a user authentication file specified by the auth_file option in pgbouncer.ini. This is a plain text file with one user per line in one of the following formats:

"<username1>" "<password>"
"<username2>" "md5<encoded_password>"
"<username3>" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"

Each line contains two fields enclosed in double quotes (" "):

The first field is the Greengage DB role name.
The second field is either a plain-text password, an MD5-encoded password, or a SCRAM secret.

Create the userlist.txt file:

$ vi userlist.txt

Add the following line:

"dba" "md556f6af8d612ee0840a32c255091f41a8"

The MD5 hash is the value returned by the CREATE ROLE statement when the dba role is created (see Create database roles). This entry allows the dba user to authenticate through PgBouncer using the specified MD5 password hash. Note that the password hash in this example is shown for demonstration purposes only.

Alternatively, you can configure PgBouncer to use host-based authentication (HBA) instead of a static authentication file. When you set auth_type = hba in pgbouncer.ini, PgBouncer reads authentication rules from the file specified by the auth_hba_file parameter, using the same format as the pg_hba.conf file.

To allow PgBouncer to connect to the Greengage DB master, add an entry for the PgBouncer host in the pg_hba.conf file on the master host.

Open pg_hba.conf for editing:

$ vi $MASTER_DATA_DIRECTORY/pg_hba.conf

Add the following line:

# connection-type  database  user  address         auth-method
host               postgres  all   192.168.1.5/32  trust

This entry allows connections to the postgres database from the PgBouncer host (192.168.1.5) for all users.

After updating the file, reload the Greengage DB configuration:

$ gpstop -u

Use the pgbouncer command to start PgBouncer with the configuration file you created:

$ pgbouncer -d pgbouncer.ini

The -d option runs PgBouncer as a background (daemon) process. The pgbouncer.ini file specifies the PgBouncer configuration.

You can now connect to the database through PgBouncer under the dba user. Specify the PgBouncer host and port when running the command from the client host:

$ psql postgres -U dba -h 192.168.1.5 -p 6432

Enter the password when prompted:

Password for user dba:

psql displays a prompt similar to the following:

psql (9.4.26)
Type "help" for help.

postgres=#

PgBouncer provides an administration console similar to psql. To access it, connect to the special virtual database named pgbouncer on the PgBouncer port. The console accepts SQL-like commands for monitoring, managing, and reconfiguring PgBouncer.

Use psql to connect to the pgbouncer virtual database:

$ psql pgbouncer -U dba -p 6432

The username must be listed in the admin_users parameter in pgbouncer.ini. Alternatively, you can log in using your current Unix username if the PgBouncer process runs under that UID.

Enter the password. You should see the PgBouncer prompt:

pgbouncer=#

To view the available PgBouncer administration console commands, run:

SHOW HELP;

Example output:

NOTICE:  Console usage
DETAIL:
        SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
        SHOW PEERS|PEER_POOLS
        SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM|STATE
        SHOW DNS_HOSTS|DNS_ZONES
        SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
        SET key = arg
        RELOAD
        PAUSE [<db>]
        RESUME [<db>]
        DISABLE <db>
        ENABLE <db>
        RECONNECT [<db>]
        KILL <db>
        SUSPEND
        SHUTDOWN
        WAIT_CLOSE [<db>]

Use the administration console to view the current runtime status of servers and clients. These commands help you monitor connections, resource usage, and client activity.

The SHOW SERVERS command displays information about server-side connections from PgBouncer to database backends, including the connection state, database, and user:

SHOW SERVERS;

Example output:

type | user | database | state | addr         | port | local_addr  | local_port | connect_time            | request_time            | wait | wait_us | close_needed | ptr            | link | remote_pid | tls | application_name | prepared_statements
-----+------+----------+-------+--------------+------+-------------+------------+-------------------------+-------------------------+------+---------+--------------+----------------+------+------------+-----+------------------+--------------------
S    | dba  | postgres | used  | 192.168.1.10 | 5432 | 192.168.1.5 | 56126      | 2026-03-02 12:29:16 UTC | 2026-03-02 12:29:16 UTC | 0    | 0       | 0            | 0x556149d6fd20 |      | 3846       |     |                  | 0

In this example, 192.168.1.10 is the IP address of the Greengage DB master server.

The SHOW CLIENTS command displays information about active client connections to PgBouncer, including connection state, addresses, ports, and prepared statements:

SHOW CLIENTS;

Example output:

type | user | database  | state  | addr          | port  | local_addr  | local_port | connect_time            | request_time            | wait | wait_us | close_needed | ptr            | link | remote_pid | tls | application_name | prepared_statements
-----+------+-----------+--------+---------------+-------+-------------+------------+-------------------------+-------------------------+------+---------+--------------+----------------+------+------------+-----+------------------+--------------------
C    | dba  | pgbouncer | active | unix          | 6432  | unix        | 6432       | 2026-03-02 12:36:15 UTC | 2026-03-02 12:36:16 UTC | 0    | 0       | 0            | 0x556149d12cd0 |      | 4072       |     | psql             | 0
C    | dba  | postgres  | active | 192.168.1.155 | 14148 | 192.168.1.5 | 6432       | 2026-03-02 12:29:16 UTC | 2026-03-02 12:29:16 UTC | 0    | 0       | 0            | 0x556149d13380 |      | 0          |     | psql             | 0

In this example:

The first row shows the dba user connected via a local Unix domain socket (addr is unix) to the pgbouncer virtual database.
The second row shows the dba user connected remotely via TCP from client IP 192.168.1.155 to the postgres database.

After editing pgbouncer.ini, you can apply changes without restarting PgBouncer by using the RELOAD command in the administration console. This reloads the configuration and applies updates to databases, users, or connection settings:

RELOAD;

PgBouncer supports LDAP authentication, which allows you to authenticate users against your organization’s directory service (such as Active Directory or 389 Directory Server) before they connect to the Greengage DB cluster.

This section demonstrates how to integrate PgBouncer with a directory service provided by FreeIPA.

See LDAP authentication for detailed prerequisites and configuration steps.

To use LDAP authentication, enable host-based authentication in PgBouncer.

Edit pgbouncer.ini:

$ vi pgbouncer.ini

Change the configuration as follows:

# ...
auth_type = hba
auth_hba_file = hba_bouncer.conf
# ...

Next, create the hba_bouncer.conf configuration file in the same directory as pgbouncer.ini:

$ vi hba_bouncer.conf

To preserve local access for the dba user without LDAP authentication, add the following entry:

# connection-type  database  user     auth-method
local              all       dba      trust

To enable LDAP authentication for a specific user without a bind DN, edit hba_bouncer.conf:

$ vi hba_bouncer.conf

Add an entry like:

# connection-type  database  user   address           auth-method  auth-options
host               postgres  alice  192.168.1.155/32  ldap         ldapserver=ipa.example.com ldapprefix="uid=" ldapsuffix=",cn=users,cn=accounts,dc=example,dc=com"

Reload the PgBouncer configuration as described in Reload PgBouncer configuration. Then, test the connection from the client host:

$ psql postgres -U alice -h 192.168.1.5 -p 6432

Enter the LDAP user password:

Password for user alice:

Result:

psql (9.4.26)
Type "help" for help.

postgres=>

LDAP servers might require a bind DN (a privileged account) to perform user lookups and authentication. Configure PgBouncer to use a bind DN and password when connecting to the LDAP server.

Edit hba_bouncer.conf and add an entry like this:

# connection-type  database  user   address           auth-method  auth-options
host               postgres  alice  192.168.1.155/32  ldap         ldapserver=ipa.example.com ldapbasedn="cn=users,cn=accounts,dc=example,dc=com" ldapbinddn="uid=admin,cn=users,cn=accounts,dc=example,dc=com" ldapbindpasswd="12345678"

In this example, the ldapbindpasswd value is shown in plain text. In production, prefer encrypted bind credentials.

To apply the changes, reload the PgBouncer configuration as described in Reload PgBouncer configuration.

PgBouncer supports encrypted bind credentials to avoid storing plain-text LDAP passwords. This section demonstrates how to generate an encryption key, encrypt the LDAP password, and configure PgBouncer to use it.

Generate a key file named ldkeyfile to encrypt the LDAP password:

$ openssl rand -base64 256 | tr -d '\n' > ldkeyfile

Encrypt the LDAP password (12345678 in this example) using the key file:

$ ENCRYPTED_PASSWD=$(echo -n "12345678" | openssl enc -aes-256-cbc -base64 -md sha256 -pass file:ldkeyfile)

Save the encrypted password to the .ldapbindpass file in your home directory:

$ echo -n $ENCRYPTED_PASSWD > "${HOME}/.ldapbindpass"

Edit pgbouncer.ini to specify the key file location and cipher:

# ...
auth_key_file = ldkeyfile
auth_cipher = aes-256-cbc
# ...

Update your HBA configuration (hba_bouncer.conf) to use the encrypted password. Set ldapbindpasswd to "$bindpasswd":

# connection-type  database  user   address           auth-method  auth-options
host               postgres  alice  192.168.1.155/32  ldap         ldapserver=ipa.example.com ldapbasedn="cn=users,cn=accounts,dc=example,dc=com" ldapbinddn="uid=admin,cn=users,cn=accounts,dc=example,dc=com" ldapbindpasswd="$bindpasswd"

To apply the changes, reload the PgBouncer configuration as described in Reload PgBouncer configuration.

PgBouncer supports SSL encryption for both client-to-PgBouncer and PgBouncer-to-DBMS connections, ensuring that credentials and query traffic are transmitted securely.

Before configuring SSL for PgBouncer, enable SSL on Greengage DBMS as described in Encryption of database connections.

Then edit pg_hba.conf to allow SSL connections from PgBouncer:

# connection-type  database  user   address         auth-method
hostssl            postgres  all    192.168.1.5/32  trust

Restart the cluster to apply the changes.

Generate a server certificate for the PgBouncer host as described in Generate certificates, using the same root CA.

Edit pgbouncer.ini to configure SSL for server connections:

# ...
server_tls_sslmode = verify-full
server_tls_ca_file = certs/root.crt
server_tls_cert_file = certs/server.crt
server_tls_key_file = certs/server.key
# ...

Reload the PgBouncer configuration as described in Reload PgBouncer configuration.

Connect as alice from the client host:

$ psql postgres -U alice -h 192.168.1.5 -p 6432

On the PgBouncer host, check the server connections:

\x
SHOW SERVERS;

Verify the tls field in the output:

-[ RECORD 1 ]-------+-----------------------------------------------
type                | S
user                | alice
database            | postgres
state               | active
addr                | 192.168.1.10
port                | 5432
local_addr          | 192.168.1.5
local_port          | 40084
connect_time        | 2026-03-03 07:49:09 UTC
request_time        | 2026-03-03 07:49:14 UTC
wait                | 0
wait_us             | 0
close_needed        | 0
ptr                 | 0x561630bc5830
link                | 0x561630bb0930
remote_pid          | 17464
tls                 | TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
application_name    | psql
prepared_statements | 0

Edit pgbouncer.ini to enable SSL for client connections:

# ...
client_tls_sslmode = require
client_tls_ca_file = certs/root.crt
client_tls_cert_file = certs/server.crt
client_tls_key_file = certs/server.key
# ...

Reload the PgBouncer configuration as described in Reload PgBouncer configuration.

Connect as alice from the client host:

$ psql postgres -U alice -h 192.168.1.5 -p 6432

Confirm SSL is in use:

psql (9.4.26)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

Logging

Start and stop

Configure and use PgBouncer

Overview

Prerequisites

Prepare hosts

Create database roles

Install PgBouncer

Clone the Greengage DB repository

Build PgBouncer from sources

Set up PgBouncer

Create the PgBouncer configuration file

Create the PgBouncer authentication file

Allow PgBouncer connections in pg_hba.conf

Start PgBouncer

Connect via PgBouncer

Manage PgBouncer

Connect to the PgBouncer administration console

View PgBouncer runtime status

Reload PgBouncer configuration

Configure LDAP authentication

Prerequisites for LDAP authentication

Enable host-based authentication in PgBouncer

LDAP authentication without bind DN (anonymous bind)

LDAP authentication with bind DN

Secure LDAP bind credentials

Generate an encryption key

Configure encrypted LDAP bind credentials

Configure SSL encryption

Enable SSL on the DBMS

Encrypt PgBouncer ↔ DBMS connections

Encrypt client ↔ PgBouncer connections