CODEWITHSUNDEEP
sqlitedatabase-replication
Tacitus86
Tacitus86

Reputation: 1404

Database Replication Software

I've seen a few questions related to softwares that replicate data on databases but I haven't seen one that might work for my needs. I need to replicate many slaves to a single master. The master will have all the data from the slaves merged into its table. The scenario below should help explain what I'm looking for:

Each machine has the same table/DB layout using SQLite. I will use 1 table with columns Timestamp, MachineNumber, PointName, Value for these examples:

DB table on Machine 1

00:00:00 1 Point1 1.0
00:00:00 1 Point2 2.1
00:00:01 1 Point1 1.5
00:00:01 1 Point2 2.1

DB table on Machine 2

00:00:00 2 Point1 2.0
00:00:00 2 Point2 3.1
00:00:01 2 Point1 2.1
00:00:01 2 Point2 3.0

DB on MASTER (Needs replicated from each slave and incremental additions due to loss of connection)

00:00:00 1 Point1 1.0
00:00:00 1 Point2 2.1
00:00:00 2 Point1 2.0
00:00:00 2 Point2 3.1
00:00:01 1 Point1 1.5
00:00:01 1 Point2 2.1
00:00:01 2 Point1 2.1
00:00:01 2 Point2 3.0

Upvotes: 0

Views: 372

Answers (1)

varro
varro

Reputation: 2492

Try:

sqlite3 db-master
sqlite> attach 'db1' as db1;
sqlite> attach 'db2' as db2;
sqlite> insert or ignore into data select * from db1.data;
sqlite> insert or ignore into data select * from db2.data;

Note: if the data in the slave tables continually accumulates, you probably need to define a unique ID on the data so the same information doesn't get re-added every time you sync with the master database.

Upvotes: 1

Related Questions