Re: migration between databases and versions

Hi Mohan,

I'm running 7.4 on a reasonably old version of turbo linux, on and even
older creakier machine and it seems fine so far. I suspect I may be having
some problems due to the fact that the development environment is 3 linux
servers running under virtual pc on one windows 2000 box. :)

I'm facing a similar data migration issue to you, but possibly more complex
from what I read. So far my data migration has been from the old production
system to the new development system in order to test the procedures and
generate test data. My approach has been to dump tables individually, then
edit the dump file and change the tables name, and delete things like
constraints and index definitions. I then load the old table as person_old
(to use your example) and then create a bulk insert statement to copy data
from the old table format to the new one. My system is a major
redevelopment, so I wouldn't expect pg_dump to handle it all for me
automatically.

My understanding is that you will have problems loading data into a table
that does not have exactly the same definition as the one used to create the
dump. For example, if the 2 tables have the same columns but they were added
in a different order. It may be that using different dump options could get
around this though - such as generating the dump as insert statements.

If you come up wth any interesting information as you progress with this,
I'd be happy to hear about it.

Regards
Iain
----- Original Message ----- 
From: <mohan@physics.gmu.edu>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, December 18, 2003 3:58 AM
Subject: [SQL] migration between databases and versions


> I have been Red Hat linux 7.3 on my prod server. I have been running
> postgres 7.2 on it.
> I would like to upgrade it to the newest version of postgres.
> 1) Is there a version for redhat 7.3.
> 2) How stable is postgres 7.4 ?
> I have another critical isssue.
> My database has now changed.In our new release of the product we have
> added few more columns to some of the tables in the database. But the
> production does not have these fields. All the fields in the tables in
> production are also in developement version. But there are some extra
> fields in developement database that are not in production. There is a lot
> of data already in the production database.
> Now i need to add move the data from the old production database to new
> one which has certain extra fields. Please let me know if it is possible
> to do this migration, if yes how? using pg_dump?
>
> Eg: Old database with data
>    has a Table named 'person' with fields A,B
>  New Database to be put under prodcution
>    also has an Table named 'person' with field A, B,C
>
> If i do a pg_dump from old to new does it fill the A,B and leave C alone
> or will it give an error given that C is not a null field.
>
> Please help me
>
> --Mohan
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org