PgBouncer Guide

EDB Postgres™ PgBouncer Guide

Sep 11, 2020

Contents

1 Installation 2
1.1 Installing PgBouncer on a CentOS Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2 Installing PgBouncer on a Debian or Ubuntu Host . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.3 Installing PgBouncer on a Windows Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.4 Installing PgBouncer on an SLES 12 Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

2 Configuration and Usage 19

2.1 Configuring PgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2 Using the PgBouncer Admin Console . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

3 Uninstallation 24
3.1 Uninstalling PgBouncer on a CentOS Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
3.2 Uninstalling PgBouncer on a Debian or Ubuntu Host . . . . . . . . . . . . . . . . . . . . . . . . . . 25
3.3 Uninstalling PgBouncer on an SLES 12 Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
3.4 Uninstalling PgBouncer on a Windows Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Index 28

i
PgBouncer Guide

When a client application connects to a Postgres server, it negotiates a connection; that negotiation takes time. Pg-
Bouncer saves time by maintaining a pool of pre-established connections to the server. Instead of connecting directly
to the server, the client connects to PgBouncer, minimizing the connection negotiation time by using a previously
established connection made available to the client in the PgBouncer pool.
PgBouncer is a lightweight connection pooling utility for Postgres and Advanced Server installations that is based on
the openSource PgBouncer project.
EnterpriseDB enhancements for the PgBouncer project are available via RPM Packages, deb packages, or a graphical
installer that you can download with StackBuilder Plus. The enhancements allow PgBouncer to service clients that are
using EDB Connectors that require compatible out parameter handling.
For more information about PgBouncer, including reference and usage information, please visit the PgBouncer project
site.

Contents 1
CHAPTER 1

Installation

This section walks you through installing PgBouncer on a CentOS, Debian/Ubuntu, or a Windows host.
The following table lists the latest PgBouncer versions, their supported corresponding Advanced Server versions, and
the supported platforms for each PgBouncer version.
The PgBouncer version required by your Advanced Server installation is version-specific, but the documented and
supported functionality of each version is the same. The information in this guide applies to each version listed in the
table below.

2
PgBouncer Guide

PgBouncer Version Supported Advanced Supported Platforms

Server Version
PgBouncer 1.14.0.1 Advanced Server 12
RHEL 6, RHEL 7, and RHEL 8 - x86_64
RHEL 7 - ppc64le
Debian 9x Stretch
Ubuntu 18.04 Bionic Beaver
Windows 86 x64 Interactive Installer
SUSE Linux Enterprise Server (SLES) 12 SP4

PgBouncer 1.13.0.1 Advanced Server 12

RHEL 6, RHEL 7, and RHEL 8 - x86_64
RHEL 7 - ppc64le
Debian 9x Stretch
Ubuntu 18.04 Bionic Beaver
Windows 86 x64 Interactive Installer

PgBouncer 1.12.0.1 Advanced Server 12

RHEL 6, RHEL 7, and RHEL 8 - x86_64
RHEL 7 - ppc64le
Debian 9x Stretch
Ubuntu 18.04 Bionic Beaver
Windows 86 x64 Interactive Installer

PgBouncer 1.9.0.1 Advanced Server 11

RHEL 6 and RHEL 7 - x86_64
RHEL 7 - ppc64le
Debian 9x Stretch
Ubuntu 18.04 Bionic Beaver
Windows 86 x64 Interactive Installer
Linux 86_64 Interactive Installer

PgBouncer 1.7.2.1-1 Advanced Server 10, 9.6,

and 9.5
RHEL 6 and RHEL 7 - x86_64
RHEL 7 - ppc64le
Linux 86_64 Interactive Installer
Windows 86 x64 Interactive Installer

3
PgBouncer Guide

1.1 Installing PgBouncer on a CentOS Host

If you have previously used an RPM package to install Advanced Server, you have probably already created the
repository configuration file and have EnterpriseDB credentials. If that is the case, you can install PgBouncer with the
following command:
On RHEL/CentOS 6 and 7:

