Synchronize data between two databases with the same structure

Question

Synchronize data between two databases with the same structure

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

I need to pass the data from one database to another with the same structure, the problem is that both databases are in use, in both you can insert different records and they are in different places.

For example: in DB1 we enter an invoice from the client1, in the DB2 we enter the client's invoice2 and we need that in the DB2 this is the invoice of the client1 and client2.

The problem we have is given by the IDs, that when inserting without the ID the data of the header of the invoice (table: sales_bills) so that it does not show duplicate ID error then we can not insert the detail (table: invoices_sales_detail ) because we do not have the ID and we have to modify the query by hand.

I'm looking for a more practical way to pass the data than having to put together an ETL for each table that you insert without the ID, and then in each related table solve the new ID to be able to insert the data.

Is there any way to make it easy without having to create an ETL for each table in this way, with some tools or another way of doing it?

sql postgresql

asked by Net Wilson 26.05.2016 в 16:47

source

2 answers

0

One way I had to fix a similar problem was using UUID codes for unique keys. Generating them randomly, it is practically impossible for duplicates to occur, even with partitions much larger than 2.

Using UUID as type for the ID field, you will only need one mechanism to assign random UUIDs by default. The recommended method is to use the function gen_random_uuid() of the module pgcrypto .

In my case, the alternative I chose was to use the uuid module from python through the < a href="https://www.postgresql.org/docs/current/static/plpython.html"> pl / pythonu .

answered by 26.05.2016 в 18:20

Notice: Undefined index: PHP and Ajax LINQ Join a query in a single row

score 1 · Accepted Answer

Another way is not to use an autonomic ID for these cases. The databases allow to put keys based on text records and / or have composite keys. In fact, using numeric ID when there is already a field that determines functionally is unnecessary.

In fact the invoices already have to have a different number when printing them (this can be achieved by prefixing a "point of sale" or "checkbook number" or "branch id" number).

Then you can have a composite primary key (branch, number).

If you are using a framework that does or does need to be a single field and that is numerical, you can pre-calculate even numbers for one base and odd numbers for the other (or something similar).