Does SQLite support replication?

O

7

31

In an application which embeds SQLite3 and uses an in-memory database, is it possible to replicate the database between two running instances of the application? I could do this by hand with a homebrew protocol duplicating all my DB accesses, but it seems like something that should be done inside the DB layer.

Overrefinement answered 11/12, 2009 at 13:58 Comment(0)

B

16

Brute force approach: Send it the ".dump" command to create a text representation of the data. Read that data in into the second database. Not sure you can use that.

If you need a fine grained update (sending a copy of each upto the other copy), have a look at sqlite3_update_hook

But how do you plan to handle errors? For example, what happens when the copy of the DB in app2 can't make an update for some reason?

To solve this, move the database to a server process and have the two apps talk to it.

Beat answered 11/12, 2009 at 14:8 Comment(4)

Okay, so sqlite3_update_hook would be my starting point for layering that on at a row-by-row level. That's very helpful, thank you. In response to the other points, I avoid things which cannot be inserted by certain magic properties of the application. The desire for two separate instances rather than a central DB is driven by a robustness-by-distribution requirement, and sqlite is driven by a system resource limit. – Overrefinement 11/12, 2009 at 16:37

Okay. Just make sure both copies run out of memory/disk space at the same time, etc. – Beat 12/12, 2009 at 12:41

This doesn't seem like a serious answer. Yes you could eventually build it yourself, but it will be a big undertaking to make it reliable. – Roughish 7/12, 2017 at 3:41

@AaronDigulla fair enough, but the question was about replication between two running instances ... which your answer doesn't really provide. – Roughish 18/1, 2018 at 22:7

E

11

Out of the box, no. There are a small number of third-party options:

SQLite sync: https://ampliapps.com/sqlite-sync/ This one seems attractive because it can replicate to other databases as well as SQLite, and doesn't modify the SQLite engine. I haven't tried it yet.

Litereplica: http://litereplica.io/ One way only. Seems to have been around a bit.

LiteSync: http://litesync.io/ Two-way replication. Pretty new, but an evolution of Litereplica so probably more mature than it appears. I have tried this a bit and it does seem to work smoothly, with a few bugs which the developer is looking at. You have to use the developer's modified SQLite engine, which seems like a concerning dependency. You also don't get much control, e.g. you can't say replicate now without reopening the database.

Euchromosome answered 18/1, 2018 at 22:2 Comment(0)

U

3

Lsyncd - Live Syncing (Mirror) Daemon may be of use here. It uses rsync to do continuous replication on the file level.

Umpire answered 7/4, 2015 at 15:34 Comment(1)

This is probably not a good idea for live running databases. It may result in the slave database being corrupted if it replicates during a transaction. – Roughish 7/12, 2017 at 3:43

H

2

If you want replication in an in-memory database you need to look at berkeley DB (BDB). However the data model for BDB is string-string dictionary, so you loose the flexibility of SQL. plus it has a three clause license, so if your project is commercial you need to get licenses.

Hey answered 11/12, 2009 at 14:15 Comment(0)

B

1

We still do not have native support, although we have some improvements in backup, check docs, but we have more third party options: welcome Litestream, which is published and developed in a similar way to SQLite.

Bombycid answered 27/8, 2021 at 22:36 Comment(0)

O

0

No it doesn't because the project's scope is being a simple in-process database. But because the database is just a single file, you could write your own replication script based on plain file copy operations, rsync or something similar.

If you really want a SQLite based client/server type of RDBMS, you could take a look at SQLiteDBMS.

Offside answered 11/12, 2009 at 14:9 Comment(1)

Sqlitedbms dead now – Superpatriot 30/12, 2020 at 13:48

E

0

Unison? Best you could do though was hot spare, because SQLite db in one monolithic file. You couldn't round-robin between the two "instances". Hot spare isn't bad, you simply pick up the other app+db without much fuss, compare to MySQL master-slave or active-passive where there is some manual intervention which is not easy. MySQL replication passes around SQL, not just diffs like unison/rsync. But with unison you have master-master.

Eshelman answered 21/2, 2012 at 22:24 Comment(1)

@BoppityBop cis.upenn.edu/~bcpierce/unison which points to github.com/bcpierce00/unison written in OCaml which is interesting in itself. You may prefer a golang solution these days, SyncThing syncthing.net – Eshelman 31/12, 2020 at 22:50

Recommended topics

Hot tags