TimescaleDB/Postgres using a unix socket in docker(-compose) as HA-database

Hi,

I have been using @freol 's amazing custom component ltss for some time now and I recently decided that I wanted to use the same timescaleDB as my recorder-database.

My setup uses a docker-compose file to start my services and since socket-connections are faster than TCP-connections I wanted to use sockets in docker-compose. The setup is quite simple: Sockets are just files and files can be shared between containers using a volume. So tl;dr: Create a volume for the socket and mount it in both containers.

To explain this a bit more detailed consider the following docker-compose.yaml:

version: "2"
volumes:
  socketvol:
services:
  home-assistant:
    image: homeassistant/home-assistant:0.118.0
    environment:
            - TZ=Europe/Berlin
    volumes:
            - ${HA}:/config
            - socketvol:/var/run/postgresql
    restart: unless-stopped
    #network_mode: host
  timescaledb:
    image: dekiesel/timescaledb-postgis:1.7.4-pg12
    volumes:
      - ${TS}/data:/var/lib/postgresql/data
      - ${TS}/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d/
      - /etc/localtime:/etc/localtime:ro
      - socketvol:/var/run/postgresql
    environment:
      - POSTGRES_DB=home_assistant
      - POSTGRES_USER=ha
      - POSTGRES_PASSWORD=mys3cr3tpw
    restart: unless-stopped

I have highlighted the important parts in bold.

I am using my own timescaledb-container since I couldn’t find a timescaledb-container with the postgis extension that works on raspberry pis, but you can use your own timescaledb/postgres container if you like. I am only using the postgis extension because ltss needs it.

To enable the socket add this line to your pg_hba.conf:

local home_assistant all peer

where home_assistant is the name of the database you configured in the docker-compose file. Using all in that line means every linux user can use that socket, but since I am only sharing the socket with the home-assistant container I feel comfortable using it. If you don’t want to use all you can specify the linux user making the calls to the DB.

Now that the database part is done let’s use this database in Home Assistant. Personally I like using !include's to keep my configuration as clean as possible so I added the line in bold to my configuration.yaml:

# Configure a default setup of Home Assistant (frontend, api, etc)
default_config:

# Text to speech
tts:
  - platform: google_translate

group: !include groups.yaml
automation: !include automations.yaml
script: !include scripts.yaml
scene: !include scenes.yaml
recorder: !include recorder.yaml

Now that Home Assistant uses recorder.yaml we can define our database connection:

db_url: postgresql://[email protected]/home_assistant?host=/var/run/postgresql

That’s it! Restart your Home Assistant and you are done, you should now be using timescaledb/postgres using a unix socket in docker-coompose :slight_smile:

You can use the same socket for ltss (just remember to create an additional database for it).

To monitor the size of my Home Assistant Database I am using this sensor:

- platform: sql
  db_url: postgresql://[email protected]/home_assistant?host=/var/run/postgresql
  queries:
    - name: DB size
      query: "SELECT (pg_database_size('home_assistant')/1024/1024) as db_size;"
      column: "db_size"
      unit_of_measurement: MB

Relevant part from my configuration.yaml:

sensor: !include_dir_merge_list config/sensors/

I hope this helps somebody, let me know if I have missed anything.

Nice to see that you have some use of my component and interesting approach to sharing the same Postgres instance for both the recorder and LTSS. I also use them both but just connect via tcp for both of them. Do you have any idea what kind of performance benefits that can be expected using a socket instead?

Also, nice to see that you managed to get a 32bit docker image compiled including PostGIS. If you want, consider opening a PR to https://github.com/freol35241/ltss with an addition to the README.md about the image.

@freol, According to this test the connection is 33% faster with sockets (175%when ssl is activated)

https://momjian.us/main/blogs/pgblog/2012.html#June_6_2012

The benchmark is 8 years old though and the heavier your queries are the less likely it is to make a noticeable difference.

I’ll look into creating a PR, it would be my first one so I first need to read up on how to do that :wink: