1

We have an application written in C and we are looking for a way to do transparent replication of SQLite database on one server (but on different disks). I read lots of guides about replication across multiple servers and most of what I've found is about RDBMSs such as MySQL database.

This post is the only one I've found about replication specifically on SQLite, where some solutions were suggested. For example, someone suggested using Litereplica which seems to be exactly what I am looking for, but after spending about 2,3 days, it didn't work (or maybe I couldn't use it properly). Another solution was Rqlite, but that is not on the same server.

I am not a SQLite expert and I just started using it, and I really stuck on this. Is there any other solution to replicate the SQLite database on the same server? For example, what we need is e.g., what we write in the main db, will be written in the replicated db/dbs as well. Also, for read, if the main db did not work, it should read from the slave/backup db...

UPDATE_1

I used the ATTACH to copy all the data from the main db to the backup db. Here is what I've done in SQLite command prompt:

ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup;  //This attached a new empty db to the main db

CREATE TABLE backup.fuel_transaction_1 AS SELECT * FROM main.fuel_transaction_1;

Now, Is there any way to sync this two databases? so that e.g., if I delete a row from a table in main db, it deletes the same row from a table in backup db?

1
  • Ive done this myself by just copying files. How frequently does it need to be updated? Oct 25, 2016 at 9:04

2 Answers 2

1

Searching for the same topic, I came across a mention in Using SQLite: Small. Fast. Reliable. Choose Any Three.:

Replication

SQLite has no internal support for database replication or redundancy. Simple replication can be achieved by copying the database file, but this must be done when nothing is attempting to modify the database. Replication systems can be built on top of the basic database API, but such systems tend to be somewhat fragile. Overall, if you're looking for real-time replication — especially at a transaction-safe level — you'll need to look at a more complex RDBMS platform

0

Definitely look into sqldiff, which is typically included with the sqlite3 install. Very simple to use, just feed it two db (or table via command line switch) names and it will create a listing of sql insert, update, delete statements to bring one to the exact state of the other. Appears to have been fully debugged and ruggedized. This makes a very solid foundation for any sqlite replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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