PG Bouncer + Odoo Installation Steps

sudo apt install pgbouncer

Edit the pgbouncer configuration file /etc/pgbouncer/pgbouncer.ini

  * = host=localhost port=5432
  Set the listen_port = 5432 and listen_address = * 
  Set the auth_type = md5.

Run below query in psql

COPY ( SELECT '"' || rolname || '" "' ||  CASE WHEN rolpassword IS null THEN '' ELSE rolpassword END || '"'
FROM pg_authid ) TO '/etc/pgbouncer/userlist.txt';

Note :- Ensure that you update this file every time a new db user is created.

Change the db_port in Odoo config to listen_port (5432)

Restart Pgbouncer & then Odoo

More options: https://pgbouncer.github.io/config.html Official doc: https://wiki.postgresql.org/wiki/PgBouncer

Don't forget to check the log file /var/log/postgresql/pgbouncer.log.

Testing with pgbench:

Init pgbench tables

pgbench --host localhost --port 5435 -U odoo dbname -i

Run pgbench test command:

WIth pgbouncer:

pgbench --host localhost --port 5435 -U odoo -t 20 -c 100 -S -C app

starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 20
number of transactions actually processed: 2000/2000
latency average = 847.001 ms
tps = 118.063545 (including connections establishing)
tps = 119.237957 (excluding connections establishing)

without bgboucr:

pgbench --host localhost --port 5435 -U odoo -t 20 -c 100 -S -C app

starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 20
number of transactions actually processed: 2000/2000
latency average = 3003.772 ms
tps = 33.291470 (including connections establishing)
tps = 33.626995 (excluding connections establishing)

Docker Compose Example

version: '2'
services:
  web:
    image: odoo:12
    depends_on:
      - pgbouncer
    ports:
      - "8069:8069"
    volumes:
      - ./data:/var/lib/odoo
      - ./config:/etc/odoo
      - ./addons:/mnt/extra-addons
      - ./logs:/var/log/odoo
  db:
    image: postgres:12
    ports:
      - "5435:5432"
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_PASSWORD=odoo
      - POSTGRES_USER=odoo
      - PGDATA=/var/lib/postgresql/data/pgdata
    volumes:
      - ./db-data:/var/lib/postgresql/data/pgdata

  pgbouncer:
    image: edoburu/pgbouncer:latest
    depends_on:
      - db
    ports:
      - "5431:5432"
    environment:
      - DB_HOST=db
      - DB_USER=odoo
      - DB_PASSWORD=odoo
      - MAX_CLIENT_CONN=200

akhdaniel/PG Bouncer + Odoo Installation Steps.md

Docker Compose Example