0

I tried implementing pgbouncer and found unusual behavior that I cannot solve.

Here is my implementation using docker.

version: '3.5'

services:
  dev8_web:
    container_name: dev8_web_1

    image: xcgd/odoo:8.0

    depends_on:
        - dev8_pgbouncer

    restart: unless-stopped

    command: "start"

  dev8_pgbouncer:
    container_name: dev8_pgbouncer_1

    depends_on:
      - dev8_db
    restart: unless-stopped
    image: pgbouncer/pgbouncer

    env_file:
      - env/pgbouncer.env

  dev8_db:
    container_name: dev8_db_1

    image: postgres:12.7

    restart: unless-stopped

    volumes:
      - ./db:/var/lib/postgresql/data

    environment:
      - POSTGRES_PASSWORD=odoo
      - POSTGRES_USER=odoo
      - POSTGRES_DB=postgres # Leave this set to postgres

Here is my pgbouncer.env

DATABASES_HOST=dev8_db
DATABASES_PORT=5432
DATABASES_USER=odoo
DATABASES_PASSWORD=odoo
DATABASES_DBNAME=postgres

Here is my odoo.conf application configuration file

# -*- coding: ascii -*-

[options]

admin_passwd = top-secret-password

# |--------------------------------------------------------------------------
# | Port Options
# |--------------------------------------------------------------------------
# |
# | Define the application port and longpolling ports.
# |

xmlrpc_port = 8069

# |--------------------------------------------------------------------------
# | Database Configurations
# |--------------------------------------------------------------------------
# |
# | Database configurations that setup Odoo to connect to a
# | postgresql database.
# |

db_host = dev8_pgbouncer
db_user = odoo
db_password = odoo
db_port = 6432

addons_path = /opt/odoo/sources/odoo/addons,/mnt/logic-addons,/mnt/data-addons

proxy_mode = True

Issues I have encountered

When I created a database and initialized the database (created all the necessary tables for the application) everything works as intended.

Things start getting weird when I tried to delete the database. The database was deleted, I can no longer see it in psql \l. But when I tried to create a new database using the same name with the previous database, I got all the previous tables without even having to initialize.

And I got all the previous data inside the tables too.

In further inspection I checked the database size using psql \l+ it showed only 8MB.

And when I tried to display all tables inside the database using psql \dt it says

No relations found

I have checked the schema and I only have public schema.

In an implementation without pgbouncer the size of an initialized database should be a little over 100MB.

I tried checking the postgresql data folder and it is located correctly in the volume I mounted.

Can anyone explain why this issue occurred and how can I have a transparent pgbouncer implementation without having this issue?

strike_noir
  • 4,080
  • 11
  • 57
  • 100
  • You should show how pgBouncer is set up and how you connect to the database. – Laurenz Albe Aug 30 '21 at 07:08
  • @LaurenzAlbe Hi I have added my configuration file to connect from odoo to pgbouncer. Basically it only changes the postgresql port from 5432 to 6432 and the db_host from dev8_db to dev8_pgbouncer. – strike_noir Aug 30 '21 at 08:03
  • I see no database configured in pgBouncer. – Laurenz Albe Aug 30 '21 at 08:10
  • I put pgbounncer configuration inside pgbouncer.env which I have attached in the question. The databases_dbname = postgres. Should I change this to the database name of my application? – strike_noir Aug 30 '21 at 08:21

0 Answers0