Setup & Configuration


General Setup

There are many ways to install Home Assistant. Here is some detail on my instance:

Reference Configurations:

Useful Information:

Database Configuration

  • How Home Assistant stores data
  • Use the Recorder Integration to connect Home Assistant to a database server
  • Things to consider:
    • purge_days_keep attribute - how long to keep data before it is purged. An auto purge is run every night unless disabled.
    • good info even if not using SD card…
    • Useful SQL Queries (Postgres):

      • Count of records added per hour over last 3 days:

        SELECT
    date_trunc('hour', last_updated),
    count(1)
FROM
    states
WHERE
    last_updated > now() - interval '3 day'
GROUP BY
    1

      • Check the oldest records in the events & states tables to confirm HA is purging the DB as per the purge_keep_days value:

        SELECT
    event_id,
    created
FROM
    events
ORDER BY
    created
LIMIT 10;

      • To Find the size of HA’s tables:

        SELECT nspname || '.' || relname AS "relation",
  pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;

      • To Find the size of the indexes of HA’s tables:

        SELECT
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size
FROM
    pg_stat_all_indexes
WHERE
    schemaname = 'public'
    AND indexrelname NOT LIKE 'pg_toast_%'
    AND idx_scan = 0
    AND idx_tup_read = 0
    AND idx_tup_fetch = 0
ORDER BY
    pg_relation_size(indexrelname::regclass) DESC;