Setup pgBouncer on Django Server

• Install pgBouncer on Django Server:

sudo apt install -y pgbouncer
# The Debian package runs PgBouncer as the postgres user, which can't read your home folder
sudo install -o postgres -g postgres -m 644 ~/.postgresql/root.crt /etc/pgbouncer/root.crt

• Get the password hash by running this on the database server:

sudo -u postgres psql -Atc "SELECT format('\"%s\" \"%s\"', rolname, rolpassword) FROM pg_authid WHERE rolname = '<your-db-username>';"

• Paste the output line from above command into /etc/pgbouncer/userlist.txt on the Django server, then set its owner to postgres and its mode to 600.
  • Confirm the output secret starts with SCRAM-SHA-256$.
  • The line holds the SCRAM hash, not the password itself. PgBouncer uses the hash both to check Django's login and to log in to PostgreSQL.

sudo vi /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt && sudo chmod 600 /etc/pgbouncer/userlist.txt

• Configure pgBouncer using sudo vi /etc/pgbouncer/pgbouncer.ini:

[databases]
<your-database-name> = host=<hostname-or-IP-covered-by-your-certificate> port=5432 dbname=<your-database-name>

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
; Maximum PostgreSQL connections per database/user pair
default_pool_size = 50
; real connections to PostgreSQL - keep well under max_connections (100), to give room for other applications and instances
max_db_connections = 50


; TLS from PgBouncer to PostgreSQL
server_tls_sslmode = verify-full
server_tls_ca_file = /etc/pgbouncer/root.crt

• Also, on the database server, run this SQL:
  • When Django opens a connection, it runs SET TIME ZONE if the database's timezone doesn't match its own. With transaction pooling, that setting stays on the shared server connection and carries over to other clients. With the role already set to UTC, Django skips the command. This assumes USE_TZ = True, which is Django's default.

-- Use `sudo -u postgres psql -d postgres`
ALTER ROLE <your-db-username> SET timezone TO 'UTC';

• Enable and restart pgBouncer:

sudo systemctl enable pgbouncer # Start automatically after reboot
sudo systemctl restart pgbouncer

• Update Django project's settings.py file:

USE_PGBOUNCER = os.getenv("POSTGRES_USE_PGBOUNCER", "false").lower() == "true"

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.getenv("POSTGRES_DATABASE"),
        "HOST": os.getenv("PGBOUNCER_HOST") if USE_PGBOUNCER else os.getenv("POSTGRES_HOST"),
        "PORT": os.getenv("PGBOUNCER_PORT") if USE_PGBOUNCER else os.getenv("POSTGRES_PORT"),
        "USER": os.getenv("POSTGRES_USER"),
        "PASSWORD": os.getenv("POSTGRES_PASSWORD"),
        "CONN_MAX_AGE": 600,
        "CONN_HEALTH_CHECKS": True,
        "DISABLE_SERVER_SIDE_CURSORS": USE_PGBOUNCER,
        "OPTIONS": (
            {"sslmode": "disable"}
            if USE_PGBOUNCER
            else {
                "sslmode": "verify-full",
                "sslrootcert": os.getenv("POSTGRES_SSLROOTCERT", "/home/piratedev/.postgresql/root.crt"),
            }
        ),
    }
}

• After restarting the web server, confirm changes by running following code in Django shell:

from django.db import connection

# Confirm Django targets your local PgBouncer.
db = connection.settings_dict
print("Database target:", db["HOST"], db["PORT"])
assert (db["HOST"], str(db["PORT"])) == ("127.0.0.1", "6432"), \
    "Django is not configured to use local PgBouncer"
assert db.get("DISABLE_SERVER_SIDE_CURSORS") is True, \
    "Disable server-side cursors for transaction pooling"

# Execute a real query and check the upstream PostgreSQL connection.
with connection.cursor() as cursor:
    cursor.execute("""
        SELECT current_database(), current_user, ssl
        FROM pg_stat_ssl
        WHERE pid = pg_backend_pid()
    """)
    result = cursor.fetchone()

print("PostgreSQL database, user, TLS:", result)
assert result and result[2] is True, "PostgreSQL connection is not using TLS"
print("PgBouncer/PostgreSQL: OK")