search string side menu

BRIX 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:

  1. Install PGBouncer.
  2. Configure PGBouncer.
  3. Add users.
  4. Set LimitNOFILE in the pgbouncer.service file.
  5. Connect to PGBouncer.

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

  1. Move the default configuration file:

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

  1. Create and open a new configuration file for editing:

sudo nano /etc/pgbouncer/pgbouncer.ini

  1. Add the example configurations to the file:

[databases]
postgres = host=127.0.0.1 port=5432 dbname=elma365
* = 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

To enable TLS/SSL support in PGBouncer, add the following parameters to the configuration file /etc/pgbouncer/pgbouncer.ini:

  • client_tls_sslmode set TLS schedule by setting require;
  • client_tls_ca_file specify the path to the root CA file;
  • client_tls_cert_file specify  the path to the server certificate file;
  • client_tls_key_file specify the path to the private key file.

[databases]
postgres = host=127.0.0.1 port=5432 dbname=elma365
* = 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
client_tls_sslmode = require
client_tls_ca_file = /etc/ssl/certs/rootCA.pem
client_tls_key_file = /etc/ssl/private/selfsigned.key
client_tls_cert_file = /etc/ssl/certs/selfsigned.crt

Refer to the official PGBouncer documentation for detailed TLS/SSL configuration.

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:

  1. Create file userlist.txt:

sudo nano /etc/pgbouncer/userlist.txt

  1. 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"

Step 4: Set LimitNOFILE in the pgbouncer.service file

Add the LimitNOFILE parameter to the Service section of the PGBouncer service file.

  1. Open the file/lib/systemd/system/pgbouncer.service:

sudo nano /etc/pgbouncer/userlist.txt

  1. Set the file open limit LimitNOFILE to 15000.

Example of adding LimitNOFILE to pgbouncer.service

[Unit]
Description=connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
Documentation=https://www.pgbouncer.org/
After=network.target
#Requires=pgbouncer.socket
 
[Service]
Type=notify
User=postgres
ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
LimitNOFILE=15000
 
[Install]
WantedBy=multi-user.target
  1. Restart PgBouncer:

sudo systemctl daemon-reload
sudo systemctl restart pgbouncer

Step 5: Connect to PGBouncer

Connection string for PGBouncer:

postgresql://elma365:SecretPassword@<postgresql-server-address>:6432/elma365?sslmode=disable

Connection string for PGBouncer with TLS/SSL:

postgresql://elma365:SecretPassword@<postgresql-server-address>:6432/elma365?sslmode=require

proxy-s3-kubernetes.html installing-harbor.html