Hello, trying to find the right words to explain my problem I realized that after all I've a synchronization problem, I guess! I've two databases starting with the same data but from monday they'll work on two separated subset of data with two different releases of the software and also two different databases (MySQL the old one and PostgreSQL, the customer felt more secure with PostgreSQL).
After a period of about a month I need to merge the data of the databases back in a single db. To better explain let's say I've a table for support tickets that has already say 1000 records regarding the 300 stations. From Monday the system will collect the tickets for 20 stations while the other will collect the tickets for the remaining 280 stations. At the beginning of December I need merge the two databases. The real situation is far more complicated with about 90 tables and a lot of foreign keys.
Do you have suggestions or links?
Don't expect to find a ready solution for your problem. I think that you will have to manually code this "merge" utility.
You can make your life easier by ensuring that the same key in the same table in two databases has different ranges by using something like:
ALTER TABLE tbl AUTO_INCREMENT = 10000;
Thank you Vasyapupkin, that was one of my options but I hope there's something more methodical that can ensure the correctness of the merge even if I know I'll have to write a lot of SQLs.
I reckon there is a better solution but since time is short I wold suggest you consider this.
Can you transfer the data from the existing db to the new one now, before the new one goes live? Thne can you build a script such that the data being entered to the old one for the next month, can it be inputted as well, to the new one? then, when the old one is 'switched off', the data is already to go in the new one?
just a suggestion.