I have a customer that had filled a database unexpectedly. My first reaction when I arrived onsite was to start storing current data to another database.
I then figured out what was going on with db1, extended the memory in the needed partition.
So now I need to restore db1 as the database, but I want to include the tag history db2 has captured in the mean time.
I would imagine I just do a dump of the sqlt_data_1_2022_09 table and then insert that into the db1.
Will I run into any indexing issues? I really don’t understand how the tag historian schema works, will I run into any primary key index issues?
Historized tags are referenced in sqlth_te. The sqlt_data_X_XXX_XX partitions have a tagid foreign key, which matches the id primary key from sqlth_te.
If you want to insert stuff in a sqlt_data partition, you’ll need to make sure that the ids from both databases’ sqlth_te match. Which is probably not the case, and will make your life a nightmare if you try to move things from one db to another.
I have no good solution to suggest here, I’m just warning you that simply copying stuff from one sqlt_data to another won’t be enough.
I guess I will go ahead and restore db1 and start collecting current data from there.
It will be painful but I guess could make a chart that shows the id info from sqlth_te on db1 and db2 then manually edit the tag id to match db1 on the sqlt_data_1_2022_09.
edit:
I had also reached out to support this morning and just got a response. Sounds like they are directing me the same way. They included a helpful link to an ignition database document.
Ignition has a lot of systems built in that will query the database automatically without requiring you to build a query. Because of this, there will be a couple tables that are created in order to store the tag history data from the tags. The sqlth_te contains information about the tag but the tag's data will not be stored in this table. It will have the id, tag path and so on. The sqlt_data_X_X table will contain the actual tag history data from the tags.
Since you also have 2 different databases and one holding new data and the other one is holding old data, you could combine the 2 databases and adjust the tag id for the data inside the database. Basically you would be changing tag id to merge all the data.
