Обсуждение: Database Migration
Hi All, 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? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
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/>
--- Chris Browne <cbbrowne@acm.org> wrote: > 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. agreed. as soon as i get the 8.03 setup, i'm going to uninstall the 7.4.x version from my dev box and upgrade it to 8.03. any db work on my 7.3.x webhost will be pretty minimal from that time forward. if i do anything serious, i will most likely change webhosts to one that keeps up to date with pgsql. > ... Those first four steps are pretty important > prerequisites to the > conversion... Chris, i may well end up following your very thorough process... but i'm going to try creating the new prod cb structure using my current dev table structure and then i'm going to try and pull data from the old prod box and try and restore it to the new db. i'm crossing my fingers... if that doesn't work, i'll follow your cookbook instructions. fingers still crossed. ;-) __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com