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
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