I tested this with Postgres 12 and Pgbouncer 1.13.0 version on CentOS 7. In this scenario, pgbouncer and Postgres are running on the same server.
Once you installed Pgbouncer, the configuration files are available on /etc/pgbouncer dir. Pgbouncer config file is, pgbouncer.ini.
Get username and passwordBefore you start changing the config file, you need to find out Postgres user(s) and the database(s) which you need for the connection pooling. In Postgres cmd prompt(psql), execute the code below to get those info.
select usename, passwd from pg_shadow;You need both username and the passwd (please note, this password is based on md5) If you need more secure password, you'll have to follow few additional steps. I will get back to that later in this post.
Open Pgbouncer.ini file in a text editor (vi). Make the changes as shown below; Assume the database name is testdb and the username is testuser.
testdb = host=127.0.0.1 port=5432 dbname=testdb user=testuser
auth_type = md5
listen_addr = *
listen_port = 6432
auth_file = /etc/pgbouncer/userlist.txt
Note: 6432 is the default port of pgbouncer.
Adding UsersCreate userlist.txt file and add username and password in the file as stated below;
"testuser" "md590828d6f73b12acc3866c26968bde150"Add all the users in this file which connects from the applications.
At this point, the very basic setup of Pgbouncer is completed. You can restart pgbouncer service and probably the Postgres service too.
systemctl restart pgbouncersystemctl restart postgresql-12.service
How To TestYou can use psql to test pgbouncer setup like below;psql -h 127.0.0.1 -p 6432 -U testuser testdb
Pgbouncer 1.14 version (Jun-11,2020) supports encrypted SCRAM secrets for enhanced security. So that you can avoid use of md5 or plain text passwords. I found below link very useful to implement encrypted passwords.
This is just one method of configuring pgbouncer. There are few more ways to configure pgbouncer.