Re: two databases in one

On 16.08.2006, at 9:13 Uhr, Soulabaille Samantha wrote:

> I've got two databases with the same tables and schema on two
> different servers.
> I want to have only one database.

Okay.

> I don't know how to do this because almost of the tables contains
> serial.

And you have overlapping keys from that? Not nice.

> It's for production databases, i can't make mistakes.

NEVER try such a thing on a production DB. Always test in a clean
room test environment!

I have done this once, but it was a big hazzle. What I've done:

0. Dump production and create a test server. Work on that for now.

1. Add columns to db1 where you can temporarily store the keys needed
to build relationships (like orig_pk, orig_fk_other_table).

2. Drop foreign key constraints if they prevent you from inserting
rows without doing relationships. Drop existing "not null"
constraints from foreign key columns.

3. Read the content of the db2 into db1 while doing the following:
insert with NEW primary keys and empty foreign keys, store necessary
orig_pk and original foreign keys in the temp columns.

4. Iterate over the the newly inserted rows and insert the correct
foreign keys for the new db by traversing the old relationships with
the information you have stored in the temp columns.

5. Re-create foreign key constraints. Re-create "not null" constraints.

6. Drop the tmp columns.

7. Test your result carefully.

8. Be absolutely sure, it contains, what you want it to contain.
Check again.

====> Build a script with all steps, so that it runs automatically.
Test it again and again!

9. Shut down your production environment for a service window.

10. Backup your production environment.

11. Apply your script (yes, you should have build a script for
that!!!!) to the production server.

12. Test again the result.

13. Test again.

14. Bring your service back online.

No guarantee that this will work for you. You will also get real fun
if you have n:m join tables in your database.

Also you may want to build special handling if you have non-unique
values (like product categories, usergroups, users, ...) in your dbs:
you might not want to have duplicates for them. Build special
handling for that.

Good case to show, when primary keys that are absolutely unique and
not only unique in one db would have helped very much. Therefor I
like the ugly 24 byte unique pks from WebObjects very much ...

cug