SQLite is probably the world’s simplest Relational Database Management System. Basically it’s a C library which can be embedded in programs easily. There is no server/client mechanism, the database is a single file. For small work loads, it often makes no sense to use a big RDBMS package like MySQL or PostgreSQL, unless of course you need the special features they provide.
So, I came across this situation where I need replicate SQLite database. The problem arised because, data redundancy was needed across multiple servers and the program in question was supporting SQLite, MySQL and PostgreSQL; but one of the servers had only workload for PostgreSQL database and installing MySQL for the small amount of data the program handled wasn’t sensible. The other two servers had pure MySQL workload. Also, the updates needed to be propagated. So there is the deadlock.
I searched around to find nothing useful, but I remembered there is a cron called incrond which can watch files and directories for events using INOTIFY and execute commands when specific events occur. The solution is almost there. All I need is a script to copy the database file to other servers when data was written to it. I wrote a simple script which would copy the files to other servers; at first I tried rsync with incremental updates, but it didn’t work, because SQLite doesn’t delete data actually when rows are deleted as written in this FAQ at official website. The data is simply marked for deletion and reused during future inserts.
So I guess you now know why incremental updates won’t work: even if I delete a row, the size of database is going to remain same. Actually it should work with rsync’s checksum method, but it didn’t for me. Nevertheless, since the data size was pretty small, I used scp to transfer the database.
The script is ready, now you need a incrontab entry for IN_MODIFY event i.e. to run the script when the file is modified. Here’s a small example:
/var/lib/database.sqlite IN_MODIFY /scripts/copy_database.sh
That’s it. Whenever the file is modified, it will be transferred to the other servers. Wait, the story doesn’t end there.
I tried modifying the database on primary server and it did properly replicate to other servers, but the new changes weren’t visible neither on the primary server nor on the other servers. In the sense, the program could see the deleted row. This is mainly because of caching in memory. The solution was to reload the program (which didn’t cost much in this case). So I just added the commands for reloading the program in the script before syncing the database to other servers on the primary server and then two ssh commands in the script which execute the reload command on the other two servers.
/etc/init.d/program reload rsync rsync ssh server1 '/etc/init.d/program reload' ssh server2 '/etc/init.d/program reload'
Did you tried the rsync with incremental updates along with the reload of the program?
if yes then what was the result
I don’t remember if the rsync I tried was incremental or not. But I think it should work. Why not try it and post your findings back as a comment?
I was going to setup a ghost website with a SQLlite database backend distributed across 3 nodes, but i was thinking of using lsyncd for the replication since lsyncd incorporates both the inode watching and rsync functions.
Good tip. I’ll check this out.
But the problem with sqlite replication is that the process reading it needs to be restarted. That was my observation when I wrote this article.
Good stuff. But more complex than I’d like.
That’s an interesting solution. For small databases, replicating only one way, it should work fine, but an incremental trigger-based replication solution (such as CopyCat for example : http://www.copycat.fr) would be better. That would also allow you to handle replication both ways : if the database was changed on both sides, the changes can be merged.
I guess it would be better to use litereplica (http://litereplica.io)
This didn’t seem to exist when I wrote this post in 2012.
Oh yeah, sorry! I forgot to say ‘now’. And thanks for sharing your experiences.
Has anyone actually tried the rsync incremental updates ?
We would like to port this for an android app used by small shops in Dakar.
If you’re not doing two way sync and make sure that the db is not open when you are writing it via rsync, it should work.
Thank you so much for your quick reply Nilesh.
So we start today using it.
Would you be interested in following developments of rsync integration ? Any email address or Telegram number I could reach out to you ?
If you have anything that will be useful to someone else looking for it please post it as a comment. You can ping me on twitter : @nileshgr