Incremental probkup - Is there a limit for the -io option?

Posted by slacroixak on 21-Dec-2017 02:27

Using OE 11.6.2.  Thinking of the best backup policy for a development database that evolves pretty little, with remote developers that have to regularly replace their local dev DB with a common reference dev DB.  (Yes, the point is really to throw their local dB away and replace it with the common reference one).  The idea is to minimize the number of times people have to copy a large backup file (even when zipped)

Using incremental backups is a good way to keep a same large reference full backup then apply a small set of incremental backups files.  Still I would like to avoid dealing with a set of incremental backup files and I was wondering if it was possible to work with one single incremental file that would contain ALL the updates since the last full backup.

Note I am not interested in AI and rfutil for that.

I just discovered the -io option of the probkup statement.  If I use it with a large value, then I should be able to deal with one single incremental backup to keep a local DB in synch with all the updates of our common DB since a last full backup snapshot.

Hence the question: is there a limit for the -io option?  Could I use something like 365 or thousands?

Would I be the first one dealing with this concern? I did not manage to find anything about this so far.

Many thanks in advance

/S

Posted by George Potemkin on 21-Dec-2017 05:13

Right!

All Replies

Posted by George Potemkin on 21-Dec-2017 02:51

> I just discovered the -io option of the probkup statement.  If I use it with a large value, then I should be able to deal with one single incremental backup

With large value of the -io option the incremental backup becomes the full backup. The -io option is 4 byte field and it sets how many the intermediate incremental backups can be skipped with prorest.

Posted by slacroixak on 21-Dec-2017 04:19

Hi George,

So I if understand well, my solution might be to use -io 0 for the very first incremental; -io1 for the 2nd, -io 2 for the third an so forth.  Would this do the trick?

I mean, with that, my incremental backups should be limited to the blocks that have been modified since my previous full backup.  Correct?

Posted by George Potemkin on 21-Dec-2017 05:13

Right!

Posted by slacroixak on 21-Dec-2017 07:59

Thank you George.

I will just add to this thread the way to know these two pieces of info

  •   the date of last incremental backup
    • _DbStatus._DbStatus-ibDate   (not just a date as it says "Thu Dec 21 14:57:56 2017"
  •   the number of incremental backups since last full backup
    •   _DbStatus._DbStatus-ibSeq   (0 if last backup was a full backup)

That's actually pretty cool...

Posted by Thomas Mercer-Hursh on 21-Dec-2017 09:28

Note that you may find that the time for an incremental backup is close to the time for a full backup since it still has to read the whole database, even if it is only writing a smallish part of it.  Don't expect a one day backup to run in a small fraction of a full backup.

Posted by slacroixak on 21-Dec-2017 11:18

Hi Thomas, note I do not mind about the time it takes to perform such an incremental backup.  (it's still pretty quick BTW)

This point of this thread is to end up with ONE SINGLE rather SMALL incremental backup file that holds only the blocks that have changed since the Database was fully backed up.   Much easier to pass through a WAN than a full backup (still a couple of hundred MB once zipped)

I am actually surprised Progress has not yet provided an option to make such a "single-incremental".  Indeed, most of the times, a database just grows a little every day, but people keep backing up the all stuff.  I'd rather handle a safe full backup from time to time (once a month for example) and one single pretty small (compared to the big multi year animal) incremental.  This way it is easier to restore a database to what it was at a given time (just keep one full backup per month, and one small single-incremental per day).

As it is now, we can do it we handle ourself the value to use for the -io option, as described in my previous post (the VST knows it)

Note this approach opens the door to achieve solutions that are pretty similar to the typical complex AI based stuff...

Posted by Rob Fitzpatrick on 21-Dec-2017 11:42

> Note this approach opens the door to achieve solutions that are pretty similar to the typical complex AI based stuff...

> This point of this thread is to end up with ONE SINGLE rather SMALL incremental backup file that holds only the blocks that have changed since the Database was fully backed up.

I think you're making AI sound harder than it is to implement.  

As an alternative to incremental backups, how about AI with -aiarcinterval of 86400?  You'd still get a daily DB change file without the time and I/O of a daily backup.  Restore/roll forward is easily scripted.  

Posted by James Palmer on 21-Dec-2017 12:38

I would just use AI roll forward to achieve this. Sounds much simpler to implement to me.

Sent from Nine

Posted by Thomas Mercer-Hursh on 21-Dec-2017 13:27

I am actually surprised Progress has not yet provided an option to make such a "single-incremental". 

They have ... it's called AI! :)

Posted by slacroixak on 21-Dec-2017 15:00

Guys,I said at the very beginning I was not interested in AI.  I can give a few reasons now:

I know AI.  It's NOT simple for everyone, more for DBA guys than for regular developers.

At first, you need to enable AI for the wanted database, and administrate it when you switch AI files.  The life cycle of AI files is tight to the database, I mean you cannot just loose AI files and make them again, as opposed as the single-incremental file I am talking about.  

A simple backup policy requires no enabling.  A single script can do the job I mentioned.

I can ask any developer to do probkup and prorest with a large full file and a small single-incremental file as I said.

For my cases, AI has great chances to end up with larger files since all the transactions we do are updating or recreating the same records in a rather limited set of DB blocks. (it's a dev database)

On one hand, an incremental backup is very quick (7 to 10 seconds), that's a very limited in time disk activity.

On the other hand, AI lead to constant AI disk activity each time transactions occur, affecting the performance we are used to. (Normally, you handle AI on a separate Disk for performance sake etc...)

At last, there could also be a few interesting licensing aspects, but it's not top of my head now

Posted by James Palmer on 21-Dec-2017 15:06

After imaging is really simple. Particularly with the after image daemon process. It should be on in production anyway if it's a database you care about. It should already be part of your backup process.
A script to identify and roll forward AI files is not that difficult to write either.

Sent from Nine

Posted by ChUIMonster on 21-Dec-2017 15:58

In the scenario where one is wanting to update a DEV db as described incremental backup and restore makes perfectly good sense.

When the same data  is being repeatedly updated incremental backup can create smaller files than after-imaging.  That's because you are sacrificing point in time recovery -- all that you get is the end result.  Which is fine for what he needs.

On the other hand...

0) I would not recommend this as an alternative to after-imaging in a production environment.

1) If the backup is only taking a few seconds I'm not sure why there is much of an incentive to improve it... the files can't be all that big if they are being created that fast.

2) Developers who think that AI is complicated really ought to spend more time working with it ;)

3) AI is not /quite/ a "set it and forget it" that can just be made into a default option (even though I sincerely wish that it could be).  The ai daemon does go a long ways towards getting there but the lack of a purge daemon to clean up the logs means that eventually your disk fills up and goes BOOM!  If Progress would implement a retention policy with the ai archive daemon then it would truly be a feature that could be enabled by default.

This thread is closed