Setting Up PgBouncer with Odoo on a Linux Server

Introduction

PgBouncer helps Odoo scale by reducing the load on PostgreSQL using connection pooling. Here’s a concise setup guide for Ubuntu-based systems.

Why is it needed?

PostgreSQL doesn’t handle a high number of connections efficiently, this causes memory bloat and slower performance.

PgBouncer is a connection pooler. Instead of each Odoo session opening a new connection to PostgreSQL, PgBouncer keeps a small pool of connections alive and reuses them.

Requirements

• Linux server (e.g., Ubuntu 20.04+)

• PostgreSQL installed

• Odoo installed (odoo as DB user)

• Sudo/root access

Install PgBouncer

bash

sudo apt-get update sudo apt-get install pgbouncer

Configure PostgreSQL

Edit pg_hba.conf:

conf

host all odoo 127.0.0.1/32 md5

Set max connections:Edit postgresql.conf:

conf

max_connections = 100

bash

sudo systemctl restart postgresql 

Create PgBouncer Userlist

bash

sudo -u postgres psql -c "COPY (SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname = 'odoo') TO '/etc/pgbouncer/userlist.txt';" sudo chown postgres:postgres /etc/pgbouncer/userlist.txt sudo chmod 640 /etc/pgbouncer/userlist.txt

Configure PgBouncer

Edit /etc/pgbouncer/pgbouncer.ini:

ini

[databases] * = host=localhost port=5432 dbname=odoo auth_user=odoo [pgbouncer] listen_port = 6432 listen_addr = 127.0.0.1 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres pool_mode = transaction max_client_conn = 200 default_pool_size = 18 reserve_pool_size = 10

Set permissions:

bash

sudo chown postgres:postgres /etc/pgbouncer/ -R sudo chmod 640 /etc/pgbouncer/pgbouncer.ini

Add PgBouncer as a Service

Create /usr/lib/systemd/system/pgbouncer.service:

ini

[Unit] Description=A lightweight connection pooler for PostgreSQL After=network.target [Service] User=postgres Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer.ini ExecStart=/usr/bin/pgbouncer -q ${BOUNCERCONF} ExecReload=/usr/bin/pgbouncer -R -q ${BOUNCERCONF} RemainAfterExit=yes [Install] WantedBy=multi-user.target

Enable and start:

bash

sudo systemctl daemon-reload sudo systemctl enable pgbouncer sudo systemctl start pgbouncer

Configure Odoo to Use PgBouncer

Edit /etc/odoo.conf:

ini

[options] db_host = 127.0.0.1 db_port = 6432 db_user = odoo db_password = your_odoo_db_password db_maxconn = 64 workers = 8

bash

sudo systemctl restart odoo

Handle Long-Polling

Create /etc/odoo-longpoll.conf:

ini

[options] db_host = 127.0.0.1 db_port = 5432 db_user = odoo db_password = your_odoo_db_password gevent_port = 8072 workers = 0

Use a separate instance for long-polling or proxy /websocket/ to port 8072.

Test Everything

Test PgBouncer Connection:

bash

psql -U odoo -h 127.0.0.1 -p 6432 -d odoo

Monitor Stats:

sql

SHOW STATS; SHOW POOLS;

Optional Load Testing

bash

sudo apt install postgresql-contrib pgbench --host localhost --port 6432 -U odoo -t 20 -c 100 -S -C odoo

Troubleshooting

• Check logs: /var/log/pgbouncer/pgbouncer.log

• Create PID directory if needed:

bash

sudo mkdir -p /var/run/pgbouncer sudo chown postgres:postgres /var/run/pgbouncer

• Update userlist.txt if odoo password changes.

Performance Benefits

• Reduced memory usage

• Faster query response

• Scales well for 50+ users and 8 workers