two databases in one

This is a multi-part message in MIME format.
--------------080507070201090407020809
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

I'm working with a postgresql 7.4.7
I've got two databases with the same tables and schema on two different
servers.
I want to have only one database.
So i would like to insert in the first database the data of the second
database.
I don't know how to do this because almost of the tables contains serial.
It's for production databases, i can't make mistakes.

Can anyone help me? It's urgent.

Thanks





--------------080507070201090407020809
Content-Type: text/x-vcard; charset=utf-8;
name="samantha.soulabaille.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="samantha.soulabaille.vcf"

begin:vcard
fn:Samantha Soulabaille
n:Soulabaille;Samantha
org:SIHPIC
adr;dom:;;43 avenue d'Italie;amiens;;80094 cedex 3
email;internet:samantha.soulabaille@sihpic.fr
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------080507070201090407020809
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--------------080507070201090407020809--

Re: two databases in one

> I've got two databases with the same tables and schema on two different
> servers.
> I want to have only one database.
> So i would like to insert in the first database the data of the second
> database.
> I don't know how to do this because almost of the tables contains serial.
> It's for production databases, i can't make mistakes.

Maybe you could just import the second database into another schema of
the first database if it's urgent...

Otherwise the nature of conflicts will have to be thought through, like

- Are there some data that exist in both databases - i.e. which become
duplicates if both tables are merged?

- Are the serials used as foreign keys in other tables or as the basis of
any other fields?

- Can the serials be changed to different numbers - e.g., is it ok or even
needed to convert "11" on database B to "10011" on database A and so on,
to avoid conflict two ID 11 records having different data?

- Does the order of records matter?


Once the problem is familiar, many gurus will be able to help. I just
wanted to provide a starter.


Regards,


Ben K.
Developer
http://benix.tamu.edu

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: two databases in one

This is a multi-part message in MIME format.
--------------060806040202060707020206
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Thanks for your response.

Indeed , the serials are used as foreign keys.

We made a web site with a database. Then we duplicated the web site and=20
the databse (without data, only sch=E9ma) for another server .
The 2 databases were filled in the same time.

Now someone would like to merge the 2 databases and have only 1 site and=20
1 database.
I was thinking about changing serials of the 2nd database and do a=20
restore on the 1st, as you said.
But it's a big database, and i'm not sure of the exactly things to do.



Ben K. a =E9crit :
>> I've got two databases with the same tables and schema on two=20
>> different servers.
>> I want to have only one database.
>> So i would like to insert in the first database the data of the=20
>> second database.
>> I don't know how to do this because almost of the tables contains=20
>> serial.
>> It's for production databases, i can't make mistakes.
>
> Maybe you could just import the second database into another schema of=20
> the first database if it's urgent...
>
> Otherwise the nature of conflicts will have to be thought through, like
>
> - Are there some data that exist in both databases - i.e. which become=20
> duplicates if both tables are merged?
>
> - Are the serials used as foreign keys in other tables or as the basis=20
> of any other fields?
>
> - Can the serials be changed to different numbers - e.g., is it ok or=20
> even needed to convert "11" on database B to "10011" on database A and=20
> so on, to avoid conflict two ID 11 records having different data?
>
> - Does the order of records matter?
>
>
> Once the problem is familiar, many gurus will be able to help. I just=20
> wanted to provide a starter.
>
>
> Regards,
>
>
> Ben K.
> Developer
> http://benix.tamu.edu
>
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>


--------------060806040202060707020206
Content-Type: text/x-vcard; charset=utf-8;
name="samantha.soulabaille.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="samantha.soulabaille.vcf"

begin:vcard
fn:Samantha Soulabaille
n:Soulabaille;Samantha
org:SIHPIC
adr;dom:;;43 avenue d'Italie;amiens;;80094 cedex 3
email;internet:samantha.soulabaille@sihpic.fr
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------060806040202060707020206
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--------------060806040202060707020206--

Re: two databases in one

Soulabaille Samantha wrote:
> Hello,
>
> I'm working with a postgresql 7.4.7
> I've got two databases with the same tables and schema on two different
> servers.
> I want to have only one database.
> So i would like to insert in the first database the data of the second
> database.
> I don't know how to do this because almost of the tables contains serial.
> It's for production databases, i can't make mistakes.
>
> Can anyone help me? It's urgent.

Whatever you do, I would suggest doing it in a test environment first to
work out any problems. You can also get some ideas of the time required
and possibly tune the process. And of course make a good backup first.

I would examine all the tables to determine whether there is likely to
be an underlying data collision (duplicate id in both places where
unique is required). If the only place that is likely to happen is with
sequences then first make a list of them:

select * from pg_class where relkind = 'S';

You will need to "make room" in the main tables for the data from the
tables from the other database. I would look at the tables influenced by
the sequence in question like this:

select min(some_serial), max(some_serial),
max(some_serial)-min(some_serial) as range from some_table;

-[ RECORD 1 ]----
min | 104973931
max | 111209210
range | 6235279

So you need a gap of 6235279 ids in the target table so check the status
of the target sequence then advance it as appropriate - give yourself
whatever cushion is appropriate given how rapidly the sequence is used.

select * from some_sequence ;

-[ RECORD 1 ]-+--------------------
sequence_name | some_sequence
last_value | 111209231
increment_by | 1
max_value | 9223372036854775807
min_value | 100000000
cache_value | 1
log_cnt | 25
is_cycled | f
is_called | t


select setval('some_sequence', 111209231 + 6500000);

Then you need to move the source data sequences so they will land in the
gap so in this case they need to be moved forward by
111209231-104973931+1 so run the appropriate updates on the source database.

This is a basic example which assumes you can shut down the source
database (sequences stop incrementing) prior to the transfer. You may
also need to examine multiple tables if they all rely on the same sequence.

You will need to also determine the order in which to put data back into
the target server in order to avoid violating foreign-key restrictions.

Sounds like a headache. Good luck.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: two databases in one

Please unsubscribe

Regards
R Mohan
Blue Star Infotech Limited
T : +91-22-66956969

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match