0

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

  • 1
    Have a look a this SO question, at this tool or this – McNets Jan 24 at 8:33
  • Are you trying to have just one schema on each server with combined data in every table? Or have have a "my schema" and an "other schema" and then use UNION ALL or inheritance to combine them when you want them combined? – jjanes Jan 24 at 16:35
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.

  • Thanks, it's really good for me right now. But, can you think of something that will be scaleable? Say if I'll have 10, 1000, 100000 workstations that need this mechanism? – JLev Jan 27 at 6:13
  • First, I'lm sure you don't want 1000000 workstations in that kind of a situation... That solution work for quite small number of workstations that must be defined at the beginning. If you want to change the number of workstations involved easily, I think you'd need UUID (see postgresql.org/docs/11/datatype-uuid.html). – Arkhena Jan 27 at 10:20
  • You can't always get what you want :) Thanks for the help! – JLev Jan 27 at 10:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.