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.
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
