ELMA365 On-Premises > Prepare infrastructure > Load balancer / Install BGBouncer

Install BGBouncer

PGBouncer is a program that manages a connection pool for PostgreSQL. Any end application can connect to PGBouncer as if it were directly connecting to a PostgreSQL server. PGBouncer will establish a connection to the real server or reuse one of the previously established connections.

The purpose of PGBouncer is to minimize the costs associated with establishing new connections to PostgreSQL. You can also refer to the official PGBouncer documentation for more details.

The installation process consists of five steps:

Step 1: Install PGBouncer

Install PGBouncer on the node with PostgreSQL using the following command:

sudo apt-get install pgbouncer -y

Step 2: Configure PGBouncer

Move the default configuration file:

sudo mv /etc/pgbouncer/pgbouncer.ini{,.original}

Create and open a new configuration file for editing:

sudo nano /etc/pgbouncer/pgbouncer.ini

Add the example configurations to the file:

[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
ignore_startup_parameters = extra_float_digits,geqo
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 800
reserve_pool_size = 150
reserve_pool_timeout = 1
max_db_connections = 1000
pkt_buf = 8192
listen_backlog = 4096
log_connections = 0
log_disconnections = 0

where:

listen_addr is a comma-separated list of addresses to listen for TCP connections. If set to *, it will listen on all addresses;
listen_port is the port to listen on; the default is 6432;
pool_mode is the work schedule;
auth_type is the user authentication mode;
max_client_conn is the maximum allowed number of client connections;
default_pool_size is the size of the pool of connections to database;
reserve_pool_size is the size of the reserve pool of connections to databases;
max_db_connections is the maximum number of allowed open connections to databases;
[databases] defines the names of databases that clients can connect to and where those connections will be routed.

Configuring TLS/SSL in PGBouncer

Step 3: Add users

To the /etc/pgbouncer/userlist.txt file, add the usernames and passwords for connecting PgBouncer to the database. To do this:

Create file userlist.txt:

sudo nano /etc/pgbouncer/userlist.txt

Add the username and password to the file userlist.txt for connecting to PostgreSQL. The user elma365 was previously created during the deployment of PostgreSQL. For more information, refer to the articles PostgreSQL and PostgreSQL cluster.

"elma365" "SecretPassword"