Re:Cron-based backup for SQLite
by oefrha on Sunday May 15, 2022 @10:04AM
(#31387637)
See the SQLite online backup API documentation [1].
You can finish the backup in one step, but a read-lock would be held during the entire duration, preventing writes. If you do the backup several pages at a time, then
> If another thread or process writes to the source database while this function is sleeping, then SQLite detects this and usually restarts the backup process when sqlite3_backup_step() is next called. ...
> Whether or not the backup process is restarted as a result of writes to the source database mid-backup, the user can be sure that when the backup operation is completed the backup database contains a consistent and up-to-date snapshot of the original. However: ...
> If the backup process is restarted frequently enough it may never run to completion and the backupDb() function may never return.
The CLI .backup command does non-blocking backup IIRC so is subject to restarts.
You can finish the backup in one step, but a read-lock would be held during the entire duration, preventing writes. If you do the backup several pages at a time, then
> If another thread or process writes to the source database while this function is sleeping, then SQLite detects this and usually restarts the backup process when sqlite3_backup_step() is next called. ...
> Whether or not the backup process is restarted as a result of writes to the source database mid-backup, the user can be sure that when the backup operation is completed the backup database contains a consistent and up-to-date snapshot of the original. However: ...
> If the backup process is restarted frequently enough it may never run to completion and the backupDb() function may never return.
The CLI .backup command does non-blocking backup IIRC so is subject to restarts.
[1] https://www.sqlite.org/backup.html