yum -y install edb-pgbouncer<xx>

Where <xx> is the PgBouncer version you want to install.

For example, to install PgBouncer 1.14.0.1 on a CentOS/RHEL 6 and 7 host, the command is:

yum -y install edb-pgbouncer114

On a RHEL/CentOS 8 host, you can use the dnf package manager to install PgBouncer:

dnf - y install edb-pgbouncer<xx>

Where <xx> is the PgBouncer version you want to install.

Similarly, to install PgBouncer 1.14.0.1 on a CentOS/RHEL 8 host, the command is:

dnf -y install edb-pgbouncer114

To create the repository file, follow the steps given below.

Creating a Repository Configuration File to Install PgBouncer
Before creating the repository configuration file, you must have credentials that allow access to the EnterpriseDB
repository. For information about requesting credentials, see the EnterpriseDB repository instructions.
1. To create the repository configuration file, assume superuser privileges, and invoke one of the following
platform-specific commands:
On RHEL/CentOS 6 and 7:

yum -y install https://yum.enterprisedb.com/edb-repo-rpms/edb-repo-latest.noarch.rpm

On CentOS/RHEL 8:

dnf -y install https://yum.enterprisedb.com/edb-repo-rpms/edb-repo-latest.noarch.rpm

The repository configuration file is named edb.repo, which resides in /etc/yum.repos.d.

2. After creating the edb.repo file, use your choice of editor to open this file and ensure that the value of the
enabled parameter is 1, and the username and password placeholders in the baseurl specification are
replaced with the name and password of a registered EnterpriseDB user.

[edb]
name=EnterpriseDB RPMs $releasever - $basearch
baseurl=https://<username>:<password>@yum.enterprisedb.com/edb/redhat/
˓→rhel-$releasever-$basearch

enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/ENTERPRISEDB-GPG-KEY

3. Save your changes to the configuration file and exit the editor.

1.1. Installing PgBouncer on a CentOS Host 4

PgBouncer Guide

4. Execute the following commands to update the metadata:

yum clean all

yum makecache

5. Before installing PgBouncer, execute the following command to install the Extra Packages for Enterprise Linux
(EPEL) release package:
On RHEL/CentOS 6 and 7:

yum -y install epel-release

On RHEL/CentOS 8:

dnf -y install epel-release

Note: You may need to enable the [extras] repository definition in the CentOS-Base.repo file (located in
/etc/yum.repos.d).

6. Use the following platform-specific command to install PgBouncer.

On RHEL/CentOS 6 and 7:

yum -y install edb-pgbouncer<xx>

Where <xx> is the PgBouncer version you want to install.

For example, the following command installs PgBouncer 1.14.0.1 on RHEL/CentOS 6 and 7:

yum -y install edb-pgbouncer114

On RHEL/CentOS 8:

dnf -y install edb-pgbouncer<xx>

Where <xx> is the PgBouncer version you want to install.

Similarly, to install PgBouncer 1.14.0.1 on CentOS/RHEL 8, the command is:

dnf -y install edb-pgbouncer114

When you install an RPM package that is signed by a source that is not recognized by your system, yum may ask
for your permission to import the key to your local server. If prompted, and you are satisfied that the packages
come from a trustworthy source, enter y, and press Return to continue.
During the installation, yum may encounter a dependency that it cannot resolve. If it does, it will provide a list
of the required dependencies that you must manually resolve. PgBouncer will be installed in the /usr/edb/
pgbouncer<x.x> directory.

1.1. Installing PgBouncer on a CentOS Host 5

PgBouncer Guide

1.2 Installing PgBouncer on a Debian or Ubuntu Host

You must install Advanced Server before installing PgBouncer. For details about installing and configuring Advanced
Server, see the EDB Postgres Advanced Server Installation Guide available at the EDB website. To install a package
on a Debian or Ubuntu host, you must have credentials to access the EnterpriseDB repository. See the EDB repository
access instructions to request the EnterpriseDB credentials.
The following steps will walk you through using the EnterpriseDB repository to install a DEB package. When using
the commands, replace the username and password with the credentials provided by EnterpriseDB.
Please note that if you are using the pdf version of this document, using cut/paste to copy command may result in extra
spaces or carriage returns in the pasted command. If a command fails, check the command carefully for additional
characters.
1. Assume superuser privileges:

