Using pgpool2 to timeout idle Postgres connections from Django

Django has a well known issue with "<IDLE> in transaction" queries piling up. I have found that simply "<IDLE>" queries also have a tendency to hang out. This can cause problems for a high traffic site, as you can run out of open connections slots on the database side.

To see if you're affected, you can run a database query to see the status of open connections.

>select count(*), current_query from pg_stat_activity group by current_query;
 count |                                current_query                                 
-------+------------------------------------------------------------------------------
    31 | <IDLE> in transaction
    54 | <IDLE>
     1 | select count(*), current_query from pg_stat_activity group by current_query;
(3 rows)

>SHOW max_connections;
125

In this case, you can see that 86 out of a possible 125 connections are open, and there is only one "real" query running. After a while, you might start seeing the OperationalError: FATAL: sorry, too many clients already error from Postgres, by way of Django.

Why are these connections hanging out? Maybe you're opening a transaction and not closing it. In my case, I have no idea. But I don't really care either, I just want them closed after say 90 seconds. Surprisingly, there is no configuration option in Django or Postgres for this. Instead, you have to rely on a Postgres add-on like pgpool2.

Here are my notes for installing pgpool2 on Ubuntu, fixing a bug in the Ubuntu 11.04 init script and configuring a 90 second connection timeout. In this example, I'm installing pgpool2 on the Postgres database server itself, and having pgpool2 take over post 5432, moving Postgres itself to 5431 (to make it a seamless transition as far as Django config is concerned).

apt-get install pgpool2 

vim /etc/init.d/pgpool2 
PIDFILE=/var/run/pgpool/pgpool.pid 

vim /etc/pgpool.conf 
port = 5432 
connection_life_time = 90 
client_idle_limit = 90 
backend_hostname0 = 'localhost' 
backend_port0 = 5431 
backend_weight0 = 1 

vim /etc/postgresql/8.4/main/postgresql.conf 
port = 5431 

/etc/init.d/postgresql-8.4 stop 
/etc/init.d/postgresql-8.4 start 
service pgpool2 restart

Chase Seibert