I am looking the right solution for a daily backup of a sqlite database. The database is about 5GB, growing regularly, receives new data every second and in WAL mode. The service is 24/7, so there is no way to stop anything during the backup. Locking the DB for a few seconds (minutes) may be possible, although to be checked. The intention is not to have the very last data in the backup, but to avoid loosing "too much" data in case of major crash. The backup would run at least very day and maybe more frequently if it doesn't take too long. I tried the .backup command, but the transfer of the database is extremely slow (only a few MB after 10 minutes, still very far from completed after 3 hours) - this is probably due to the constant write activity. I tried the .dump command. This works well, except when trying to restore the data (with .read). Such a large query (about 10GB .sql file) consumes all the RAM on the system and gets killed by the OS (Linux). I read about LVM and the capability of creating a snapshot - this could be an interesting solution, but for now the host machine does not support LVM. I am also considering integrating the backup procedure in the code accessing this DB, but again, I'd like the backup procedure to be independent. I also don't see why this solution wold be faster than the '.backup' command. Any suggestion, except "add some RAM" ? Thanks.

Reputation: 51

Alternate SQlite Backup solution

I am looking the right solution for a daily backup of a sqlite database. The database is about 5GB, growing regularly, receives new data every second and in WAL mode. The service is 24/7, so there is no way to stop anything during the backup. Locking the DB for a few seconds (minutes) may be possible, although to be checked. The intention is not to have the very last data in the backup, but to avoid loosing "too much" data in case of major crash. The backup would run at least very day and maybe more frequently if it doesn't take too long.

I tried the .backup command, but the transfer of the database is extremely slow (only a few MB after 10 minutes, still very far from completed after 3 hours) - this is probably due to the constant write activity.
I tried the .dump command. This works well, except when trying to restore the data (with .read). Such a large query (about 10GB .sql file) consumes all the RAM on the system and gets killed by the OS (Linux).
I read about LVM and the capability of creating a snapshot - this could be an interesting solution, but for now the host machine does not support LVM.
I am also considering integrating the backup procedure in the code accessing this DB, but again, I'd like the backup procedure to be independent. I also don't see why this solution wold be faster than the '.backup' command.

Any suggestion, except "add some RAM" ?

Thanks.

Upvotes: 2

Answers (2)

PhilippeL

Reputation: 51

@CL: thanks for the answer. I completely skipped the restart of the backup API.

In case it benefits others: I gave a shot to the LVM solution and it works really well. In brief:

create/mount a snapshot of the Logical Volume hosting the DB file(s).
use the sqlite .backup command line - or simply copy the file(s) out of the snapshot.
unmount/delete the snapshot.
do what you have to do with your DB copy/backup.

A condensed "how to use LVM" is available here: http://www.howtogeek.com/howto/40702/how-to-manage-and-use-lvm-logical-volume-management-in-ubuntu/

Upvotes: 3

CL.

Reputation: 180030

SQLite's backup API restarts when it detects that the DB has been changed. (In the sqlite3 shell, this check is done after every 100 pages.)

In WAL mode, readers do not block a writer, so it would not hurt to make a backup by reading the entire database without restarting. Write your own backup program that just calls sqlite3_step once for all DB pages (see the API example).

Upvotes: 0

Alternate SQlite Backup solution

Answers (2)

Related Questions