sudo su -

2. Configure the EnterpriseDB repository:

sh -c 'echo "deb https://<username>:<password>@apt.enterprisedb.com/$(lsb_

˓→release -cs)-edb $(lsb_release -cs) main" > /etc/apt/sources.list.d/edb-

˓→$(lsb_release -cs).list'

3. Add support to your system for secure APT repositories:

apt-get install apt-transport-https

4. Add the EDB signing key:

wget -q -O - https://<username>:<password>@apt.enterprisedb.com/edb-deb.
˓→gpg.key | apt-key add -

5. Update the repository metadata:

apt-get update

6. Install DEB package:

apt-get install edb-pgbouncer<xx>

Where <xx> is the PgBouncer version you want to install.

For example, to install the PgBouncer 1.14.0.1 package for Advanced Server 12, execute the follow-
ing command:

apt-get install edb-pgbouncer114

PgBouncer will be installed in the /usr/edb/pgbouncer<x.x> directory.

Where <x.x> is the PgBouncer version you have installed.

1.2. Installing PgBouncer on a Debian or Ubuntu Host 6

PgBouncer Guide

1.3 Installing PgBouncer on a Windows Host

Graphical installers for PgBouncer are available via StackBuilder Plus (for Advanced Server hosts) or Stack Builder
(for PostgreSQL hosts).
The following steps walk you through installing PgBouncer 1.14.0.1 by accessing StackBuilder Plus through Windows
start menu
1. Access StackBuilder Plus through the Windows start menu; on the Welcome window, select your Advanced
Server installation from the drop-down list. Click Next to continue to the application selection page.

Fig. 1: The StackBuilder Plus Welcome window

1.3. Installing PgBouncer on a Windows Host 7

PgBouncer Guide

2. Expand the Add-ons, tools and utilities node, and check the box next to the PgBouncer v1.
14.0.1-1. Click Next to continue.

Fig. 2: The PgBouncer Version Selection window

1.3. Installing PgBouncer on a Windows Host 8

PgBouncer Guide

3. The selected packages and the default download directory where the package will be installed are displayed;
change the download directory location if required. Click Next.

Fig. 3: The Selected Packages window

1.3. Installing PgBouncer on a Windows Host 9

PgBouncer Guide

4. Once you have downloaded the installation files, a confirmation message is displayed. Click Next to start the
PgBouncer installation.

Fig. 4: Installing PgBouncer

1.3. Installing PgBouncer on a Windows Host 10

PgBouncer Guide

5. Select an installation language and click OK.

Fig. 5: The Language Selection window

1.3. Installing PgBouncer on a Windows Host 11

PgBouncer Guide

6. The installer welcomes you to the setup wizard. Click Next.

Fig. 6: The PgBouncer Welcome window

1.3. Installing PgBouncer on a Windows Host 12

PgBouncer Guide

7. Use the Installation Directory field to specify the directory in which you wish to install PgBouncer
(the default installation directory is C:\Program Files\edb) Then, click Next to continue.

Fig. 7: The Installation Directory window

1.3. Installing PgBouncer on a Windows Host 13

PgBouncer Guide

8. Use fields on the EDB Postgres Advanced Server Installation Details window to provide
connection information for the Advanced Server host:
• Use the Host field to identify the system on which Advanced Server resides.
• Provide the name of the role that PgBouncer will use for connections to the server in the User
Name field.
• Provide the password associated with the role in the Password field.
• Use the Port field to identify the listener port that Advanced Server monitors for client connections.
Then, click Next to continue.

Fig. 8: The Advanced Server Installation Details window

1.3. Installing PgBouncer on a Windows Host 14

PgBouncer Guide

