Re: Database Migration - Mailing list pgsql-novice

operationsengineer1@yahoo.com writes:
> i'm facing a db / data migration issue.  i have 3 dbs.
>
> my dev box is pgsql 7.4.6 (laptop, winxp / cygwin)
> my current production box is 7.3.x (webhost, linux)
> my new production box is 8.03 (in-house, linux)
>
> my dev db is waaaay ahead of my production db b/c i
> had to code a lot of pages to include some
> functionality.
>
> i'm thinking about doing this two ways.
>
> 1. dump my table structures from my dev box and then
> fill it with data from the 7.3.x production db and
> hope there are are no glitches.
> 2. build the pgsql 8.03 from scratch and then fill it
> with data from the 7.3.x production db and hope there
> are are no glitches.
>
> do these options sound reasonable?  will 7.4.6 data
> types conflict with 8.03 data types?

In our environments, we get *real* uncomfortable about there being any
"major version" differences between development and production; you
can't warrant that things will work the same unless you are using the
same versions everywhere.

The fact that you have massively different platforms is also a matter
for *some* concern.

The only reasonable approach, to my mind, is to *fix* your development
environment, which is desperately broken since it isn't running the
same version of *anything* as you are running in *any* of your
would-be production environments.

Thus....

Step 1:  Install PG 7.3.x on your development system.

You need to test out, in some sort of "QA" context, the conversion of
data from old to new version (of application and/or database
software).

That new box actually seems a reasonable candidate for that.

Step 2:  Install PG 7.3.x on the new box.

If you plan to use PG 8.0.x in production, you'd better have it in the
development environment.

Step 3:  Install PG 8.0.x on the development system.

At this point, you'll have 7.3.x on *all* the hosts, and 8.0.x on all
but the present "production" box.  There isn't much value to keeping
the 7.4.6 instance around, as it does not correspond to production
deployment you are indicating that you are planning.

Step 4:  Eliminate the useless 7.4.6 instance

You might then use the 8.0.x pg_dumpall to dump everything out of the
7.4.6 instance on the laptop, load it into 8.0.x, and hook the
application up see how that plays out.

Step 5:  Try some conversions...

You will now have 7.3 and 8.0 on both the laptop and the "new
production" system, and can start testing out approaches to copying
the data.  Using the 8.0 pg_dump to pull data from 7.3 is likely to be
the best approach.

Those first four steps are pretty important prerequisites to the
conversion...
--
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/internet.html
Rules of the  Evil Overlord #164.  "I will  hire one hopelessly stupid
and  incompetent  lieutenant,  but  make  sure  that  he  is  full  of
misinformation when I send him to capture the hero."
<http://www.eviloverlord.com/>