.::: Install & Configure PgBouncer in Centos 9 Almalinux 9 for PostgreSQL 18 :::.

 

1. Install PgBouncer

dnf install -y pgbouncer

rpm -qa | grep pgbouncer

[root@teguhth-all ~]# rpm -qa | grep pgbouncer
pgbouncer-1.25.2-43PGDG.rhel9.8.x86_64
[root@teguhth-all ~]# pgbouncer --version
PgBouncer 1.25.2
libevent 2.1.12-stable
adns: c-ares 1.19.1
tls: OpenSSL 3.5.5 27 Jan 2026
systemd: yes
[root@teguhth-all ~]#

2. Backup konfigurasi

cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/back_pgbouncer.ini
 
3. create user for pgbouncer admin 

create user userpgbouncer ;
ALTER ROLE userpgbouncer SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE userpgbouncer PASSWORD 'admin';
ALTER USER userpgbouncer WITH PASSWORD 'admin';

4. Konfigurasi paling sederhana

[databases]
teguhth = host=127.0.0.1 port=5432 dbname=teguhth

[pgbouncer]
listen_addr = 10.10.10.90
listen_port = 6432

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres
stats_users = postgres

pool_mode = transaction

max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5

server_idle_timeout = 600
server_lifetime = 3600

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

5. restart pgbouncer
 
systemctl restart pgbouncer 
systemctl enabled pgbouncer 

6. creat userlist via pgbouncer 
chmod 600 /etc/pgbouncer/userlist.txt
chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt

[root@teguhth-all pgbouncer]# cat /etc/pgbouncer/userlist.txt
"userpgbouncer" "admin"
[root@teguhth-all pgbouncer]# chmod 600 /etc/pgbouncer/userlist.txt
chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
[root@teguhth-all pgbouncer]#

[root@teguhth-all ~]# cat /etc/pgbouncer/userlist.txt
"userpgbouncer" "admin"
"admin" "SCRAM-SHA-256$4096:m4KzoN+aqgVFA8ZP5sRCJA==$FtS3clX43VAGv3dZ+CI9InCaVkqUi20gz5IsRhCCSqI=:Cl6nJIGW3JHyPk/jqwNz03rv0B2wvFUg+w9Mz0YWSKw="
"userpgpool" "SCRAM-SHA-256$4096:kRdOtJTNGc41EXNbQSgiHg==$donZn9H3BqJUDpacbaquEBavtvSVA/6Y/ZUmjeauEfg=:w3UUUeu4Bh9wY8qPFTbzvrsfXzW8wLiZr+pBCLWViSY="

#"admin" "admin"
#"userpgpool" "admin"
[root@teguhth-all ~]#

7. restart pgbouncer every change in /etc/pgbouncer/userlist.txt
systemctl restart pgbouncer 

8. if add user for access pgbouncer 

SET password_encryption = 'scram-sha-256';
ALTER ROLE admin PASSWORD 'admin';
ALTER ROLE userpgpool PASSWORD 'admin';
ALTER ROLE userpgbouncer PASSWORD 'admin';

SELECT usename, passwd
FROM pg_shadow
WHERE usename IN ('admin', 'userpgpool', 'userpgbouncer');

SELECT usename, passwd FROM pg_shadow WHERE usename IN ('admin', 'userpgpool', 'userpgbouncer');
 

9. test login via direct

psql -h 127.0.0.1 -p 5432 -U admin -d teguhth
psql -h 127.0.0.1 -p 5432 -U userpgpool -d teguhth
psql -h 127.0.0.1 -p 5432 -U userpgbouncer -d teguhth
 

10. test login via pgbouncer 

psql -h 10.10.10.90 -p 6432 -U admin -d teguhth
psql -h 10.10.10.90 -p 6432 -U userpgpool -d teguhth
psql -h 10.10.10.90 -p 6432 -U userpgbouncer -d teguhth
 

11. compare result direct vs via pgbouncer 

SELECT inet_client_addr(), inet_client_port(), current_user, current_database();     
SELECT usename, client_addr, state, count(*) FROM pg_stat_activity WHERE usename = 'userpgbouncer' GROUP BY usename, client_addr, state ORDER BY count(*) DESC;