9. Use fields on the PgBouncer Configurations window to provide your preferences for the PgBouncer
installation:
• Use the Listening Port field to specify the port that PgBouncer monitors for connections.
• Use the Operating System User field to specify the name of the Linux operating system user that Pg-
Bouncer will change to after startup. This option is not supported on Windows hosts.

Fig. 9: The PgBouncer Configuration window

1.3. Installing PgBouncer on a Windows Host 15

PgBouncer Guide

10. The Ready to Install window notifies you when the installer has all of the information needed to install
PgBouncer on your system. Click Next to install PgBouncer. Progress bars inform you as the installation
progresses.

Fig. 10: The Ready to Install window

1.3. Installing PgBouncer on a Windows Host 16

PgBouncer Guide

11. The installer notifies you when the setup wizard has completed the installation. Click Finish to exit the
installer.

Fig. 11: The installation is complete

1.3. Installing PgBouncer on a Windows Host 17

PgBouncer Guide

1.4 Installing PgBouncer on an SLES 12 Host

Perform the following steps to install PgBouncer on a SLES 12 SP4 host using the zypper package manager.
1. Assume superuser privileges and use the following command to add the EnterpriseDB repository configuration
file to your SLES host:

zypper addrepo https://zypp.enterprisedb.com/suse/edb-sles.repo

This command creates a repository configuration file named edb.repo in the /etc/zypp/repos.d direc-
tory.
2. Invoke the following command to refresh the repository.

zypper refresh

3. Use the zypper utility to install PgBouncer.

zypper install edb-pgbouncer<xx>

Where <xx> is the PgBouncer version you have installed.

For example, to install PgBouncer 1.14.0.1, invoke the following command:

zypper install edb-pgbouncer114

Note: Currently version 1.14.0.1 is supported on SLES 12 SP4.

PgBouncer will be installed in the /usr/edb/pgbouncer<xx> directory.

1.4. Installing PgBouncer on an SLES 12 Host 18

CHAPTER 2

Configuration and Usage

This section walks you through how to configure and use PgBouncer.

2.1 Configuring PgBouncer

When the PgBouncer service is running, any Postgres client connecting to the PgBouncer listener port specified in the
configuration file will use connection pooling.
PgBouncer connection and configuration information is stored in the .ini file, located in the following directory:
On Linux:
/etc/edb/pgbouncer<x.x>/edb-pgbouncer-<x.x>.ini
Where <x.x> is the PgBouncer version.
On Debian:
/etc/edb/pgbouncer<x.x>/pgbouncer.ini
Where <x.x> is the PgBouncer version.
On SLES 12 SP4:
/etc/edb/pgbouncer1.14/edb-pgbouncer-<x.x>.ini
Where <x.x> is the PgBouncer version.
On Windows:
C:\Program Files\edb\pgbouncer<x.x>\share\pgbouncer.ini
Where <x.x> is the PgBouncer version.
The PgBouncer configuration file is divided into two sections: [databases] and [pgbouncer].
The [databases] section of the configuration file contains a list of databases and the associated connection infor-
mation. In an Advanced Server installation, the configuration file contains an entry for the installation of Advanced
Server that installed PgBouncer:

19
PgBouncer Guide

edb = host=127.0.0.1 port=5444

You can specify additional database connection information in the configuration file in the form of keyword=value
pairs. You can include the following parameters:

Parameter Description
name The name of the database to which the client application will connect.
host The IP address of the host.
port The port on which the host is listening.
dbname The (optional) database name.
user A username (if different than the information specified by the connecting client ).
password A password (if different than the information specified by the connecting client).

The following example demonstrates the syntax allowed in the [databases] section of the configuration file:

[databases]
edb = host=127.0.0.1 port=5444
acctg = host=192.168.10.101 port=5432 user=bob password=XXXXXX

Include the dbname parameter to map the connection name to an alternate database name. For example:
hr = host=127.0.0.1 port=5445 dbname=humanresources
When the client provides authentication information, that information is used to connect to PgBouncer, which in
turn uses the information specified in the PgBouncer configuration file to connect to the database server. The user
information provided in the configuration file must match a role defined in the Postgres database cluster.

