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.
Thanks for the info.
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.
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.
Here is a detailed documentation on database tables and it also has a flow chart showing how all the tables are connected to each other. Ignition Database Table Reference - Ignition User Manual 8.1 - Ignition Documentation
Good luck with that (Most of my historian partitions have more than 15 million entries)
You could use the
tagpath column to match tags and correct the
id, but that’s out of what I can do with SQL