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 pgbouncer.ini file, located in the following directory:

On Linux:

/opt/edb/pgbouncer1.7/share/

On Windows:

C:\Program Files\edb\pgbouncer1.7\share

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

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=1safe_pwd!

Include the dbname parameter to map the connection name to an alternate database name. For example:

hr = host=127.0.0.1 port=5444 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:

Parameter

Description

admin_users

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

auth_file

The 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 /opt/edb/pgbouncer-1.7/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 authentication 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 PgBouncer 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 default, 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.

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

[pgbouncer]
logfile = /var/log/edb/pgbouncer1.7/edb-pgbouncer-1.7.log
pidfile = /var/run/edb/pgbouncer1.7/edb-pgbouncer-1.7.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /opt/edb/pgbouncer-1.7/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.7; 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:

# ./psql -p 6432 -U enterprisedb pgbouncer

Please note that the required connection information will vary according to the connecting client, platform and authentication 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
   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, click here.