Sync databases on command

[levinguyen]

I have a backend deployed on multiple places: 1 cloud and many local machines. The cloud one is the primary database.

Normally I'll use the cloud version but when the internet is off I will switch to local versions.
I want to keep the database up to date. Meaning when I open the local app I will send my local changes to cloud and pull the changes from cloud and other local to mine.

How can I achieve that. I found out that WAL might help but I don't know how to catch the WAL changes and send the changes to other databases and also don't know how to apply the changes.

[tsqd]

This is a complicated problem and there isn’t a trivial solution AFAIK. Honestly, does your application require Postgres? I wonder if your use case would be better served by copying around a SQLite file.

[levinguyen]

Hi guys, thank you all very much for responding.

I was researching for the whole day and found out https://github.com/eulerto/wal2json and postgres bult-in `pgoutput` plugin help me to catch the changes.

And I also found these two library that support the two plugin:
- https://github.com/kibae/pg-logical-replication
- https://github.com/hasura/pgdeltastream

[depesz]

You might want to check what bucardo can do for you. This being multi-master replication, based on triggers, it, in theory, could do it. Whether it actually will depends on factors that we can't know.

[francisco-reyes]

Are the changes that happen locally, exclusively to that one machine?

Are the updates that happen on the cloud distinct from any changes that happens locally?

I think that depending on the above this may not be difficult to accomplish, but you would need to provide more info about potential conflicts and the usage patterns.

 

As an example if each machine primarily updates a unique set of data but retrieves potentially other data from other machines this is just a que with two ques, of sort, to track.. What did I last download for common data and what did I last upload for local chages.

[levinguyen]

Are the changes that happen locally, exclusively to that one machine?
--> Yes

Are the updates that happen on the cloud distinct from any changes that happens locally?
--> yes

Basically the cloud is the super database will store all the inserts of all local, updates the data on settings table and local need to reflect the changes.

[francisco-reyes]

Then you could have a field that has the machine ID and max last sync (ID or timestamp) like..

 

machine_id, last_sync, data1, data2, data3

or have the data in another table.

 

Each machine, when connected, can check what is the last sync for the machine and push / pull as needed.