Migration Historical Data

Ignition
,
1

I have a Ignition setup, which uses only one database connection.
Historical data tables per month have more than 200 billion rows and size more than 50GB each one.
How to correctly transfer historical data tables to another database without lost data?
I would like to know the correct sequence of actions. Please help.

2

The simplest solution is to upgrade your database, or restore from it into a new one while Ignition is completely shutdown, where the new one takes the same connection settings and authentication. So it just wakes up with a new, bigger, database where the old one used to be.

Otherwise, it is complicated. Too complicated and situation-specific to even have a single correct sequence. You should study the documentation for the tables and examine your install until you understand the complex interactions between the various primary keys and the columns in other tables that reference them.

Once you understand the task, you can pick one of these approaches, based on your needs and options for downtime:

  • Point Ignition at the new database and old database simultaneously. Tag by tag, migrate data into the new database, switching the tag's history provider as soon as the new DB is finished backfilling. Almost no downtime, but may need manual intervention (missed row copies) for each tag when flipping over.

  • Point Ignition at the new database and old database simulateously. Create a tag history splitter to store to both databases. Bulk reconfigure tags to point at the splitter. After all tags are storing to both, you can then start backfilling. When backfilling is complete, reconfigure the splitter to query against the new DB. Then you can bulk reconfigure tags to point only to the new DB.

  • Point Ignition at the new database and old database simultaneously, but with the new database taking the old DB's connection name. Short downtime while the old DB is renamed but the new DB isn't. Once running, backfill from old to new.

3 Likes
3

Thanks for the detailed answer.

4

Would you need to completly shut Ignition down, or would disabling the database connection while the new database is being resotred be sufficient?

Thank You,
Dalton

5

It depends on how much data will pile up in S&F during the conversion. So, maybe. It's complicated.

6

We utalize spokes as well so the data should collect there. We are expecting about 21 hours to restore the DB. If calculated properly this should be a little over 1gb of data in the S&F for our system.

7

Consider approach 2 or 3 in my list.