How to setup Pgbouncer

PostgreSql needs a middleware program to handle application connections also known as connection pooling. One of the most common such middleware programs is called, Pgbouncer. This blog post will highlight the changes you need to make to setup Pgbouncer so that application can connect to PostgreSql via Pgbouncer.

I tested this with Postgres 12 and Pgbouncer 1.13.0 version on CentOS 7. In this scenario, pgbouncer and PostgreSQL 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 password before 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.

psql$ 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.

Edit Pgbouncer.ini

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.

[databases]
testdb = host=127.0.0.1 port=5432 dbname=testdb user=testuser

[pgbouncer]
auth_type = md5
listen_addr = *
listen_port = 6432
auth_file = /etc/pgbouncer/userlist.txt

Adding Users

Create 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 pgbouncer
$ systemctl restart postgresql-12.service

How To Test

You can use psql to test pgbouncer setup like below;

psql -h 127.0.0.1 -p 6432 -U testuser testdb

SCRAM Secrets

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 this link very useful to implement encrypted passwords.

This is just one method of configuring pgbouncer. There are few more ways to configure pgbouncer.

About the post header picture: Sunset from the High Line Canal trail on Jun,25th 2020.