Friday, March 26, 2010

HOWTO setup pgBouncer on Debian Part 1

When looking to provide database connections under a steady (or heavy) load, you will likely need to look into a database connection pooler. There are a number of good options for PostgreSQL such as pgBouncer, pgpool-II, SQL Relay, and a few others.
My personal favorite as of late is pgBouncer. Here's a few reasons why I like it:
  • a lightweight connection pooler--it is designed solely for pooling
  • low resource requirement
  • written with Python (a lot of our inernal apps use Python, so interoperability is great to have)
  • supports online restart/upgrade without dropping client connections
When setting up a pooler, you will want to have a separate server just for the pooler. Adding a pooler to the same server as the database only degrades the performance of PostgreSQL, so make sure you don't.

I use Debian as my choice of Linux distro. Unfortunately, there isn't an official package to install on Lenny. However, thanks to good ol' backports, we can find an up-to-date package. First things first, add backports to your /etc/apt/sources.list by adding the following source:
deb lenny-backports main contrib non-free

Next, you will want to do aptitude update (or apt-get update)

You can learn more about how to use backports on their instructions page.

Finally, here's how to do an install of pgBouncer:
$ aptitude -t lenny_backports install pgbouncer

No comments: