To stop the triggers and copying of records to the archive tables, just delete the two triggersĭrop trigger if exists trigger_on_states on states ĭrop trigger if exists trigger_on_events on events
create a single index in archive tables on the 'last_updated' column, seems the most usefulĬreate index ix_states_archive_last_updated on states_archive using btree (last_updated DESC) Ĭreate index ix_events_archive_time_fired on events_archive using btree (time_fired DESC) Trigger that is fired on each insert to events table Function to insert into analytics table the the row being added to events tableĬreate or replace function trigger_on_events() events Analytics Table Definition, create the archive table Trigger that is fired on each insert to states table Function to insert into analytics table the the row being added to states tableĬreate or replace function trigger_on_states() states Analytics Table Definition, create the archive table Postgresql home assistant archive trigger setupĬreated tables, procedures and triggers to save records from HA states and events tables to archive tables This is how I got starting with my analytics: Have a look at the Home Assistant iPython/Jupyter notebooks here and see if you can adapt them to reading from your Postgresql tables. A lot of folks have great success with Influxdb. I was already using Python, Pandas, Matplotlib and other iPython, Jupyter tools. But it was another pair of systems to learn, manage and keep current on. I started with Influxdb and Grafana, both very powerful. I have not used the events archive for anything, but that might be due to the way I use and think about Home Assistant, YMMV. Hi Below are the steps I used to create and manage the two archive tables I keep. Trigger_on_events BEFORE INSERT ON events FOR EACH ROW EXECUTE FUNCTION trigger_on_events() TABLE "states" CONSTRAINT "states_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id) "ix_events_time_fired" btree (time_fired) "ix_events_event_type_time_fired" btree (event_type, time_fired) "ix_events_context_user_id" btree (context_user_id) "ix_events_context_parent_id" btree (context_parent_id) "ix_events_context_id" btree (context_id) "events_pkey" PRIMARY KEY, btree (event_id) Trigger_on_states BEFORE INSERT ON states FOR EACH ROW EXECUTE FUNCTION trigger_on_states() "states_event_id_fkey" FOREIGN KEY (event_id) REFERENCES events(event_id) "ix_states_last_updated" btree (last_updated) "ix_states_entity_id_last_updated" btree (entity_id, last_updated) "states_pkey" PRIMARY KEY, btree (state_id) You could try dropping the constraint with the command below in psql, sounds like you are comfortable (unfortunately) with rebuilding your HA Recorder in Postgresql, so if it messes things up more, just start again with a clean Postgresql homeassistant database: ALTER TABLE public.states DROP CONSTRAINT states_old_state_id_fkey I used psql to look at my ‘states’ and ‘events’ tables and I do not see a constraint named ‘states_old_state_id_fkey’ on either table.
Reading the more detailed info you posted over there. I see you posted a bug report in github, that is probably a good path to an answer. So if that in fact works, HA does not care about the previous ‘old_state_id’ values not being available when it build a new SQLite DB. I am pretty sure the default ‘fix’ when your SQLite DB gets corrupt is to simply shut down HA, delete the database file and restart HA. Might be a bug in HA with it’s Postgresql integration. But I can query these as well externallly with no issues. These are what I mostly query, leaving the main states and events table to HA’s use. But I also archive all my history to Postgresql archive tables. I let HA purge the database using it’s standard function. I have 54 million records in my states table for 65 days of history. I am using Postgresql docker image created by postgresql people on docker hub. If so, I would think there is something amiss with the Postgresql image you are using. There was a big database change back a couple releases ago, changing what was stored in the events table.Ī pain to do, but is it possible for you to temporarily switch to a clean Postgresql VM and let HA build it’s tables from zero. I have only these tables as created by HA in Postgresql database homeassistant: The message seems to reference a table I do not have.
0 kommentar(er)
