I use rsync to backup a 400MB sqlite db to a remote server. This is not fast (which is a don't care problem in my context). You may want to test changes to a 'log of sql commands at database' to get a replay-backup remotely as it handles text better than binary files (at least the version I use does). Rsync of that will save IO. I also have a process where changed / new records are flagged and exported for backup. Rsync works well for the little delta files, especially identifying ones that have already been transmitted. You will have to ensure that your automated process warns you when the remote host decided to change their address or pc and the sync fails because it is asking if THIS host can be trusted.
regards, Adam DeVita On Thu, May 5, 2016 at 11:50 AM, Rob Willett <rob.sqlite at robertwillett.com> wrote: > Hi, > > We did look at this before, and discarded the idea but I can?t remember > why. > > I?ve just looked again and seen the ?in-place option which I wasn?t aware > of. That *might* help and be an interesting solution. We know we can make > cp wrk, though with a little downtime. We?ll investigate rsync ?in-place on > a closed (and definitely not working database), see what happens and report > back. It should be easy to test. > > Thanks for the information > > Rob > > > On 5 May 2016, at 16:42, J Decker wrote: > > Instead of cp, rsync might help it is able to send delta changes. >> >> On Wed, May 4, 2016 at 10:55 AM, Rob Willett >> <rob.sqlite at robertwillett.com> wrote: >> >>> Scott, >>> >>> OK, We can see how to do this (I think). Our app is written in Perl and >>> we?d >>> just need to capture the command we write down. The only issue I can >>> think >>> of is the prepare statement and making sure we capture the right SQL >>> command. W >>> >>> We?ll dig into it and have a look, >>> >>> Thanks for taking the time to reply. >>> >>> Rob >>> >>> >>> On 4 May 2016, at 18:52, Scott Robison wrote: >>> >>> On Wed, May 4, 2016 at 11:47 AM, Rob Willett >>>> <rob.sqlite at robertwillett.com> >>>> wrote: >>>> >>>> Scott, >>>>> >>>>> Thats an interesting idea. Is there an option in SQLite to do this for >>>>> us, >>>>> or do we have to write a small shim in our app? >>>>> >>>>> I like the idea of this as its simple and elegant. >>>>> >>>> >>>> >>>> >>>> It would require a little extra work on your part. Nothing built into >>>> the >>>> system that would accomplish this directly. However, I've done similar >>>> things and they don't involve a ton of overhead. You could use another >>>> SQLite database as the append only log, or a simple text file. >>>> >>>> I'm not aware of a free lunch solution, sadly. >>>> >>>> >>>> >>>>> >>>>> Rob >>>>> >>>>> >>>>> On 4 May 2016, at 16:51, Scott Robison wrote: >>>>> >>>>> This is going to become a bigger problem for us as the database will >>>>> >>>>>> >>>>>>>> only get bigger so any advice welcomed. >>>>>>>> >>>>>>>> >>>>>>> Perhaps, rather than backing up the live data, you create an append >>>>>> only >>>>>> log of each and every query you send to the database. Should you need >>>>>> to >>>>>> restore, you replay the log of statements. Or at the appointed backup >>>>>> time, >>>>>> you replay the day's log of statements into another database. No need >>>>>> to >>>>>> ever take the live database offline at the cost of slightly longer >>>>>> running >>>>>> commands during the day to handle the append operation. >>>>>> _______________________________________________ >>>>>> sqlite-users mailing list >>>>>> sqlite-users at mailinglists.sqlite.org >>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>>>> >>>>>> _______________________________________________ >>>>> sqlite-users mailing list >>>>> sqlite-users at mailinglists.sqlite.org >>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>>> >>>>> >>>> >>>> >>>> -- >>>> Scott Robison >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users at mailinglists.sqlite.org >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- -------------- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
