PostgreSQL – Sync Two Postgres Databases from Separate Locations Without Network

data synchronizationpostgresqlreplication

I have 2 postgres databases running the same schemes on two different computers, that are not guaranteed to have any network connection while having to handle read/write requests.

Because they operate on two different locations, I'm trying to find a solution to merge them once in a while to keep data updated in both (I can connect them every couple of week, or pass files with a flash drive).

My main concern is that this databases have unique values in some of the tables (Auto Increment Primary Keys), and I'm not sure how to handle this – what if both dbs create the same key, for different data? Are there any tools that can merge automatically and output changes that were done?

(If it matters, using postgres 10, ubuntu env.)

Thanks

Best Answer

I think a good way to do that is to have sequences with an increment of 2 instead of 1. That way your primary keys will be uneven on one site and even on the other one. Then you can merge without worrying.

create sequence seq_uneven increment by 2 start with 1;
create sequence seq_even increment by 2 start with 2;

And then you use seq_uneven on one site and seq_even on the other.

Related Question