On 05/05/2016 12:45 AM, Rob Willett wrote: > Ryan, > > Ahhhhh! The penny drops, we didn?t realise that with the backup API. > That explains a great deal. We must have missed that in the docs. Blast.
There is one exception to this: If the database is written to via the same database handle that is being used as the source db by the backup API, then the backup is not restarted. In this case if any pages that have already been transferred to the backup db are modified the new versions are written into the backup db at the same time as the source is updated. Dan. > > We?ve looked around for other providers in Europe and the cost > differences are very high. We need to be in the EU for various data > protection reasons. Until now we haven?t had any issues as we don?t > move a significant amount of data around in a very short period of > time, so the rate limited IO has not been a problem. > > One of our options is to do what you suggest with a second database > server and run them hot/warm. We had already thought of that but not > got around to it as the setting up time is quite high (we need a bank > of servers, feeding things from one server to another), but our > immediate issue is simply copying the 10GB database. The downside of > the second server is moving 10GB data files around the internet > afterwards back to the failed server. Rebuilding from scratch is a > pain as it takes around 2-3 weeks to rebuild the database from scratch > as we have to process every file again (circa 200,000) in order and > each file takes around 4-8 secs to run. > > I think the backup solution is the tried and tested Keep-It-Simple > shell script. We pause the queue upstream which stops the update > process, do a cp and then restart the queue again. All of this is > doable in shell script. > > Rob > > On 4 May 2016, at 18:22, R Smith wrote: > >> On 2016/05/04 2:35 PM, Rob Willett wrote: >>> Dominque, >>> >>> We put together a quick C program to try out the C API a few weeks >>> ago, it worked but it was very slow, from memory not much different >>> to the sqlite command line backup system. We put it on the back >>> burner as it wasn?t anywhere near quick enough. >> >> You do realize that the backup API restarts the backup once the >> database content changes, right? I'm sure at the rates you describe >> and update frequency, that backup would never finish. The backup API >> is quite fast if your destination file is on a not-too-slow drive, >> but you will have to stop the incoming data to allow it to finish. >> >> As an aside - you need a better provider, but that said, and if it >> was me, I would get two sites up from two different providers, one >> live, one stand-by, both the cheap sort so costs stay minimal >> (usually two cheap ones are much cheaper than the next level beefy >> one). Feed all updates/inserts to both sites - one then is the backup >> of the other, not only data-wise, but also can easily be switched to >> by simple DNS redirect should the first site/provider go down for any >> reason. The second site can easily be interfered with / copied from >> / backed up / whatever without affecting the service to the public. >> >> I only do this with somewhat critical sites, but your use-case sounds >> like it might benefit from it. My second choice would be to simply >> stop operations at a best-case time-slot while the backup / copy >> completes. >> >> Cheers, >> Ryan >> >> >> >> _______________________________________________ >> 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
