1. b.93z.org
  2. Notes

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:

; fallback connect string
* = host=

Set host and port:

listen_addr =
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).

© 2008–2017 93z.org