Sync two postgres databases from two sperate locations with no network

Question

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

Answer 1

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.

Answer 2

The solution to your main concern is pretty simple, at least conceptually. Make your sequence-based columns be bigint or bigserial, and then start your sequences so far apart that they will never overlap within any conceivable time frame. If these values get exposed to users as, for example, invoice numbers, then you may have to compromise so that they don't overlap within any practical time frame.