Home Assistant Add-on: PostgreSQL + TimescaleDB

Share your Projects!
,
225

Hi @GeertV,

I don’t have system_stats included in the addon by default. I don’t have it installed anyway.
Perhaps I could add it to the next version of the addon. I have created a feature request for it so I won’t forget.

226

New versions of the addon have been released (pgAdmin as well as Timescale).
For any issues, please use the github issue tracker as mentioned in the addon-docs.

Thank you all for your support and valuable input! :pray:t2:

227

Hey, is there a way to connect the postgresql with home assistant via the socket connection?

My recorder won’t connect with the link from the documentation, e.g. postgresql://@/DB_NAME

Appreciate any help!

228

socket connection is available only if db is running on the same machine.

Your example of connection URI is incomplete though

229

The recorder URL that I use is:

postgresql://<username>:<password>@77b2833f-timescaledb/homeassistant

For fresh installations (you haven’t yet created a user of password yourself), the full connectionstring would be:

postgresql://postgres:homeassistant@77b2833f-timescaledb/homeassistant

You can try that for a start, but I would strongly encourage you to create a separate username, and change the default password of the postgresql user.

The /homeassistant at the end is the postgresql database name. The Addon will create this database for you by default.

The 77b2833f-timescaledb part is fixed for everyone. It’s the unique internal hostname of the addon.
You should only change this if you run the addon-docker-image from a different machine or on a non-supervised installation.

1 Like
230

Queries and deleting data

these work in pgAdmin4

statistics count:

SELECT
  COUNT(*) as cnt,
  COUNT(*) * 100 / (SELECT COUNT(*) FROM statistics) AS cnt_pct,
  statistics_meta.statistic_id
FROM statistics
LEFT JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
GROUP BY statistics_meta.statistic_id
ORDER BY cnt DESC

events count:

SELECT
  COUNT(*) as cnt,
  COUNT(*) * 100 / (SELECT COUNT(*) FROM events) AS cnt_pct,
  event_types.event_type
FROM events
INNER JOIN event_types ON events.event_type_id = event_types.event_type_id
GROUP BY event_types.event_type
ORDER BY cnt DESC

states count:

SELECT 
    COUNT(*) cnt,
  	COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
    states_meta.entity_id
FROM states
LEFT JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.metadata_id
ORDER BY cnt DESC

delete sensor:

DELETE
FROM
  states
WHERE
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      entity_id = 'sensor.bed'
  )

deleting sensor only deletes it in STATES but in EVENTS it is still remains, please advise query deleting sensor from events

also query for events to sort by entity_id would be great

also tried deleting from service Recorder: Purge entities but it will not delete from events

231

Apologies if this is a daft question but if i have a backup of my HA instance that used the in built SQLite db.
Lets say i spun up a new HA VM with postgres as the main db with the tables all initialised.
If i performed a restore would it not ingest the data into the postgres db or would it just fail.?

232

HA doesn’t support data migration between databases. If you want to maintain old data you have to migrate them manually from sqlite to pg. then run HA with the PG as a recorder storage