Hello everyone,
I’m working on a project where we originally used MySQL 8.4 as the history database on the same host as Ignition. However, after some changes in scope, the customer now wants to move the history storage to a separate server running PostgreSQL with the TimescaleDB extension.
Is there any way to migrate the existing historical data from the old database to the new one?
I’ve already created the PostgreSQL database connection and added a test history tag, which created the basic database structure.
Should I copy the contents of the metadata tables (sqlth_te, sqlth_scinfo, sqlth_sce, sqlth_partitions, sqlth_drv, and sqlth_annotations) from the old database to the new one?
Also, since TimescaleDB handles partitioning and pruning automatically, I assume there will only be a single table (sqlth_1_data) for the historical values. Is that correct? If so, should I keep the old partition tables, or merge all historical data into the new sqlth_1_data table?
I would really appreciate any feedback from someone who has done something similar and could point me in the right direction.