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. | Hostname | IP | Role |
| 1 | test-machine01 | 192.168.114.177 | PostgreSql Cluster Node |
| 2 | test-machine02 | 192.168.114.176 | PgBouncer 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
- Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)
- Access MySql database from PostgreSQL using MySQL Foreign Data Wrapper (MYSQL_FDW)
- Configure and monitor using pg_profile in PostgreSQL
- Monitor PostgreSQL Cluster using pgCenter
- Install and Configure pg_repack in PostgreSQL
