Below are the pgbouncer files configuration
ls -l /etc/pgbouncer
-rwx------. 1 postgres postgres 824 Jan 10 05:59 mkauth.py
-rw-r--r--. 1 postgres postgres 361 Apr 7 14:46 pgbouncer.ini
-rw-r--r--. 1 postgres postgres 825 Apr 7 14:40 userlist.txt
edit pgbouncer.ini with below parameter
[databases]
powerfulldb = port=5432 dbname=powerfulldb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
unix_socket_dir = /tmp
unix_socket_mode = 0777
login to psql and run below statment
SELECT '"' || rolname || '" "' || rolpassword || '"' as userlist
FROM pg_shadow
WHERE rolpassword IS NOT NULL;
Below is the example result and must to paste to this file /etc/pgbouncer/userlist.txt
userlist
--------------------------------------------------------
"admin1" "md5c1d7c7825caaac2441xc633a274b45ea"
"admin2" "md525e4b33435daa2f8ebx1664dd7df2aab"
"admin3" "md5a99ea413e7dea3bd19xd760507bcec00"
restart the pgbouncer service
systemctl restart pgbouncer.service
then login to the database using pgboncher port
psql -p 6432 -U admin1 -h 10.10.20.100 -d powerfulldb