On 10/9/15, David Barrett <dbarrett at expensify.com> wrote: > Thanks Richard, this is exactly what I was thinking. One question on this: > > On Tue, Oct 6, 2015 at 10:22 PM, Richard Hipp <drh at sqlite.org> wrote: > >> (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. >> > > Hm, that's interesting. I was thinking that when the backup started, all > new writes would just accumulate in the WAL file -- and then be > checkpointed after the backup completed. Thus the backup would be of the > state of the database at the moment the backup was *started*. However, > based on your description it sounds like the backup API will copy over > changes that have occurred throughout the backup process itself. Our > database is modified about 40 times per second, so my fear is the backup > API would never actually complete because it would be continuously > re-backing up the same pages. Am I understanding this correctly? Is there > any way to obtain the behavior I initially assumed? >
Yes - run the backup using a separate "source" database connection and do the entire backup within a read transaction. Note that having this long-running read transaction going in a separate database connection will prevent checkpoint operations from resetting the WAL file, and so the WAL file will continue to grow until the backup completes. So you may want to take steps to ensure that the backup doesn't take *too* long. -- D. Richard Hipp drh at sqlite.org
