On 10/6/15, David Barrett <dbarrett at expensify.com> wrote: > sqlite has a cool "online backup" API: https://www.sqlite.org/backup.html > However, it only backs up to a local disk. I'm wondering if anybody can > think on how to use this API to do an incremental backup over a network > connection to a remote host? The networking part is easy. But I can't > figure out how to programmatically get the binary of the database file into > memory such that I can send over the network (other than waiting for the > whole thing to backup to disk and then just doing a remote disk copy, > obviously, but my file is so large I don't want to wait that long nor do I > have enough disk to do that). I'm not really familiar with the VFS layer > -- would there be some way to do a custom VFS that the online backup API > writes to, and then I just copy the blocks over from that? Thanks for any > creative ideas! >
Suppose you have a really massive database that is being continually updated, and you want to have a live backup to another host over a network. The SQLite backup API will work for this, we believe, as long as the database is only being written from a single database connection. The backup API transfers information from a "source" database connection over to a "destination" database connection. There are several considerations here: (1) For the destination database connection, use a custom VFS that writes content over the network rather than writing content to disk. This custom VFS does not need to be general-purpose. It can be highly specialized so that it only handles the specific VFS methods that the backup interface will generate. (2) For the source database connection of the backup, use the same database connection that is used for writing to the database. That means that when changes are made to the source database, the backup mechanism can go back and resend only those pages that actually changed. If the database is modified by any database connection other than the one used for backup, then the backup has no way of knowing which pages changed, and so it has to start over again at the beginning and rewrite every page. (3) The thread that is running the backup should monitor how fast new content is being shipped over the network and should throttle calls to sqlite3_backup_step() to prevent too much content from being queued up in cache. -- D. Richard Hipp drh at sqlite.org
