Two Node SQLite Sync

10th September 2020

Goal: Build a stupid simple routine that will keep one SQLite database (slave on Node2) in sync with the master database (Node1) over a local network (active / passive setup).

This is still a part of my IoT smart home project. I have been looking for a cheap platform that would act as the heart of everything and I finally settled on the Raspberry Pi due to its super cheap entry price, amazing OS (Raspbian which is really Debian) and popularity. The "brains" of it all will consist of two of these boards and they will be in High Availability mode just in case one crashes or gives up the ghost for any reason. However in order to get the HA to work I need to replicate the content of the Master SQLite database to the Slave SQLite database. I have poked around for a tool that could do that for me and all the things I have found did not really meet my requirements. One of the most promising candidates was watchdb which allows for node to node replication but it is not an active project anymore (dead for 6 years). So back to the drawing board. The the general picture of what I will be working with looks like this:

- Node 1 (Master) and Node 2 (Slave) each with a SQLite database,
- keepalived provides the floating IP address for the failover,
- NFS server to share one of the databases to the other Node,
- SQLdiff to figure out the differences between the databases,
- PHP will execute the commands so things happen.

Let your imagination run wild and follow me with the flow of this contraption. The keepalived is maintaining the floating IP address which is pointing to the Master (Node 1) by default with the Apache web server with PHP and SQLite database running my IoT app. This node also has the NFS Server running which is exporting the location of the SQLite database file to the other node to access. On the network all of the IoT sensors are delivering their reporting and status updates to the floating IP address (which is Node 1 at the moment). Node 2 (Slave) is also running Apache with PHP and the same IoT app + it has mounted the export from Node 1 so that it can see the SQLite file from Node 1 (where all the updates are saved at the moment). Now for the juicy part. Node 2 runs a PHP script every 120 seconds which triggers the SQLDIFF to compare the local database with the database stored on Node 1 (NFS share remember?) . All of the differences will be applied to the local database of Node 2 so that it is exactly the same as the database that is acting as the active node (Node 1). This will be keeping both systems in sync with a 2 minute delay which is not a big deal for a home system. Now, when Node 1 fails for some reason the keepalived will promote the passive node to act as the master. All of the IoT devices on the network will not even notice that something has failed and they will still continue to operate as if nothing happened. Some sort of alert should be sent out to the user of this contraption that Node 1 has failed.

To sum it up: It maybe looks and sounds like a very crude routine so far but it seems like it will work based on some initial testing that I have done. I just need to put it all together and give it a try. I am certain that I will be modifying things as I go along, but hopefully you get the idea what I am trying to do here. I have always been told that if you can't find what you are looking for than you have to create / build it yourself.

Hey, like this? Why not share it with a buddy?