PgBouncer + Ubuntu 18.04

How to setup PgBouncer on Ubuntu 18.04 LTS

As a project grows there are many reasons as to why we would like to separate the machine that hosts web services from the machine(s) that hosts our production database. In order to handle connections properly and keep connections to a minimum we need to use a pooling service such as pgbouncer.

pgbouncer is lightweight connection pooler for PostgreSQL.

In this short tutorial we are going to explore how we can use PgBouncer to connect to our database more efficiently.

Assumptions made

You have a working machine that is running Ubuntu 18.04 LTS
You already have a Postgres database setup
Our Postgres user in this example is named djangoapp and our password is set to mysupersecretpassword, finally the name of our database is djangoapp

In this tutorial we are going to

Install pgbouncer on Ubuntu 18.04 LTS
Configure pgbouncer on a machine that is separate from the Managed DB machine (Could be hosted on AWS, Google Cloud, MS Azure or DigitalOcean)
Change our settings on the application side to connect to the DB via pgbouncer

Before we get started

Check the response times before
Install, configure and connect to the DB via pgbouncer
Check the response times after

The response times should have decreased drastically after You have setup pgbouncer.

Initally Your database connection string would look something like

DATABASE_URL=postgres://djangoapp:mysupersecretpassword@10.0.0.1:5432/djangoapp

Lets install pgbouncer by executing the command below

sudo apt-get install pgbouncer

2. Check that pgbouncer has been installed properly and that it is running

sudo systemctl status pgbouncer

It should respond with an output that looks similar to the one below

● pgbouncer.service - LSB: start pgbouncer
   Loaded: loaded (/etc/init.d/pgbouncer; generated)
   Active: active (running) since Fri 2019-07-26 04:38:37 +06; 1 months 30 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 2 (limit: 4915)
   CGroup: /system.slice/pgbouncer.service
           └─28515 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Jul 26 04:38:37 postgres01-example-com systemd[1]: Starting LSB: start pgbouncer...
Jul 26 04:38:37 postgres01-example-com pgbouncer[28493]:  * Starting PgBouncer pgbouncer
Jul 26 04:38:37 postgres01-example-com pgbouncer[28493]:    ...done.
Jul 26 04:38:37 postgres01-example-com systemd[1]: Started LSB: start pgbouncer.

3. Make the changes stated below in Your /etc/pgbouncer/pgbouncer.ini

[databases]
djangoapp-bouncer = host=localhost dbname=djangoapp

;; Configuration section
[pgbouncer]
auth_file = userlist.txt

; IP address or * which means all IPs
listen_addr = <LOCAL_NETWORK_IP>, 127.0.0.1
listen_port = 6432

; any, trust, plain, crypt, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; total number of clients that can connect
max_client_conn = 100

; default pool size.  20 is good number when transaction pooling
; is in use, in session pooling it needs to be the number of
; max clients you want to handle at any moment
default_pool_size = 20

❕ NOTE: The <LOCAL_NETWORK_IP> in the example above should preferably be set to Your eth1 interface.

❕ NOTE: You can listen to localhost (127.0.0.1) if the pgBouncer is sitting on the same machine as the Postgres database instance

After You have installed pgbouncer and made the changes stated above Your home/django/.env file should look something like the example below

DATABASE_URL=postgres://djangoapp:mysupersecretpassword@10.0.0.1:6432/djangoapp-bouncer

❕ NOTE: We are now routing calls to our DB via the pgBouncer layer due to the changes in which we send calls via the new port on 6432 and the database proxy created by pgBouncer named djangoapp-bouncer

❕ NOTE: You can be more explicit when it comes to the connection string defined in /etc/pgbouncer/pgbouncer.ini, see below for an example

[databases]
djangoapp-bouncer = host=localhost dbname=djangoapp

is similar to the config below

[databases]
djangoapp-bouncer = host=localhost port=5432 user=djangoapp dbname=djangoapp

❕ NOTE: Make sure You generate a valid userlist.txt file and put it in /etc/pgbouncer/userlist.txt. See below for instructions on how create and populate the userlist.txt file.

Change to the postgres user sudo su postgres
Execute the command below

psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

which should return an output like the one below

"postgres" ""
"djangoapp" "md512345659d2636855c09af03933123456"
"nodeapp" "md512345659d2636123456af03933123456"

3. The output should be added to the userlist.txt file

4. Restart pgbouncer to see that everything is running smoothly with sudo systemctl restart pgbouncer

5. Track what is going on by running sudo tail -f /var/log/postgresql/pgbouncer.log

Test that everything works!

Remember to run sudo systemctl restart pgbouncer everytime You make tweaks in the pgbouncer.ini or related files.

Run sudo tail -f /var/log/postgresql/pgbouncer.log | grep "stats" to see what is happening when pgBouncer is flushed with requests from the Django app.

It should return an output similar to the one below

2019-10-08 19:44:09.968 28515 LOG stats: 37 xacts/s, 66 queries/s, in 65875 B/s, out 93721 B/s, xact 16985 us, query 7694 us, wait 4 us
2019-10-08 19:45:09.981 28515 LOG stats: 31 xacts/s, 45 queries/s, in 50820 B/s, out 82223 B/s, xact 16196 us, query 9692 us, wait 0 us

Try running Siege or Locust to run exact benchmarks on Your Django app to ensure that everything is working correctly and that no requests are failing.

PgBouncer Cheat Sheet

To start/restart/stop or check status of the pgbouncer service You can run the commands below

sudo systemctl start|restart|stop|status pgbouncer

So lets say that You would like to restart the service, just run the command stated below

sudo systemctl restart pgbouncer

To change/update settings You can edit the file /etc/pgbouncer/pgbouncer.ini
To see the logs You can run sudo tail -f /var/log/postgresql/pgbouncer.log

❕ Pro Tip 1: Use django-dotenv to read from Your .env file if You are using Python/Django. There are other packages for other languages that do the same thing.

❕ Pro Tip 2: Use the package dj-database-url in order to read Your database credentials from Your .env file