How to correctly setup odoo with postgres9.5 and pgbouncer?

Samo Arko

I'm running odoo 10 on a ubuntu 10.04 server with postgresql 9.5 database. Because multiple odoo instances and configured workers I have 300+ idle postgres tasks. This eats up nearly all my RAM. Now I'm trying to install pgbouncer. When I lunch the pgbouncer service the first time it works, but stop/start or just restart fails and so I can't run the pgbouncer.

I've installed it from git and from apt-get. Both times the same. Have googled for two days to try to fix the issue but nothing works.

Can someone explain me the order what you need to do when you install pgbouncer that it works. Do I need to install it before the db?! do I need to stop the odoo, postgres services when I install it?

The way I tried:

sudo apt-get update
sudo apt-get install pgbouncer

# write the users
sodu -u postgres psql
COPY ( SELECT '"' || rolname || '" "' || CASE WHEN rolpassword IS null THEN '' ELSE rolpassword END || '"' FROM pg_authid ) TO '/etc/pgbouncer/userlist.txt';

# sudo nano pgbouncer.ini
[databases] ;* = port=5432 postgres = host=localhost dbname=postgres [pgbouncer] listen_port = 6432 listen_addr = auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/ admin_users = postgres pool_mode = session server_reset_query = DISCARD ALL default_pool_size=56 max_client_conn=1000

# and then change the db_port to 6432 in odoo.conf

when I started the first time it worked... all the other times I get!

 Hope someone can help! 

2 Answers

Pgbouncer is quite tricky you have to keep checking the errors in the logs and clear them one by one.

test your setup first open your terminal and start with reducing your pgbouncer to the bare minimal:

* = port=5432 postgres = host=localhost dbname=postgres

listen_port = 6432
listen_addr =
auth_file = /etc/pgbouncer/userlist.txt

save and try to activate the connection

$ /usr/sbin/pgbouncer  pgbouncer.ini

most probably you will have some errors post them will see what it is

if no errors show on the screen and pgbouncer is started do a connection test from a second terminal, following your settings type the following:

psql -U postgres -p 6432 -h localhost dbname=postgres

it should show you the connection problem and what needs to be fix, it could be 1 of 3 things or the 3 all together, the userlist, the IPs not properly configure or the authorization.

Once you have clear all the errors from the files and postgres configuration you can go ahead and start adding settings to pgbouncer, keep in mind this apps have a lot of interactions going on but the base needs to be clear to work out.

1 Comment
Samo Arko

thanks, but I gave up on pgbouncer.

Roland Lee
Best Answer

Have you tried out the Heimdall Proxy? It supports 1) Query caching, ACID-Compliant Read/Write splitting, and 3) Connection Pooling.

To be clear, pg-bouncer or pg-Pool-II will not work with Odoo as queries are in transactions and query splitting requires strong consistency. The Heimdall Proxy was specifically designed for the Odoo application.