Backing up large Historian DB - Ideas?

Ignition
1

Hi all,

I have an Ignition Gateway thats been running for a couple of years now, and the Historian DB is quite large now (88GB).
I am now required to setup a backup server on a separate PC, with the end goal to have SQL replication setup to keep both PC’s databases in sync.

I’ve got most of the partitions as monthly, which are ~8GB each, and have recently changed partitioning to weekly, so the tables are a bit smaller (~2GB).

Doing a test SQL dump of a 4.5GB table took 40min to finish, so if you extrapolate that it’ll take a long time to do a full backup of everything.

What suggestions do people have for doing this? My understanding of replication is that it cannot be started until both SQL servers have the same data, which obviously requires a lot of database dumps and restores.

HistorianDB_Breakdown.PNG518×928 20.5 KB

2

I have got automatic SQL dumps working that backup entire Databases already, but Historian is too large to use this method.

Is there a way to somehow figure out the most current Historian table partition and run an SQL command to do an SQL dump of just this one table?

3

You can get the current epoch timestamp, multiply by 1000, and find which record in sqlth_partitions has that timestamp between start and end (note: there are potentially more complications if you have multiple gateways storing to the same database). Using that 'dynamic' table name is difficult in a pure-SQL context, though; most database flavors don't support dynamic table names in queries outside of stored procedures.

4

I’ve been running my SQLDumps automatically with a script executed by a CRON job on my linux server.
I could use the following query to get the right table name:

SELECT pname FROM sqlth_partitions ORDER BY end_time DESC LIMIT 1;

but i don’t know how to run an SQL query in a script.

Is it possible to do an SQLDump through Ignition scripting somehow?

5

To anyone finding this post, i found this other post which details how to execute a script to backup the Historian database, excluding the latest created partition.

1 Like