Configuration and Usage v1.0

Edit this page

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

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.

On RedHat/Centos, Debian, and SLES 12 SP4, the .ini file is located in the following directory:

/etc/edb/pgbouncer<x.x>/

Where <x.x> is the PgBouncer version.

On Windows, the .ini file is located in the following directory:

C:\Program Files\edb\pgbouncer<x.x>\share\

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 information. For Advanced Server, the configuration file contains an entry for the installation of Advanced Server that installed PgBouncer:

edb = host=127.0.0.1 port=5444

For PostgreSQL Server, you need to enter PostgreSQL server details as shown in the following example.

postgres = host=127.0.0.1 port=5432

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

ParameterDescription
nameThe name of the database to which the client application will connect.
hostThe IP address of the host.
portThe port on which the host is listening.
dbnameThe (optional) database name.
userA username (if different than the information specified by the connecting client ).
passwordA 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
postgres = host=127.0.0.1 port=5432
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:

ParameterDescription
admin_usersA comma-delimited list of users that are allowed to access the Admin Console (for management and monitoring purposes). By default, PgBouncer is installed with an admin_users = enterprisedb. For the PostgreSQL server, set this value as admin_users = postgres.
auth_fileThe path to the authentication file that contains username and passwords of clients that may connect to PgBouncer. The authentication file (userlist.txt) is located in /etc/edb/pgbouncer<x.x>/, 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. To make changes to the identities that can access PgBouncer, you can edit the existing authentication file, or specify an alternate authentication file with the auth_file parameter.
auth_typeThe authentication method used by PgBouncer. May be: md5, crypt, plain, trust or any. The default value is md5.
default_pool_sizeThe amount of user connections that are allowed to access the server. The default is 20 active connections.
group_connectionsClients providing the same application_name will be grouped to use the same connection. The default is 0.
ignore_startup_parametersA comma-delimited list of application startup packets that PgBouncer should ignore. The default is application_name.
listen_addrThe 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_portThe port that PgBouncer monitors for client connections. By default, PgBouncer listens on port 6432.
logfileThe path to the PgBouncer log file.
max_client_connThe maximum number of connections allowed. The default is 100.
pidfileThe path to the process ID file.
pool_modeThe 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_queryThe default is DISCARD ALL which instructs PgBouncer to clean any changes made to a database session.
stats_usersA comma delimited list of users who are allowed to connect and run read-only queries. The default is stats_users = enterprisedb. For the PostgreSQL server, set this value as stats_users = postgres.

The following example demonstrates the syntax allowed in the [pgbouncer] section of the configuration file for PgBouncer 1.15 for EDB Advanced Server:

[pgbouncer]
logfile = /var/log/edb/pgbouncer1.15/edb-pgbouncer-1.15.log
pidfile = /var/run/edb/pgbouncer1.15/edb-pgbouncer-1.15.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/edb/pgbouncer-1.15/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. For CentOS, RHEL, SUSE, and Windows platforms, the PgBouncer service name is edb-pgbouncer-1.15, and for Deb packages, the PgBouncer service name is edb-pgbouncer115; use platform-specific commands to stop, start, or restart the service as needed.

Using the PgBouncer Admin Console

The Admin Console allows you to retrieve statistical information about PgBouncer activity, and to control the PgBouncer 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. Please note that the required connection information will vary according to the connecting client, platform and authentication information required by the server.

$ ./edb-psql -p 6432 -U enterprisedb pgbouncer

For PostgreSQL server, the command is:

$ ./edb-psql -p 6432 -h /tmp -U postgres  pgbouncer

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         | enterprisedb
   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
   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. For more information about using PgBouncer, see the PgBouncer project site.

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.