Note: If you do not specify user details in pgbouncer.ini, the username and password will be authenticated by
the database server and PgBouncer. As such, the username and password should be included in the userlist.txt
file and the database cluster.

The [pgbouncer] section of the configuration file contains configuration details specific to PgBouncer:

2.1. Configuring PgBouncer 20

PgBouncer Guide

Parameter Description
admin_users A comma-delimited list of users that are allowed to access the Ad-
min Console (for management and monitoring purposes). By de-
fault, PgBouncer is installed with an admin_users = enterprisedb.
auth_file The path to the authentication file that contains username and pass-
words of clients that may connect to PgBouncer. The authenti-
cation file (userlist.txt) is located in /opt/edb/pgbouncer-<x.x>/etc,
and contains username/password pairs that specify the identities
that clients may use to access PgBouncer. Within the authentication
file, the username and password must be specified within double-
quotes, as shown below:
“user_name” “password”
To make changes to the identities that can access PgBouncer, you
can edit the existing authentication file, or specify an alternate au-
thentication file with the auth_file parameter.
auth_type
auth_type The authentication method used by PgBouncer. May be: md5,
crypt, plain, trust or any. The default value is md5.
default_pool_size The amount of user connections that are allowed to access the
server. The default is 20 active connections.
group_connections Clients providing the same application_name will be grouped to use
the same connection. The default is 0.
ignore_startup_parameters A comma-delimited list of application startup packets that Pg-
Bouncer should ignore. The default is application_name .
listen_addr The IP address on which PgBouncer listens for client connections.
If omitted, only Unix socket connections are allowed; the client
must also reside on the same host as PgBouncer and may not specify
a host IP address when connecting to PgBouncer.
listen_port The port that PgBouncer monitors for client connections. By de-
fault, PgBouncer listens on port 6432.
logfile The path to the PgBouncer log file.
max_client_conn The maximum number of connections allowed. The default is 100.
pidfile The path to the process ID file.
pool_mode The value of pool_mode specifies when the server connection can be
made available to the connection pool. May be: session, transaction
or statement. The default value is session.
server_reset_query The default is DISCARD ALL which instructs PgBouncer to clean
any changes made to a database session.
stats_users A comma delimited list of users who are allowed to connect and run
read-only queries. The default is stats_users = enterprisedb.

2.1. Configuring PgBouncer 21

PgBouncer Guide

The following example demonstrates the syntax allowed in the [pgbouncer] section of the configuration file for
edb-pgbouncer-1.14 version:

[pgbouncer]
logfile = /var/log/edb/pgbouncer1.14/edb-pgbouncer-1.14.log
pidfile = /var/run/edb/pgbouncer1.14/edb-pgbouncer-1.14.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /opt/edb/pgbouncer-1.14/etc/userlist.txt
admin_users = enterprisedb
stats_users = enterprisedb
pool_mode = session
server_reset_query = DISCARD ALL
ignore_startup_parameters = application_name
max_client_conn = 100
default_pool_size = 20
group_connections = 0

For more information about the settings used in the pgbouncer.ini file, click here.
After editing the PgBouncer configuration file to reflect your environment, you must restart the PgBouncer service for
the changes to take effect. The name of the PgBouncer service is edb-pgbouncer-1.14; use platform specific
commands to stop, start, or restart the service as needed.

2.2 Using the PgBouncer Admin Console

The Admin Console allows you to retrieve statistical information about PgBouncer activity, and to control the Pg-
Bouncer process. You can use the edb-psql client to access the PgBouncer Admin Console by connecting to the
pgbouncer database. The following example connects to the pgbouncer database with the edb-psql client on a
Linux system. PgBouncer is listening on port 6432, with a user name of enterprisedb:
Enter following command after navigating to the bin directory under your Advanced Server installation:
# ./edb-psql -p 6432 -U enterprisedb pgbouncer
Please note that the required connection information will vary according to the connecting client, platform and authen-
tication information required by the server.
After connecting to the pgbouncer database, you can use the SHOW CLIENTS command to retrieve client-related
information:
# SHOW CLIENTS;
The SHOW CLIENTS command returns:

