Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Does anyone have ever tried rsync'ing sqlite database?

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

share|improve this question

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
  • 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.