Configure PgBouncer for PostgreSQL

August 22, 2021
()

Configure PgBouncer for PostgreSQL

PostgreSQL has a heavyweight connection handling architecture. For each incoming connection, the postmaster forks out a new process  called as backend process to handle it. While this design provides better stability and isolation, it does not make it particularly efficient at handling short-lived connections. A new Postgres client connection involves TCP setup, process creation, and backend initialization – all of which are costly in terms of time and system resources.


PgBouncer is an open-source, lightweight connection pooler for PostgreSQL. It can pool connections to one or more databases  and serve clients over TCP and Unix domain sockets. PgBouncer maintains a pool of connections for each unique user, database pair. It’s typically configured to hand out one of these connections to a new incoming client connection and return it back into the pool when the client disconnects.


Below are the high-level steps we will follow to set up our replication.
1. Installation of PgBouncer
2. Setup PostgreSql Cluster
3. Setup PgBouncer Configuration file
4. Test PgBouncer
5. Monitor PgBouncer

Below are setup details and the same will be used in this demonstration.

Sr. No.HostnameIPRole
1test-machine01192.168.114.177PostgreSql Cluster Node
2test-machine02192.168.114.176PgBouncer Node


Step 1. Installation of PgBouncer: You can install PgBouncer from pgdg-common repo. Use OS command yum to install PgBouncer. Once installation is done start and enables PgBouncer service using below commands. By default PgBouncer using Port 6432 you can confirm using OS command netstat. You can find sample PgBouncer configuration file under directory /etc/pgbouncer.

[root@test-machine02 ~]# yum whatprovides pgbouncer
Loaded plugins: langpacks, ulninfo
pgbouncer-1.15.0-1.rhel7.x86_64 : Lightweight connection pooler for PostgreSQL
Repo        : pgdg-common



pgbouncer-1.16.0-1.rhel7.x86_64 : Lightweight connection pooler for PostgreSQL
Repo        : pgdg-common



[root@test-machine02 ~]# yum install pgbouncer
Loaded plugins: langpacks, ulninfo
Dependencies Resolved

==============================================================================================================================================================================================
 Package                                            Arch                                   Version                                          Repository                                   Size
==============================================================================================================================================================================================
Installing:
 pgbouncer                                          x86_64                                 1.16.0-1.rhel7                                   pgdg-common                                 215 k
Installing for dependencies:
 python3                                            x86_64                                 3.6.8-18.0.3.el7                                 ol7_latest                                   70 k
 python3-libs                                       x86_64                                 3.6.8-18.0.3.el7                                 ol7_latest                                  6.9 M
 python3-pip                                        noarch                                 9.0.3-8.el7                                      ol7_latest                                  1.6 M
 python3-psycopg2                                   x86_64                                 2.8.6-1.rhel7                                    pgdg-common                                 173 k
 python3-setuptools                                 noarch                                 39.2.0-10.el7                                    ol7_latest                                  628 k

Installed:
  pgbouncer.x86_64 0:1.16.0-1.rhel7

Dependency Installed:
  python3.x86_64 0:3.6.8-18.0.3.el7 python3-libs.x86_64 0:3.6.8-18.0.3.el7 python3-pip.noarch 0:9.0.3-8.el7 python3-psycopg2.x86_64 0:2.8.6-1.rhel7 python3-setuptools.noarch 0:39.2.0-10.el7

Complete!
[root@test-machine02 ~]#

[root@test-machine02 pgbouncer]# /usr/bin/pgbouncer --version
PgBouncer 1.16.0
libevent 2.0.21-stable
adns: libc-2.17
tls: OpenSSL 1.0.2k-fips  26 Jan 2017
[root@test-machine02 pgbouncer]#

[root@test-machine02 ~]# systemctl list-unit-files |grep pgbouncer
pgbouncer.service                             disabled
[root@test-machine02 ~]#

[root@test-machine02 ~]# systemctl status pgbouncer
â pgbouncer.service - A lightweight connection pooler for PostgreSQL
   Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@test-machine02 ~]#

