How to set up PgBouncer with PostgreSQL 8.4 and Django 1.4 on Debian

Django opens database connection at the start of request, and closes it at the end of response: it’s not a secret. Authors of framework believe that pooling belongs to external tools, not ORM, that’s why “big” Django projects use connection poolers to avoid overhead.

PgBouncer is a lightweight and easy to use connection pooler for PostgreSQL. It is stable and production-ready (e.g. Disqus and Skype use it).

First, install it via APT:

$ sudo apt-get install pgbouncer

Then edit /etc/pgbouncer/pgbouncer.ini:

auth_type = md5
auth_file = /var/lib/postgresql/8.4/main/global/pg_auth

pg_auth file looks like this:

"user1" "md5 of password" ""
"user2" "md5 of password" ""
"user3" "md5 of password" ""

This format is specific to PostgreSQL < 9.0, but it is solved problem: there is Python script to generate such file from pg_shadow table (PostgreSQL 9.*).

Also change server_reset_query:

server_reset_query = DISCARD ALL;

If you want, you can change pool_mode, but following is default (and offers full transparency):

pool_mode = session

If you don’t know what to put into databases section, make it look like this:

[databases]
; fallback connect string
* = host=127.0.0.1

Set host and port:

listen_addr = 127.0.0.1
listen_port = 6432

Of course there are other settings, but it is sufficient to use mentioned ones, say, to try PgBouncer on your machine. Note that I didn’t write anything about logfile, pidfile, and other settings that are already defined in default config on Debian.

Now, edit /etc/default/pgbouncer and set START to 1 (it is 0 by default). Finally, start PgBouncer:

$ sudo /etc/init.d/pgbouncer start

By default log is located at /var/log/postgresql/pgbouncer.log. There is bunch of useful settings (in /etc/pgbouncer/pgbouncer.ini) related to logging:

log_connections = 1
log_disconnections = 1

; log error messages pooler sends to clients
log_pooler_errors = 1
stats_period = 60

If you find log too verbose, you can set first two to zeroes, and increase last one. Last one, that is, stats_period, defines how often PgBouncer will log statistics that looks like this:

2012-09-25 00:00:00.000 12345 LOG Stats: N req/s, in N b/s, out N b/s,query N us

If you changed configuration when PgBouncer is already running, reload it:

$ sudo /etc/init.d/pgbouncer reload

All you need to change in Python code is PORT setting (corresponds to listen_port in pgbouncer.ini).