--[ RECORD 1]-+--------------------

type | C
user | postgres
database | pgbouncer
state | active
addr | unix
port | 6432
local_addr | unix
local_port | 6432
connect_time | 2010-05-25 05:26:20
request_time | 2010-05-25 05:39:46
(continues on next page)

2.2. Using the PgBouncer Admin Console 22

PgBouncer Guide

(continued from previous page)

ptr | 0x8655d20
link |

You can use other variations of the SHOW command to retrieve information about PgBouncer:

SHOW STATS
SHOW SERVERS
SHOW POOLS
SHOW LISTS
SHOW USERS
SHOW DATABASES
SHOW FDS
SHOW CONFIG

You can use the following commands to control the PgBouncer process:
PAUSE
Use the PAUSE command to disconnect all servers after waiting for current queries to complete.
SUSPEND
Use the SUSPEND command to flush the socket buffers and suspend the PgBouncer process.
RESUME
Use the RESUME command to resume work after a PAUSE or SUSPEND command.
SHUTDOWN
Use the SHUTDOWN command to stop the PgBouncer process and exit.
RELOAD
Use the RELOAD command to reload the PgBouncer configuration files.
For more information about using PgBouncer, see the PgBouncer project site.

2.2. Using the PgBouncer Admin Console 23

CHAPTER 3

Uninstallation

This section walks you through uninstalling PgBouncer.

3.1 Uninstalling PgBouncer on a CentOS Host

To uninstall PgBouncer on a CentOS host, assume identity of the root user and invoke the following command:
On RHEL/CentOS 6 and 7:

yum -y erase edb-pgbouncer<xx>

On RHEL/CentOS 8:

dnf -y erase edb-pgbouncer<xx>

Where <xx> is the PgBouncer version.

24
PgBouncer Guide

3.2 Uninstalling PgBouncer on a Debian or Ubuntu Host

To uninstall PgBouncer on a Debian or Ubuntu host, invoke the following command:

apt-get remove edb-pgbouncer<xx>

Where <xx> is the version you want to uninstall.

3.2. Uninstalling PgBouncer on a Debian or Ubuntu Host 25

PgBouncer Guide

3.3 Uninstalling PgBouncer on an SLES 12 Host

To uninstall PgBouncer on an SLES 12 host, assume the identity of the root user and invoke the following command:

zypper remove edb-pgbouncer<xx>

Where <xx> is the version you want to uninstall.

For example, to uninstall PgBouncer 1.14.0.1, invoke the following command:

zypper remove edb-pgbouncer114

3.3. Uninstalling PgBouncer on an SLES 12 Host 26

PgBouncer Guide

3.4 Uninstalling PgBouncer on a Windows Host

To uninstall PgBouncer on a Windows Host, perform the following steps:

1. The PgBouncer graphical installer creates an uninstaller in the installation directory. Navigate into the instal-
lation directory and assume superuser privileges. Open the uninstaller and click Yes to begin uninstalling
PgBouncer:

Fig. 1: The PgBouncer Uninstaller

2. The uninstallation process begins. Click OK when the uninstallation completes:

Fig. 2: Uninstallation completes

3.4. Uninstalling PgBouncer on a Windows Host 27

Index

C
Configuration and Usage, 19
Configuring PgBouncer, 19

I
Installation, 2
Installing PgBouncer on a CentOS Host,
4
Installing PgBouncer on a Debian or
Ubuntu Host, 6
Installing PgBouncer on a Windows
Host, 7
Installing PgBouncer on an SLES 12
Host, 18

U
Uninstallation, 24
Uninstalling PgBouncer on a CentOS
Host, 24
Uninstalling PgBouncer on a Debian or
Ubuntu Host, 25
Uninstalling PgBouncer on a Windows
Host, 27
Uninstalling PgBouncer on an SLES 12
Host, 26
Using the PgBouncer Admin Console, 22

28