"events_pkey" PRIMARY KEY, btree (event_id) Trigger_on_states BEFORE INSERT ON states FOR EACH ROW EXECUTE FUNCTION trigger_on_states()ġ92.168.2.242 => \d events "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 ġ92.168.2.242 => \d states 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. Please provide your valuable suggestions and feedback.No, not seen such a message. Note: Replace the database name (DB_Dev_USER in my sample) by your destination database name. Copy the generated script of tables and Stored Procedures and execute it in the database where you want to export. Click on the Next button.Īgain click on the Next button in the new window.Īnd then click on the Finish button after generating the script of the selected Stored Procedure.Īt this point the script is generated. In this example we are saving in a new query window. In the Set Scripting Options window set the location where you want to save the generated script. Here you have the flexibility to export selected procedures and tables or all the tables and procedures by clicking checkboxes. On the right side you found tables and Stored Procedures. Go to the Object Explorer window then click on something then right-click on your database then select Tasks then click on Generate Scripts as shown in the following.Īfter clicking on Generate Scripts the popup is open as shown in the following and select Choose Objects. There are several ways to export or import tables and procedures from one database to another database on the same server, or between different database servers.
0 Comments
Leave a Reply. |