Configuring and usage

Configure EDB PgBouncer before you use it.

Configuring EDB PgBouncer

When the EDB PgBouncer service is running, any Postgres client connecting to the EDB PgBouncer listener port specified in the configuration file uses connection pooling. EDB PgBouncer connection and configuration information is stored in the .ini file.

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

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

<x.x> is the EDB PgBouncer version.

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

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

<x.x> is the EDB PgBouncer version.

The EDB 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. The configuration file includes an entry for the installation of EDB Postgres Advanced Server that installed EDB PgBouncer:

edb = host=127.0.0.1 port=5444

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

postgres = host=127.0.0.1 port=5432

You can specify more database connection information in the configuration file in the form of keyword=value pairs. You can include the parameters shown in the table.

Parameter

Description

host

The IP address of the host

port

The port on which the host is listening

dbname

The (optional) database name

user

A user name (if different from the information specified by the connecting client)

password

A password (if different from the information specified by the connecting client)

The following example shows 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 alternative 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 EDB PgBouncer. EDB PgBouncer then uses the information specified in the EDB 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 don’t specify user details in pgbouncer.ini , the user name and password are authenticated by the database server and EDB PgBouncer. As such, include the user name and password in the userlist.txt file and the database cluster.

The [pgbouncer] section of the configuration file contains configuration details specific to EDB 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, EDB PgBouncer is installed with admin_users = enterprisedb. For the PostgreSQL server, set this value as admin_users = postgres.

auth_file

The path to the authentication file that contains user name and passwords of clients that can connect to EDB 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 can use to access EDB PgBouncer. In the authentication file, specify the user name and password in double quotes. To make changes to the identities that can access EDB PgBouncer, you can edit the existing authentication file or specify an alternative authentication file with the auth_file parameter.

auth_type

The authentication method used by EDB PgBouncer. Can be: md5, crypt, plain, trust, or any. The default value is md5.

auth_dbname

The option to specify the database name. The query mentioned by auth_query runs against this database.

default_pool_size

The amount of user connections that are allowed to access the server. The default is 20 active connections.

ignore_startup_parameters

A comma-delimited list of application startup packets for EDB PgBouncer to ignore. The default is application_name.

listen_addr

The IP address on which EDB PgBouncer listens for client connections. If omitted, only Unix socket connections are allowed. The client must also reside on the same host as EDB PgBouncer and can’t specify a host IP address when connecting to EDB PgBouncer.

listen_port

The port that EDB PgBouncer monitors for client connections. By default, EDB PgBouncer listens on port 6432.

logfile

The path to the EDB 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. Can be session, transaction, or statement. The default value is session.

server_reset_query

The default is DISCARD ALL, which instructs EDB 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. For the PostgreSQL server, set this value as stats_users = postgres.

cancel_wait_timeout

The time after which PgBouncer gives up on forwarding a cancel request. The default is 10 seconds.

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

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

For more information about the settings used in the pgbouncer.ini file, see pgbouncer.ini.

After editing the EDB PgBouncer configuration file for your environment, restart the EDB PgBouncer service to make the changes take effect. For CentOS, Rocky Linux, AlmaLinux, RHEL, SUSE, and Windows platforms, the EDB PgBouncer service name is edb-pgbouncer-1.16 . For Deb packages, the EDB PgBouncer service name is edb-pgbouncer116 . Use platform-specific commands to stop, start, or restart the service as needed.

Using the EDB PgBouncer Admin console

The Admin console allows you to retrieve statistical information about EDB PgBouncer activity and to control the EDB PgBouncer process. You can use the edb-psql client to access the EDB 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. EDB PgBouncer is listening on port 6432 with a user name of enterprisedb:

Enter following command after navigating to the bin directory under your EDB Postgres Advanced Server installation. That the required connection information varies 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:

__OUTPUT__
- -[ 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 EDB PgBouncer:

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

You can use the following commands to control the EDB 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 EDB PgBouncer process.

RESUME

Use the RESUME command to resume work after a PAUSE or SUSPEND command.

SHUTDOWN

Use the SHUTDOWN command to stop the EDB PgBouncer process and exit.

RELOAD

Use the RELOAD command to reload the EDB PgBouncer configuration files.