7.6 PgBouncer

Table of Contents Previous Next



PgBouncer is a lightweight connection pooler for Advanced Server. During an Advanced Server installation, the Advanced Configuration window will offer you the opportunity to specify a PgBouncer Listening Port, and allow you to specify a PgBouncer autostart preference. By default, PgBouncer is configured to monitor port 6432, and to start automatically when the operating system starts.
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:
The following example demonstrates the syntax allowed in the [databases] section of the configuration file:
Include the dbname parameter to map the connection name to an alternate database name. For example:
Please note that the connection information will vary according to the platform, client software and identity you are connecting with. PgBouncer uses the information provided in the connection string to connect to the database server. Specifying a user in the pgbouncer.ini file overrides user details supplied by the client. If the client provides user 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 server. The details of the user specified in the configuration file must be defined in the 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:
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:
The authentication method used by PgBouncer. May be: md5, crypt, plain, trust or any. The default value is md5
The default is DISCARD ALL which instructs PgBouncer to clean any changes made to a database session.
The following example demonstrates the syntax allowed in the [pgbouncer] section of the configuration file:
For more information about the settings used in the pgbouncer.ini file, please visit:
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:
After connecting to the pgbouncer database, you can use the SHOW CLIENTS command to retrieve client-related information:
The SHOW CLIENTS command returns:
You can use other variations of the SHOW command to retrieve information about PgBouncer:
Use the PAUSE command to disconnect all servers after waiting for current queries to complete.
Use the SUSPEND command to flush the socket buffers and suspend the PgBouncer process.
Use the RESUME command to resume work after a PAUSE or SUSPEND command.
Use the SHUTDOWN command to stop the PgBouncer process and exit.
Use the RELOAD command to reload the PgBouncer configuration files.


Table of Contents Previous Next