Step by Step setup Pgbouncer

 Server 1 : DB Server           :  IP 192.168.1.36

Server 2 : Pgbouncer Server : IP 192.168.1.38

[Application Clients] -------> [PgBouncer Server]---------> [PostgreSQL Server]

Server 1 :

Step 1: Download PostgreSQL 

https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Step 2: Install PostgreSQL

#  yum install -y postgresql15-server postgresql15

#  /usr/pgsql-15/bin/postgresql-15-setup initdb

#  vim /var/lib/pgsql/15/data/postgresql.conf

 listen_addresses = '*'

#vim /var/lib/pgsql/15/data/pg_hba.conf

host    all             all             192.168.1.0/24           md5

# systemctl restart postgresql-15

Server 2 : Pgbouncer Server : IP 192.168.1.38 Configure 

Step 1: Install pgbouncer

#  yum install -y pgbouncer

# vim /etc/pgbouncer/pgbouncer.ini

test_db= dbname=postgres host=192.168.1.36 port=5432  auth_user=postgres

[root@pgbouncer ~]# vim /etc/pgbouncer/userlist.txt

[root@pgbouncer ~]#"postgres" "your_postgres_password"

[root@pgbouncer ~]# chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt

[root@pgbouncer ~]# chmod 600 /etc/pgbouncer/userlist.txt

[root@pgbouncer ~]# systemctl restart pgbouncer

[root@pgbouncer ~]# ss -ltnp | grep 6432

[root@pgbouncer ~]#/etc/pgbouncer/pgbouncer.ini 

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

[root@pgbouncer ~]#  sudo lsof -i :6432

[root@pgbouncer ~]# pkill pgbouncer

[root@pgbouncer ~]# -u pgbouncer pgbouncer /etc/pgbouncer/pgbouncer.ini

If you getting FATAL unix socket is in use, cannot continue

Step 1: Stop any running PgBouncer

[root@pgbouncer ~]#sudo systemctl stop pgbouncer

Step 2: Remove old socket and PID files

[root@pgbouncer ~]#sudo rm -f /var/run/pgbouncer/.s.PGSQL.6432

[root@pgbouncer ~]#sudo rm -f /var/run/pgbouncer/pgbouncer.pid

[root@pgbouncer ~]#sudo -u pgbouncer pgbouncer /etc/pgbouncer/pgbouncer.ini

If you're not using the Unix socket and only connecting via TCP/IP, you can disable Unix socket binding in the config file:

[root@pgbouncer ~]# /etc/pgbouncer/pgbouncer.ini:

unix_socket_dir = /tmp

Or completely disable it by pointing to a fake directory:

unix_socket_dir = /var/empty

After configure : Go to DB server try to connect pgbouncer server:

[root@localhost ~]# sudo -i -u postgres

[root@localhost ~]# psql -h 192.168.1.38  -p 6432  -U postgres test_db

-bash-4.2$ psql -h 192.168.1.38  -p 6432  -U postgres test_db

Password for user postgres:

psql (15.13)

Type "help" for help.

test_db=#

successfully connect