[root@test-machine02 pgbouncer]# systemctl enable pgbouncer
Created symlink from /etc/systemd/system/multi-user.target.wants/pgbouncer.service to /usr/lib/systemd/system/pgbouncer.service.
[root@test-machine02 pgbouncer]#

[root@test-machine02 pgbouncer]#
[root@test-machine02 pgbouncer]# systemctl start pgbouncer
[root@test-machine02 pgbouncer]#
[root@test-machine02 pgbouncer]# systemctl status pgbouncer
â pgbouncer.service - A lightweight connection pooler for PostgreSQL
   Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-08-19 10:30:39 +03; 4s ago
 Main PID: 95564 (pgbouncer)
   CGroup: /system.slice/pgbouncer.service
           ââ95564 /usr/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini

Aug 19 10:30:39 test-machine02 systemd[1]: Started A lightweight connection pooler for PostgreSQL.
Aug 19 10:30:39 test-machine02 pgbouncer[95564]: 2021-08-19 10:30:39.814 +03 [95564] ERROR could not open auth_file /etc/pgbouncer/userlist.txt: No such file or directory
Aug 19 10:30:39 test-machine02 pgbouncer[95564]: 2021-08-19 10:30:39.816 +03 [95564] LOG kernel file descriptor limit: 1024 (hard: 4096); max_client_conn: 100, max expected fd use: 112
Aug 19 10:30:39 test-machine02 pgbouncer[95564]: 2021-08-19 10:30:39.818 +03 [95564] LOG listening on [::1]:6432
Aug 19 10:30:39 test-machine02 pgbouncer[95564]: 2021-08-19 10:30:39.818 +03 [95564] LOG listening on 127.0.0.1:6432
Aug 19 10:30:39 test-machine02 pgbouncer[95564]: 2021-08-19 10:30:39.818 +03 [95564] LOG listening on unix:/tmp/.s.PGSQL.6432
Aug 19 10:30:39 test-machine02 pgbouncer[95564]: 2021-08-19 10:30:39.819 +03 [95564] LOG process up: PgBouncer 1.16.0, libevent 2.0.21-stable (epoll), adns: libc-2.17, tls: Op... 26 Jan 2017
Hint: Some lines were ellipsized, use -l to show in full.
[root@test-machine02 pgbouncer]#

[root@test-machine02 pgbouncer]#
[root@test-machine02 pgbouncer]# netstat -ntlp |grep pgbouncer
tcp        0      0 127.0.0.1:6432          0.0.0.0:*               LISTEN      95564/pgbouncer
tcp6       0      0 ::1:6432                :::*                    LISTEN      95564/pgbouncer
[root@test-machine02 pgbouncer]#

[root@test-machine02 pgbouncer]# pwd
/etc/pgbouncer
[root@test-machine02 pgbouncer]# ls -ltr
total 24
-rw-r--r--. 1 root root 9342 Aug 12 10:07 pgbouncer.ini
-rwx------. 1 root root  819 Aug 12 10:07 mkauth.py
-rw-r--r--. 2 root root 1132 Aug 12 10:07 mkauth.pyo
-rw-r--r--. 2 root root 1132 Aug 12 10:07 mkauth.pyc
[root@test-machine02 pgbouncer]#


Step 2. Setup PostgreSql Cluster: We will create a user/role appuser in PostgreSQL Cluster. We will be using same USER as your application user and postgres user as admin in PgBouncer config file. Get the password hash value using below query we need this for PgBouncer configuration. And grant privileges on sample1 database to newly created user appuser. Initialize DB sample1 for pgbench we will be using pgbench tool for testing purposes. Also, make sure pg_hba.conf allowing remote connections.

[postgres@test-machine01]
[postgres@test-machine01]psql
psql (13.1)
Type "help" for help.

postgres=#
postgres=# create role appuser with LOGIN PASSWORD 'Root@1234';
CREATE ROLE
postgres=#

