Rsync / Sqlite database

Question

Does anyone have ever tried rsync'ing sqlite database?

Is it possible to access the data while it gets syncronised?

Answer 1

I would consider this dangerous.

The SQLite database also has journal files that need to be preserved.

If you

• rsync the database without the journal files in the middle of a transaction
• copy the database file
• access the copy

It is highly likely that you will experience corruption.

Use the SQLite Online Backup API instead.

Answer 2

Assuming you'd like to do this via shell commands, you could do something along the lines of:

ssh user@host 'sqlite3 "/path/to/db.sqlite" ".backup /path/to/dbbackup.sqlite"'
rsync user@host:"/path/to/dbbackup.sqlite" "db.sqlite"

Which first, safely creates a backup of the existing database, and then copies it to "db.sqlite" on your own machine.

Answer 3

As Noah have pointed, it is dangerous. Your database will get corrupted.

You can use the litereplica library instead.

It does all the work behind the scenes respecting the normal SQLite transaction. And incrementally (only the changed pages are transferred).

Or the rqlite library.

This last one requires the installation of the Go language.

Answer 4

For those looking for a more safer solution, to replicate and backup SQLite in realtime, without interrupting its operations, checkout Litestream - Litestream is a tool that runs in a separate process and continuously replicates a SQLite database to Amazon S3.

Answer 5

• While no transaction is open, rsync of the database will be OK.

• While a transaction is open, rsync of the database will result in corruption. Reason: you'll be getting the database and its accompanying journal file at a slightly different point in time, breaking the consistency that would allow SQLite to reconcile the journal with the database. If SQLite cannot reconcile the journal with the database, then the database will be left in an inconsistent state with no way to tell what was part of a partially applied transaction.

In order to be able to rsync an SQLite database, you will need to either:

• Use SQLite's own CLI commands for creating a backup copy of the database file. This will wait to gain a write lock, and copy the file, then release the write lock, thus ensuring no transactions are partially applied. Then, rsync the copy.

  .backup ?DB? file
  

• Or, shut down all processes using the SQLite database while you are doing the rsync.

Answer 6

The previous answers are correct.

But if you make 100% sure there is only read activity on your database you can safely take any kind copy of the database file. I've done this in a production application for ages without issues (cp, tar, rsync, scp, sftp). The database file is set read only at os level (chmod a-w) and database write attempts give an error

Error: attempt to write a readonly database

while all selects work normally.

Then, of course, this approach may or may not be feasible regarding you application. The same approach can be used to prevent unauthorized writes like SQL injection on a web application.

Answer 7

No, sqlite databases aren't constantly coherent by default. However, a search on "sqlite copy file" has found a page on doing online backups with SQLite, which I suspect will cover all the issues you may have.