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://ha@/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
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://ha@/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.