Historian Database sync

Has anyone ever set up a synchronization between two Historians?

I need to have two independent, dedicated loggers in my system. However, in the case that one is offline for some period of time, it needs to backfill the gap in the data collection from the other so both have a full set of data at any given time while they are both running.

I'm not sure if the best bet is some kind of automated master-master database or a one-time sync on startup. I presume a master-master database wouldn't be happy with both constantly writing identical data (but at least it wouldn't have to deal with deletes). A one-time sync on startup might be ok... after the down system starts up, I might be able to use something like db-sync (GitHub - mrjgreen/db-sync: PHP library with command line tool for efficiently syncing tables between remote MySQL databases) to find the gaps and fill them, and this may play well even on a live system (but that presumes the order of the rows aren't all that important). But as the database grows that might be a painful sync on startup, having to parse/hash a couple dozen gigs to find gaps.

Anyone done anything similar? Am I better off setting up the loggers as redundant Ignition gateways and using storage replication to handle the sync?

Ignition doesn't have anything that will synchronize as described between two databases. It does have the tag history splitter, which can feed the same history to two databases, using the store and forward system to backfill any outages.

Consider using a DB with High Availability support, where Ignition makes one connection, and the HA cluster manages the DB instances behind the single connection point. In the PostgreSQL world, consider one of these:

https://patroni.readthedocs.io/en/latest/index.html

3 Likes

What @pturmel is saying is the way. If you want a truly synchronized database, leverage HA from your DB.

2 Likes

Do you guys think it would be sufficient to have two independent database servers and a redundant pair of gateways using the tag splitting to write to both databases? The redundancy should ensure that only one writer is active at a time, so I shouldn't get duplicate data.

How effective is that store/forward function on a redundancy switchover? ie, is the buffer part of the synchronization, such that if the primary hasn't yet flushed data to the database at the time of failure, the secondary would take care of it? Or is that buffered data lost?

There is a chance for lost data on switchover. Regardless whether your DB is HA or using a splitter. Ignition's failover is not bumpless, and can take several seconds, and the backup can be several seconds out of step with the master. If there's a serious failure on the master's hardware, whatever wasn't sync'd is likely lost.

If you have specific data from your PLCs that absolutely must not be lost, you would:

  • Use an HA database having at least one slave in synchronous replication mode,
  • Use neither SQL Bridge nor Tag historian, but instead script all transactions from PLC to/from DB with serial numbered handshakes,
  • Not use the store and forward system,
  • And stop production if the PLC's transaction buffer fills up.