Обсуждение: 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