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.