I am looking for a robust mechanism to keep databases running of different ESP32s synchronized.
Specifically, several ESP32 would sit behind a gateway that provides cellular connectivity to the cloud. For redundancy purposes, (i.e., in case one of the ESP32s or the gateway goes down), each of the ESP32s would have a local copy of the same database, which would subsequently be synchronized across the rest of the devices. The synchronization latency should be in the 1-2second range.
As I am new to the wonderful world of Arduino/ESP32, I was wondering:
-does this architecture (i.e. synchronized databases) makes sense for robustness or is there another preferred way?
-what is the database technology and specific mechanism that would be advisable in this scenario?
Can you describe your database independently? That is, forgetting for the moment the sharing requirement? Is this a "true" database, i.e. table organized tuples in relational schema? Or, is it just some ad hoc lists or files?
You asked about technology, the best choice depends on what is the data, how it should be manipulated.
Thank you for the quick reply. For now, the database would be 2 tables with a standard schema: the first table would be key-value pair (where the value would be modified by a ESP32 or through the cloud) and the second table would be an operation log for traceability (e.g. ID/Timestamp/integer representing the operation).
so far, was thinking of writing custom code (I am not up to date with what is available), however, if there are existing packages that can make my life easier, I'm open to suggestions.
I had a quick look for SCRAMnet, but wasn't able to find any framework applied specifically to ESP32/Arduino. Would you be able to point me towards the right resources?
Hi nitramc. SCRAMNet is a proprietary architecture (non-Arduino) in various forms (PCI, PMC, ISA, PCMCIA, Vax). I mentioned it for the idea of shared-RAM (similar to your request), allowing multiple remote devices to have the same information, nearly simultaneously by refreshing memory network-wide. I believe their software (Linux, Windows, Vax) is downloadable to analyze. You could request a hardware quote... but I believe price is a bit spicy.
Thanks for clarifying. At this point, I would be looking for a ESP32-compatible software solution, first from a cost point of view (this is simply an idea I would like to explore) and also to avoid being locked in an exotic technology. Not to mention the large ESP32/Arduino community that can support any investigation.
I was more thinking using a database framework that allows to automatically synchronize across ESP32s, but I am uncertain which database would be best for my use-case.
Just wanted to follow-up on our discussion to see if you could think of a database technology that would be capable of automatically synchronizing data across multiple ESP32s (for example using the ESP-NOW protocol) or whether additional processes would need to be created to push/pull data across the devices.
Interesting point - why do you actually need them to be distributed? Can't you push and pull from a central database on demand?
Whenever you synchronize multiple bases, it is inherently necessary to lock client accesses until all synchronization is complete. That is to guarantee timely up to date data.
That delay may be not much more than the wait for a datum from a central base. The only difference is when no synch is necessary, then it has only to exchange status messages. But if the datum are not much larger than the status messages, it wouldn't make a huge difference.
My primary reason was redundancy/resiliency: if I put the database on the cloud, I am concerned the gateway cellular connection might go down. Alternatively, if I put the database on a primary ESP32 with the remaining ESP32 connecting to it, I am concerned what happens if that primary ESP goes down or goes in a bad state such that nobody will be able to access the database).
This being said, the single database remains an option. Given my dataset, which database type would you suggest?