Configuring and usage v1

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, Debian, and SLES, 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.

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.

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

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:

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