document connection to host Postgres

[rvalyi]

WORK IN PROGRESS I got a great boost using the host Postgres along with openupgrade and in general I think it's great to document how to do that.

I used advices from here https://gist.github.com/MauricioMoraes/87d76577babd4e084cba70f63c04b07d

[rvalyi]

key points:

• you should install Postgres on the host

• your postgresql.conf needs listen_addresses = '*'

• your docker-compose.yml file should needs no db service or dependency to a db service

• instead you need to set these ENV var for your odoo service:

    - DB_USER=odoo
    - DB_HOST=<HOST-IP-FROM-CONTAINER>

  where you can obtain HOST-IP-FROM-CONTAINER with /sbin/ip route and then 1st line or line with docker0 for detail see: https://stackoverflow.com/questions/22944631/how-to-get-the-ip-address-of-the-docker-host-from-inside-a-docker-container

• then you also need to enable your container to access Postgres. In /etc/postgresql/10/main/pg_hba.conf add a line like: host all odoo 172.23.0.2/16 trust where you should put your container IP instead of 172.23.0.2/16 you can get it with docker ps and then docker inspect sha1_of_container | grep IP

[hparfr]

@rvalyi Why not just mount the socket ?

[rvalyi]

@rvalyi Why not just mount the socket ?

you are right. In fact I tried a lot and was failing because unlike what you would believe you also need to set listen="*" in te postgresql.conf file even when using md5 over the socket when you mount it in your Docker container. So yes it's simpler with the socket. Eventually I'll document it.

[rvalyi]

note for the future for using the host postgres with a socket connection:

• as postgres user, create an odoo user in the host postgres
• set postgresql.conf with listen_addresses = '*' despite using a unix socket this is required
• in pg_hba.conf, set a local a md5 auth method for the odoo user with a line like: local all odoo md5 (this line should come before local all all peer or you should comment local all all peer)
• restart postgres
• in your docker-compose.yml you should share the socket with a statement like:

  volumes:
    - /var/run/postgresql/.s.PGSQL.5432:/var/run/postgresql/.s.PGSQL.5432

  and of course you should pass the required ENV variables to the Odoo container with a statement like:

  odoo:                                    
  restart: always                                                        
  build: odoo
  environment:            
    - PYTHONDONTWRITEBYTECODE=True
    - DEMO=False    
    - LOCAL_USER_ID=$UID
    - DB_USER=...                  
    - DB_PASS=...              # used by the container
    - DB_PASSWORD=...   # used by Odoo
    - DB_NAME=db  
    - DB_HOST=/var/run/postgresql     # very important to use the socket
   # ...

Yeah!

[hparfr]

set postgresql.conf with listen_addresses = '*' despite using a unix socket this is required

It's not what the doc says:

if the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. (source: https://postgresqlco.nf/doc/en/param/listen_addresses/ )

in pg_hba.conf, set a local a md5 auth method for the odoo user with a line like

For sockets, peer can be used instead of md5.