pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Guido Neitzer <guido.neitzer@pharmaline.de>
To: pgsql-admin@postgresql.org
Subject: Re: two databases in one
Date: Thu, 17 Aug 2006 03:34:55 +0200
Message-ID: <6F034D74-9530-43B4-A631-38CF92199067@pharmaline.de> (raw)
In-Reply-To: <44E2C5A9.4070505@sihpic.fr>
References: <44E2C5A9.4070505@sihpic.fr>

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



view thread (6+ messages)  latest in thread

Message-ID: <6F034D74-9530-43B4-A631-38CF92199067@pharmaline.de>
Permalink:  ../6F034D74-9530-43B4-A631-38CF92199067@pharmaline.de/
Also on:    postgresql.org/message-id/6F034D74-9530-43B4-A631-38CF92199067@pharmaline.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: guido.neitzer@pharmaline.de
  Subject: Re: two databases in one
  In-Reply-To: <6F034D74-9530-43B4-A631-38CF92199067@pharmaline.de>
  References: <44E2C5A9.4070505@sihpic.fr> <6F034D74-9530-43B4-A631-38CF92199067@pharmaline.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox