If your Odoo instance is running slow under heavy load, or your PostgreSQL server keeps throwing “too many clients” errors, there’s a very good chance the real problem isn’t your server; it’s how database connections are being managed. That’s exactly where PgBouncer with Odoo comes in.
In this guide, we’ll walk you through everything you need to know — what PgBouncer actually is, why Odoo needs it, and how to set it up step by step on Ubuntu. By the end, your Odoo server will be handling database connections much more efficiently, especially under concurrent user load.
Let’s get started.
Table of Contents
What is PgBouncer?
PgBouncer is a lightweight, open-source connection pooler for PostgreSQL. Its job is simple: instead of letting every application request open a brand new connection to the database, PgBouncer maintains a small pool of already-open connections and reuses them.
Think of it like a reception desk. Without PgBouncer, every new visitor (database request) walks directly into the office (PostgreSQL), takes a seat, gets served, and then leaves. Creating that seat for every single visitor is expensive and slow. With PgBouncer, there is a fixed number of seats that keep getting reused. Visitors get served faster, and the office never gets overwhelmed.
For high-traffic Odoo deployments, this makes a massive practical difference.
Why Does Odoo Need PgBouncer?
Here’s something most Odoo setup guides don’t explain clearly: Odoo already has its own internal connection pooling. So why add PgBouncer on top of it?
Odoo’s built-in pool works at the process level. Each Odoo worker process maintains its own connection pool, limited by the db_maxconn setting. In practice, every Odoo worker ends up holding 2 to 3 open database connections even when it’s doing absolutely nothing.
So let’s do the math. If you have 10 Odoo HTTP workers, that’s potentially 30 database connections sitting open at all times just for one Odoo instance. Add a few more Odoo instances, or bump up your worker count for scale, and you can easily reach 100, 200, or even 300+ idle connections eating into your server’s RAM.
PostgreSQL handles this badly. Every connection it opens forks a new process on the server. Hundreds of idle connections = hundreds of idle processes = massive memory waste. Eventually PostgreSQL hits max_connections and throws:
FATAL: sorry, too many clients alreadyThat error means your Odoo is broken for every user until something frees up a connection.
Using PgBouncer with Odoo solves this by placing a connection manager between Odoo and PostgreSQL. Odoo workers still think they have their own connections, but those connections actually point to PgBouncer, which maps them to a much smaller number of real PostgreSQL connections. Result: less RAM usage, fewer idle processes, and no more “too many clients” crashes.
When Should You Use PgBouncer with Odoo?
PgBouncer isn’t always necessary. Here’s when it actually makes sense to set it up:
- You have 25 or more concurrent users actively using Odoo
- You’re running multiple Odoo instances on the same database server
- You’re seeing “too many clients” errors in your PostgreSQL logs
- Your server is running out of RAM due to idle PostgreSQL processes
- You’re running Odoo with a significant number of workers configured
If you’re running a small Odoo install with fewer than 15 active users and no connection errors, PgBouncer is optional. It adds a small layer of overhead that isn’t worth it for light-load setups.
PgBouncer Pool Modes Explained
Before jumping into setup, you need to understand how PgBouncer manages connections. It supports three pooling modes, and choosing the right one for Odoo matters a lot.
Session Mode: One server connection is held per client session for the entire duration of the client’s connection. This is the safest mode and fully compatible with Odoo, but it doesn’t save as many connections.
Transaction Mode: A server connection is only held during an active transaction. Once the transaction completes, the connection goes back to the pool. This is the most efficient mode and the one recommended for use with PgBouncer with Odoo. However, it has one important limitation: Odoo’s longpolling feature (which uses LISTEN/NOTIFY) is not compatible with transaction mode. We’ll cover how to handle this below.
Statement Mode: Releases the connection after each SQL statement. This is not compatible with Odoo at all because Odoo wraps everything in transactions.
Recommended mode for Odoo: pool_mode = transaction
How to Install PgBouncer on Ubuntu
Requirements before you start:
Ubuntu 20.04 or later
PostgreSQL installed and running
Odoo installed with a working database
Root or sudo accessStep 1: Install PgBouncer
sudo apt-get update
sudo apt-get install pgbouncer -yStep 2: Verify Installation
pgbouncer --versionYou should see the PgBouncer version printed in your terminal. If you do, the installation was successful.
How to Configure PgBouncer for Odoo
You have successfully installed the pgbouncer on your system. Now we’ll go over how to configure PgBouncer for Odoo
Step 1: Generate the userlist.txt file
PgBouncer requires the database user credentials to authenticate connections. Run this command inside psql as the postgres user:
sudo -u postgres psqlThen run this SQL query:
COPY (
SELECT '"' || rolname || '" "' ||
CASE WHEN rolpassword IS NULL THEN '' ELSE rolpassword END || '"'
FROM pg_authid
WHERE rolname = 'odoo'
) TO '/etc/pgbouncer/userlist.txt';Then exit psql and fix file permissions:
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txtImportant: Every time you change the Odoo database user’s password, you must regenerate this file.
Step 2: Edit the PgBouncer Configuration File
Open the main pgbouncer config file:
sudo nano /etc/pgbouncer/pgbouncer.iniReplace the contents with the following configuration:
[databases]
* = host=localhost port=5432
[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 = 20
reserve_pool_size = 10
server_reset_query = DISCARD ALLHere’s what the key settings mean:
listen_port = 6432: PgBouncer will listen on port 6432 (Odoo will connect here instead of 5432)pool_mode = transaction: Most efficient mode for Odoomax_client_conn = 200: Maximum number of client connections PgBouncer will acceptdefault_pool_size = 20: Number of actual PostgreSQL connections per database/user pairreserve_pool_size = 10: Extra connections available when the pool is fullserver_reset_query = DISCARD ALL: Resets session state between reuses in transaction mode
Step 3: Create Required Directories
sudo mkdir -p /var/run/pgbouncer
sudo chown postgres:postgres /var/run/pgbouncer
sudo mkdir -p /var/log/pgbouncer
sudo chown postgres:postgres /var/log/pgbouncerStep 4: Start and Enable PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status pgbouncerIf the status shows active (running) PgBouncer is up and listening on port 6432.
How to Update odoo.conf to Use PgBouncer
Now you need to tell Odoo to connect via PgBouncer rather than directly to PostgreSQL. Open your Odoo config file:
sudo nano /etc/odoo/odoo.conf #Path can be change so choose according to yoursUpdate the database connection settings:
[options]
db_host = 127.0.0.1
db_port = 6432
db_user = odoo
db_password = your_odoo_db_password
db_maxconn = 64
workers = 8The critical change is db_port = 6432: This is PgBouncer’s port, not PostgreSQL’s default 5432. All Odoo database requests will now go through PgBouncer first.
Restart Odoo to apply the changes:
sudo systemctl restart odooHandling the Longpolling Issue
This is the one important caveat when using PgBouncer with Odoo in transaction mode.
Odoo’s longpolling feature (used for live chat, real-time notifications, and the bus messaging system) relies on PostgreSQL’s LISTEN statement. The LISTEN command needs to keep a persistent server connection open, which is fundamentally incompatible with transaction pooling mode, where connections are released after every transaction.
The solution is to run a separate Odoo instance for longpolling that connects directly to PostgreSQL (bypassing PgBouncer), while your main Odoo workers continue using PgBouncer.
Create a separate config file for the longpolling worker:
sudo nano /etc/odoo-longpoll.conf[options]
db_host = 127.0.0.1
db_port = 5432
db_user = odoo
db_password = your_odoo_db_password
gevent_port = 8072
workers = 0Notice db_port = 5432 Here, this instance talks directly to PostgreSQL, bypassing PgBouncer entirely. Then, in your Nginx configuration, route /websocket and /longpolling/poll traffic to port 8072 while the rest goes to port 8069 through PgBouncer.
How to Test PgBouncer is Working
Now you have successfully configured the pgbouncer with Odoo. Now, it’s time to test if PGBouncer is working or not.
Test 1: Check the connection
psql -U odoo -h 127.0.0.1 -p 6432 -d your_database_nameIf this connects successfully, PgBouncer is properly routing connections to PostgreSQL.
Test 2: Check Pool Stats
Connect to PgBouncer’s admin console:
psql -U postgres -h 127.0.0.1 -p 6432 pgbouncerThen run:
SHOW STATS;
SHOW POOLS;This gives you live data on active connections, idle connections, and queries per second. If you see your Odoo database listed with active client connections, everything is working.
Test 3: Check the Log
tail -f /var/log/pgbouncer/pgbouncer.logWatch the logs while you use Odoo. You should see connections being established and released cleanly.
Related Posts
- What is Odoo Shell and How to Access It in Odoo
- How to Configure Odoo with Nginx as a Reverse Proxy
- What is Odoo Runbot and How to Access It?
Common Errors and Fixes in PgBouncer with Odoo
- Error:
FATAL: password authentication failed: This usually means youruserlist.txtfile is outdated or the password format is wrong. Regenerate it using the SQL query from Step 3 and restart PgBouncer. - Error:
could not connect to server: Check that PgBouncer is actually running withsudo systemctl status pgbouncer. Also verify thatlisten_addrinpgbouncer.iniis set to127.0.0.1and thatdb_hostinodoo.confmatches. - Error:
too many clients: Increasemax_client_conninpgbouncer.ini. Also check thatdefault_pool_sizeis not set too low for your number of Odoo workers. - Odoo shows loading forever (Longpolling broken): You’re likely running longpolling through PgBouncer in transaction mode. Set up the separate longpolling config described above and route
/websocketdirectly to port 8072.
Final Thoughts
Using PgBouncer with Odoo is one of the most effective performance improvements you can make for a production Odoo server running with multiple users. It won’t make individual queries run faster; that’s query optimization’s job, but it dramatically reduces the overhead of managing database connections, frees up RAM, and prevents the dreaded “too many clients” crash that can take your entire Odoo instance offline.
Set it up right once, handle the longpolling edge case carefully, and your database layer will be far more stable under real-world load.
Frequently Asked Questions
Q1. What is PgBouncer with Odoo in simple terms?
PgBouncer acts as a middleman between Odoo and PostgreSQL. Instead of Odoo opening new database connections every time a user does something, PgBouncer reuses a shared pool of connections, saving memory and improving performance under high load.
Q2. Does PgBouncer make Odoo queries faster?
Not directly. PgBouncer doesn’t change how SQL queries run. What it does is reduce connection overhead so queries reach the database faster, especially when many users are active simultaneously.
Q3. Which pool mode should I use for Odoo?
Use pool_mode = transaction. It’s the most efficient mode for Odoo’s workload pattern. Just make sure to handle the long-polling issue with a separate direct connection to PostgreSQL.
Q4. Is PgBouncer compatible with all Odoo versions?
Yes. PgBouncer works with Odoo 12, 14, 15, 16, and 17. The configuration approach is the same across versions, though Odoo 16+ uses gevent_port a different approach instead of the deprecated longpolling_port option.
Q5. What port does PgBouncer use with Odoo?
PgBouncer listens on port 6432 by default. You update db_port = 6432 in your odoo.conf file to route Odoo’s database connections through PgBouncer instead of directly to PostgreSQL on port 5432.
Q6. Can I use PgBouncer with Odoo on Docker?
Yes. You can add PgBouncer as a separate service in your docker-compose.yml, using the edoburu/pgbouncer image. Set DB_HOST, DB_USER, DB_PASSWORD, and MAX_CLIENT_CONN environment variables, then point your Odoo services db_port to PgBouncer’s exposed port.
Have a question about PgBouncer with Odoo? Leave a comment below.