postgres=# select rolname,rolpassword from pg_authid where rolname in ('appuser','postgres');
 rolname  |                                                              rolpassword
----------+---------------------------------------------------------------------------------------------------------------------------------------
 appuser  | SCRAM-SHA-256$4096:MkrhCPHiAPTvS4UIZiPLNA==$lx6EXBbJ6uOjimUCp8rseDj+njYts0YCKZwq7ybHrWY=:eArDiJEf5VLAqSBJYqMjKiWQmBVHqMxKG+1RDWOpygc=
 postgres | SCRAM-SHA-256$4096:/W2bB6jZs3wvdNMLJ/fyLA==$F0b96zglCc+YqaimEgKaep6IeewRhjJM3u/dHVSr++o=:d/OlJb8+2CLxSiGI/74jT1peQpFSbtEuXNDR0V6Zfgs=
(2 rows)

postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=#
sample1=# GRANT ALL ON ALL TABLES IN SCHEMA "public" TO appuser;
GRANT
sample1=#

[postgres@test-machine01]/usr/pgsql-13/bin/pgbench -i sample1 -U postgres
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.69 s (drop tables 0.06 s, create tables 0.19 s, client-side generate 0.12 s, vacuum 0.12 s, primary keys 0.20 s).
[postgres@test-machine01]
[postgres@test-machine01]

[root@test-machine01 data]# pwd
/var/lib/pgsql/13/data
[root@test-machine01 data]# cat pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.114.0/24        scram-sha-256
[root@test-machine01 data]#


Step 3. Setup PgBouncer Configuration file: PgBouncer relies on the main configuration file, typically stored as /etc/pgbouncer/pgbouncer.ini. We also need to create a userlist.txt file in the same directory, with the username and (hashed) passwords of users that PgBouncer will allow to connect. Create userlist.txt with the following contents. Please note we are using the same two users repluser & postgres. We also have to define postgres as an admin user of PgBouncer. Once all configuration is done restart the PgBouncer service.

[root@test-machine02 pgbouncer]# pwd
/etc/pgbouncer
[root@test-machine02 pgbouncer]# vi pgbouncer.ini
[databases]
sample1= host=test-machine01 port=5432 dbname=sample1
[pgbouncer]
listen_addr = *
auth_type = scram-sha-256
admin_users = postgres
max_client_conn = 1000
:wq!

[root@test-machine02 pgbouncer]# pwd
/etc/pgbouncer
[root@test-machine02 pgbouncer]# vi userlist.txt
"postgres","SCRAM-SHA-256$4096:WBjbx2xi5TM4jCJ907IwzQ==$UDJyKFYq2/pYJu/Xd8Q0Pr2P6BIx6aNkAnChkGKtiOU=:LxPlf7oy1avW0YvMo4ch0M2X99mcnHOsUcKmsq5IrbY="
"repluser","SCRAM-SHA-256$4096:WBjbx2xi5TM4jCJ907IwzQ==$UDJyKFYq2/pYJu/Xd8Q0Pr2P6BIx6aNkAnChkGKtiOU=:LxPlf7oy1avW0YvMo4ch0M2X99mcnHOsUcKmsq5IrbY="
:wq!

[root@test-machine02 pgbouncer]#
[root@test-machine02 pgbouncer]# systemctl restart pgbouncer
[root@test-machine02 pgbouncer]#


Step 4. Test PgBouncer: We will use pgbench tool for testing purposes. We will try to connect 50 concurrent database clients connection using two different methods, 1. Using PgBouncer 2. Direct Connection. You will notice using PgBouncer even creating 50 connections in DB level we will have only 20 backend sessions. But when using direct connection PostgreSQL will create 50 backend sessions.


Method 1. Connecting using PgBouncer

