# Install BGBouncer

> [HTML Version](pgbouncer-installation.html)

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](https://www.pgbouncer.org/faq.html) for more details.

The installation process consists of five steps:

1. [Install PGBouncer](#install-pgbouncer).

2. [Configure PGBouncer](#configure-pgbouncer).

3. [Add users](#add-users).

4. [Set LimitNOFILE in the pgbouncer.service file](#set-limitnofile).

5. [Connect to PGBouncer](#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\}

2. ````
Create and open a new configuration file for editing:

````
sudo nano /etc/pgbouncer/pgbouncer.ini

3. ````
Add the example configurations to the file:

````
\[databases\]  
postgres = host=127.0.0.1 port=5432 dbname=brix365  
\* = 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:

- \[OBJECT\] is a comma-separated list of addresses to listen for TCP connections. If set to \[OBJECT\], it will listen on all addresses;

- \[OBJECT\] is the port to listen on; the default is \[OBJECT\];

- \[OBJECT\] is the work schedule;

- \[OBJECT\] is the user authentication mode;

- \[OBJECT\] is the maximum allowed number of client connections;

- \[OBJECT\] is the size of the pool of connections to database;

- \[OBJECT\] is the size of the reserve pool of connections to databases;

- \[OBJECT\] is the maximum number of allowed open connections to databases;

- \[OBJECT\] 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 \[OBJECT\]:

- \[OBJECT\] set TLS schedule by setting \[OBJECT\];

- \[OBJECT\] specify the path to the root CA file;

- \[OBJECT\] specify  the path to the server certificate file;

- \[OBJECT\] specify the path to the private key file.

````
\[databases\]  
postgres = host=127.0.0.1 port=5432 dbname=brix365  
\* = 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](https://www.pgbouncer.org/config.html#tls-settings) for detailed TLS/SSL configuration.

## Step 3: Add users

To the \[OBJECT\] file, add the usernames and passwords for connecting PgBouncer to the database. To do this:

1. Create file \[OBJECT\]:

````
sudo nano /etc/pgbouncer/userlist.txt

2. ````
Add the username and password to the file \[OBJECT\] for connecting to PostgreSQL. The user **brix365** was previously created during the deployment of PostgreSQL. For more information, refer to the articles [PostgreSQL](postgresql.md) and [PostgreSQL cluster](configure-postgresql.md).

````
"brix365" "SecretPassword"

## ````
Step 4: Set LimitNOFILE in the pgbouncer.service file

Add the \[OBJECT\] parameter to the Service section of the PGBouncer service file.

1. Open the file\[OBJECT\]:

````
sudo nano /etc/pgbouncer/userlist.txt

2. ````
Set the file open limit \[OBJECT\] to \[OBJECT\].

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
````

3. ````
Restart PgBouncer:

````
sudo systemctl daemon-reload  
sudo systemctl restart pgbouncer

## ````
Step 5: Connect to PGBouncer

Connection string for PGBouncer:

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

````
Connection string for PGBouncer with TLS/SSL:

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