Django settings when using pgbouncer

Question

I have a Django website with Postgresql backend, for which I'm utilizing pgbouncer for db connection pooling (transaction mode).

The application and the DB reside on separate servers (1 server each). I have installed pgbouncer on the application server. My question is: what should the config be in settings.py? Note that I'm using Unix sockets for connecting to pgbouncer.

---

My current settings.py contains:

DATABASE_URL = 'postgres://user1:pass1@xx.xxx.xxx.xxx:5432/db1'
DATABASES = {
'default': dj_database_url.config(default=DATABASE_URL)
}

Relevant sections of pgbouncer.ini are:

[databases]
db1 = host=xx.xxx.xxx.xxx port=5432 dbname=db1

listen_addr = *
listen_port = 6432
auth_type = md5
unix_socket_dir = /var/run/postgresql
pool_mode = transaction
max_client_conn = 200
default_pool_size = 300

userlist.txt contains:

"user1" "pass1"

---

Note: One answer is here, but doesn't work for me since the DB isn't available locally in my case. I need to set the DATABASE_URL environment variable, instead of using default = '...'.

One suggestions seems to be to treat pgbouncer as a database in settings.py. In that case, would something like the following work?

if PRODUCTION == '1':
    #PRODUCTION is set to '1' if in production environment
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'pgbouncer',
            'USER': 'user1',
            'PASSWORD': 'pass1',
            'HOST': '/var/run/postgresql',
            'PORT': '6432',
        }

Answer 1

From the docs:

pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.

Also,

Have your application (or the psql client) connect to pgbouncer instead of directly to PostgreSQL server.

---

The configurations:

pgbouncer.ini: An example pgbouncer.ini with comments about defaults

[databases]
db1 = host=xx.xxx.xxx.xxx port=5432 dbname=db1

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = userlist.txt
unix_socket_dir = /var/run/postgresql
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

userlist.txt:

"user1" "pass1"

to put in settings.py:

if PRODUCTION == '1':
    #PRODUCTION is set to '1' if in production environment
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'db1',
            'USER': 'user1',
            'PASSWORD': 'pass1',
            'HOST': '/var/run/postgresql',
            # 'PORT': '6432',
        }

---

Extra:

In case not using unix socket - you can set HOST : '127.0.0.1' or 'localhost' if pgbouncer is running locally, or whatever the IP of server pgbouncer will be running on. From the docs:

If you’re using PostgreSQL, by default (empty HOST), the connection to the database is done through UNIX domain sockets (‘local’ lines in pg_hba.conf). If your UNIX domain socket is not in the standard location, use the same value of unix_socket_directory from postgresql.conf. If you want to connect through TCP sockets, set HOST to ‘localhost’ or ‘127.0.0.1’ (‘host’ lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available.

---

In case of postgreSQL For ENGINE you can use postgresql or postgresql_psycopg2 - there's difference between the both given your Django version - postgresql_psycopg2 vs posgresql.

Answer 2

All of your DB settings in settings.py should be identical to the settings in your pgbouncer config, except the host in settings.py will point to pgbouncer. You probably need to change 'NAME': 'pgbouncer' to 'NAME': 'db1'. Since you're using a unix socket the port shouldn't matter.