The short definition of PgBouncer is a connection pooler for PostgreSQL. Based on the official website, there are a few wonderful features for database administrator and system architecture for considering such as levels of brutality when rotating connections, low memory required per DB connection, the possibility of running separately from database hosts, online reconfiguration without down the services, and online restart/upgrade without interfering with client connections.

The natural characteristic of PostgreSQL has a heavyweight connection handling architecture. Each connection client has made to obtain data from the database, PostgreSQL will fork a new process that consumes more memory with the highly intensive application. While this architecture provides better stability and isolation, it does particularly inefficient at handling short-lived connections. With connection pooling served by PgBouncer, It can create pool connections to one or more databases (and possibly different servers) to provide clients over TCP and Unix domain sockets.

In this blog, we are going to introduce the installation from source and some configurations of the PgBouncer acting as a connection pool to serve the communication line between users and databases. We will install PgBouncer in the same host where we installed PostgreSQL 14 using this guideline.

STEP 1: Download Source of PgBouncer

First, we need to download the source code of PgBouncer from the official website. You can follow the command below to download it

wget https://www.pgbouncer.org/downloads/files/1.16.1/pgbouncer-1.16.1.tar.gz

STEP 2: Install Prerequisite Packages

There are some dependent packages such as gcc, zlib1g, libssl-dev, libevent-dev as a prerequisite to installing PgBouncer. Therefore, we have to install those packages first as the following command

#Checking version of gcc (gcc --version)
sudo apt install gcc
sudo apt install libreadline-dev
sudo apt install zlib1g-dev

sudo apt install libssl-dev
sudo apt install libevent-dev

STEP 3: Create Group and User

We have to create a group named dba and user pgbouncer to have permission to access the service of PgBouncer in the PostgreSQL database. As have mentioned in the previous article, This is a very important security measurement since we want only the created user to have the ability to access the PgBouncer service.

#Create a group "dba" (Maybe already exist)
sudo groupadd -g 54321 dba
#Create a user "pgbouncer" in group "dba"
sudo useradd -u 54322 -g dba pgbouncer
#Giving password to user "pgbouncer" (Let use Pgs123456 as password)
sudo passwd pgbouncer

Next, we have to create a home directory of the user pgbouncer as the following command

#Create home directory of user pgbouncer
sudo mkdir /home/pgbouncer
sudo chown -R pgbouncer:dba /home/pgbouncer/
#Setting permissions 
sudo chmod 755 /home/pgbouncer
#initialization
sudo cp -a /etc/skel/. /home/pgbouncer
#change from default from shell to bash
sudo usermod -s /bin/bash pgbouncer
#allow pgbouncer to use sudo
sudo usermod -aG sudo pgbouncer
#checking sudo permission
groups pgbouncer
#switching to user pgbouncer
su - pgbouncer

Next, prepare directories for setup the PgBouncer from source code

#Create directory for pgbouncer software to be installed on
sudo mkdir -p /app/pgbouncer/product/1.16/
sudo mkdir -p /app/pgbouncer/product/1.16/log/

sudo chown -R pgbouncer:dba /app/pgbouncer

STEP 4: Installation of PgBouncer

Now it's time to install PgBouncer from the source code we just download from step 1 by extracting using the tar command and using the make command to install as you can see below (Please read comment carefully)

tar -zxvf pgbouncer-1.16.1.tar.gz
cd pgbouncer-1.16.1/
./configure --prefix=/app/pgbouncer/product/1.16/
#if you got error because of pkg-config use below command to install
#sudo apt-get install -y pkg-config
make
make install

STEP 5: Configuration of PgBouncer Service

The reason you need to configure the PgBouncer service is to easily start, stop, or restart the PgBouncer service at any time you want without running the PgBouncer program file or shell script. You need to perform this with root permission access.

sudo vi /etc/systemd/system/pgbouncer.service

[Unit]
Description=A lightweight connection pooler for pgbouncerQL
After=syslog.target
After=network.target
[Service]
Type=forking
## Add a service section and set the max number of open files
LimitNOFILE=9999999
User=pgbouncer
#Group=pgbouncer
# Path to the init file
Environment=BOUNCERCONF=/app/pgbouncer/product/1.16/share/doc/pgbouncer/pgbouncer.ini
PIDFile=/app/pgbouncer/product/1.16/pgbouncer.pid
# Where to send early-startup messages from the server
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
ExecStart=/app/pgbouncer/product/1.16/bin/pgbouncer -d -q ${BOUNCERCONF}
ExecReload=/app/pgbouncer/product/1.16/bin/kill -HUP $MAINPID
KillSignal=SIGINT
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target

Next, let change the path of the log and PID of PgBouncer in the default configuration in /app/pgbouncer/product/1.16/share/doc/pgbouncer/pgbouncer.ini and then we can start PgBouncer service as command below

#sudo vi /app/pgbouncer/product/1.16/share/doc/pgbouncer/pgbouncer.ini
logfile = /app/pgbouncer/product/1.16/log/pgbouncer.log
pidfile = /app/pgbouncer/product/1.16/pgbouncer.pid

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer
sudo systemctl stop pgbouncer

STEP 6: Connecting PostgreSQL through PgBouncer

As mentioned in the introduction about the problem of using the direct connection from clients to the database, we will let users connect to the database through connection pooling provided by PgBouncer. We will not write any program or application to experiment. We will use the PSQL PostgreSQL client tool to connect to pooling by specifying the host, username, and port of PgBouncer and PgBouncer will route the connection to the database automatically through configuration in the config file. Next, we have to create a database testpgbouncerdb and a table tb_emploee using the below command to test.

#swtich user to postgres
su - postgres
#create database testpgbouncerdb
postgres@instance-1:/data01/log$ psql
psql (14.0)
Type "help" for help.
postgres=# create database testpgbouncerdb;

Next, let try to connect directly to the database testpgbouncerdb and create a new table tb_emploee

postgres@instance-1:~$ psql -U postgres -d testpgbouncerdb -p 5432
#5432 is the PostgreSQL port
psql (14.0)
Type "help" for help.
testpgbouncerdb=# create table tb_employee(id integer, name varchar(500));
CREATE TABLE

Next, let add the connection of this newly created database into the PgBouncer configuration file, restart PgBouncer, and test connection through the PgBouncer port to the database testpgbouncerdb

#switch user to pgbouncer
su - pgbouncer

sudo vi /app/pgbouncer/product/1.16/share/doc/pgbouncer/pgbouncer.ini

#add the below line into the section databases
[databases]
testpgbouncerdb=host=localhost port=5432 dbname=testpgbouncerdb

auth_type = trust
auth_file = /app/pgbouncer/product/1.16/share/doc/pgbouncer/userlist.txt

listen_addr = localhost
listen_port = 6432
#6432 is the pgbouncer port

#restart pgbouncer
sudo systemctl stop pgbouncer
sudo systemctl start pgbouncer

Finally, using PSQL to test the connection to the database testpgbouncerdb via port 6432 which is the PgBouncer port using the below command

#switch to postgres user
su - postgres

#command to connect to postgresql through pgbouncer
postgres@instance-1:~$ psql -U postgres -d testpgbouncerdb -p 6432
psql (14.0)
Type "help" for help.
testpgbouncerdb=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 public | tb_employee | table | postgres
(1 row)
testpgbouncerdb=# 

Note: All the setup and configuration steps we provided above are simple ones. If you would like to adjust based on your personal preference please do some more research on each configuration parameter of PgBouncer on this website.

THANK YOU, Find Us @
Facebook
Telegram
Twitter
LinkedIn