Configure PgBouncer
|
NOTE
The PgBouncer feature is available for ADPG Enterprise Edition.
|
ADPG includes the PgBouncer tool that is a lightweight connection pooler for PostgreSQL. An application can connect to PgBouncer as an ADPG server, and PgBouncer creates a connection to the ADPG server, or reuses one of its existing connections. PgBouncer is designed to reduce the performance impact of opening new PostgreSQL connections. For more information, see PgBouncer overview.
To enable PgBouncer and set its configuration parameters, open the Services tab of an ADPG cluster and select the ADPG service. On the Primary configuration tab, switch on the Enable PgBouncer toggle button. When this toggle button is enabled, you can expand the corresponding node and specify required settings.
All available settings are listed in the table below.
| Parameter name | Description | Default value |
|---|---|---|
listen_port |
The port that PgBouncer listens on. This port cannot be changed for a running cluster. Execute the Reconfigure & Restart action to apply changes |
15432 |
pool_mode |
Specifies when a server connection can be reused by other clients. It can contain the following values:
|
session |
max_client_conn |
The maximum number of client connections allowed |
100 |
default_pool_size |
The number of connections to the server that are allowed for each user/database pair |
20 |
min_pool_size |
The minimum number of connections to the server to keep in the pool |
0 |
reserve_pool_size |
The number of additional connections allowed to the pool if a client connection exceeds the time specified in the |
0 |
reserve_pool_timeout |
If a client needs to wait longer than the specified time in seconds, additional connections from |
5 |
max_db_connections |
The maximum number of connections to a server for a database |
0 |
max_user_connections |
The maximum number of connections to a server for a user |
0 |
Enable all users |
When enabled, this option allows access to all users with password authentication. If the option is disabled, utilize |
Enabled |
Users list |
List of users who are allowed to access ADPG using PgBouncer, when the |
Empty |
Enable all databases |
When enabled, this option allows access to all existing databases with PgBouncer. If the option is disabled, utilize |
|
Databases list |
List of databases that are allowed to access using PgBouncer when the |
Empty |
Use custom pg_hba.conf |
When disabled, the general PG_HBA section of the ADPG service will be used for PgBouncer. If the option is enabled, it is necessary to add required settings to Custom pg_hba.conf. In this case, PgBouncer will use settings from Custom pg_hba.conf |
Disabled |
Custom pg_hba.conf |
Custom pg_hba.conf section for PgBouncer |
Empty |
To apply changes, click Save, and execute the Reconfigure & Restart action of the ADPG service.
To make PgBouncer monitoring work correctly, call the cluster action Reconfigure monitoring agents after changing settings from the Enable pgbouncer configuration section.
In ADPG, PgBouncer utilizes the hba client authentication. If Use custom pg_hba.conf is disabled, the actual authentication type is loaded from the pg_hba.conf file. If Use custom pg_hba.conf is enabled, the settings from Custom pg_hba.conf is used.
By default, the Enable all users option is enabled. It allows access to all users with password authentication. You can disable this option and specify a list of users who are allowed to access ADPG using PgBouncer in the Users list parameter.
The Enable all databases option allows access to all existing databases with PgBouncer. You can disable this option and specify a list of databases that are allowed to access using PgBouncer in the Databases list parameter.
If the Balancer service is not added to the cluster, all the client requests should be sent to a PgBouncer listen_port on both leader and replicas nodes.
If your ADPG cluster uses the Balancer service, connections to the leader node should be sent to the balancer port specified in the pgbouncer_leader_port parameter of the Balancer service. All connections to this port are transferred to a PgBouncer instance of an ADPG leader node. The default pgbouncer_leader_port value is 16432.
Connections to replica nodes should be sent to the balancer port specified in the pgbouncer_replica_port parameter of the Balancer service. All connections to this port are transferred to PgBouncer instances of ADPG replica nodes. The default pgbouncer_replica_port value is 16433.
To change the pgbouncer_leader_port and pgbouncer_replica_port parameters, open the Services tab of an ADPG cluster and select the Balancer service. Specify the parameters on the Primary configuration tab.
After you specify parameters, click Save and execute the Reconfigure & Restart action of the Balancer service to apply changes.