[postgres@test-machine02]/usr/pgsql-13/bin/pgbench  -h test-machine02 -p 6432  -U appuser -d sample1 -c 50 -t 100
pgbench: pghost: test-machine02 pgport: 6432 nclients: 50 nxacts: 100 dbName: sample1
Password:
starting vacuum...WARNING:  skipping "pgbench_branches" --- only table or database owner can vacuum it
WARNING:  skipping "pgbench_tellers" --- only table or database owner can vacuum it
end.
pgbench: client 0 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 0 executing \set aid
pgbench: client 0 executing \set bid
pgbench: client 0 executing \set tid
pgbench: client 0 executing \set delta


[postgres@test-machine01]
[postgres@test-machine01]psql
psql (13.1)
Type "help" for help.
postgres=#
postgres=# select count(*) from pg_stat_activity where datname='sample1';
 count
-------
    20
(1 row)

postgres=#


Method 2. Connecting directly to PostgreSql Cluster

[postgres@test-machine02]
[postgres@test-machine02]/usr/pgsql-13/bin/pgbench  -h test-machine01 -p 5432  -U appuser -d sample1 -c 50 -t 100
pgbench: pghost: test-machine01 pgport: 5432 nclients: 50 nxacts: 100 dbName: sample1
Password:
starting vacuum...WARNING:  skipping "pgbench_branches" --- only table or database owner can vacuum it
WARNING:  skipping "pgbench_tellers" --- only table or database owner can vacuum it
end.
pgbench: client 0 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 0 executing \set aid
pgbench: client 0 executing \set bid
pgbench: client 0 executing \set tid
pgbench: client 0 executing \set delta


[postgres@test-machine01]
[postgres@test-machine01]psql
psql (13.1)
Type "help" for help.

postgres=#
postgres=# select count(*) from pg_stat_activity where datname='sample1';
 count
-------
    50
(1 row)

postgres=#


Step 5. Monitor PgBouncer: PgBouncer allows users marked as admins to connect to a virtual database called “pgbouncer” and issue commands to control the server and see statistics. 

[postgres@test-machine02]
[postgres@test-machine02]psql -p 6432 -U postgres  -h localhost pgbouncer
Password for user postgres:
psql (13.2, server 1.16.0/bouncer)
Type "help" for help.

pgbouncer=#
pgbouncer=# show help;
NOTICE:  Console usage
DETAIL:
        SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
        SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
        SHOW DNS_HOSTS|DNS_ZONES
        SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
        SET key = arg
        RELOAD
        PAUSE [<db>]
        RESUME [<db>]
        DISABLE <db>
        ENABLE <db>
        RECONNECT [<db>]
        KILL <db>
        SUSPEND
        SHUTDOWN

SHOW
pgbouncer=#

pgbouncer=# show databases;
   name    |      host      | port | database  | force_user | pool_size | min_pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled
-----------+----------------+------+-----------+------------+-----------+---------------+--------------+-----------+-----------------+---------------------+--------+----------
 pgbouncer |                | 6432 | pgbouncer | pgbouncer  |         2 |             0 |            0 | statement |               0 |                   0 |      0 |        0
 sample1   | test-machine01 | 5432 | sample1   |            |        20 |             0 |            0 |           |               0 |                   0 |      0 |        0
(2 rows)

pgbouncer=#


pgbouncer=#
pgbouncer=# show pools;
 database  |   user    | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
 pgbouncer | pgbouncer |         1 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement
 sample1   | appuser   |         0 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | session
(2 rows)

pgbouncer=#

pgbouncer=#
pgbouncer=# show clients;
 type |   user   | database  | state  | addr | port  | local_addr | local_port |      connect_time       |      request_time       | wait | wait_us | close_needed |   ptr    | link | remote_
pid | tls
------+----------+-----------+--------+------+-------+------------+------------+-------------------------+-------------------------+------+---------+--------------+----------+------+--------
----+-----
 C    | postgres | pgbouncer | active | ::1  | 59760 | ::1        |       6432 | 2021-08-19 17:16:47 +03 | 2021-08-19 17:18:04 +03 |   71 |  311945 |            0 | 0xe80528 |      |
  0 |
(1 row)

pgbouncer=#

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *