Re: MySQL -> pgsql - Mailing list pgsql-general

"Diehl, Jeffrey" wrote:
> The database I am hoping to migrate has a few tables with around 50K
> records.  These databases get updated every hour, 24/7.  I don't think I can
> do the migration with a text editor.  So, I'm still looking for some other
> method if it exists.

I'm a bit late on this thread, but I'm currently wranging a large set of migrations:
 postgreSQL-> Oracle
 mySQL-> Oracle
 mySQL-> postgreSQL
 LDAP-> postgreSQL
 mySQL-> LDAP
all at once. All live. 350+ websites, a few thousand hits a minute. (Yippee?)
Acceptable downtime is under 3 minutes for the entire system. I do lots
of migration. Here's my general workflow, HTH:

1. Plan any large table rollovers ahead of time on test boxes. Build
your schema in your destination db before you migrate, so your're just
moving recent record sets into pre-built environments. It helps if
your new data is hosted on a new box, so you aren't down if the new
server tanks.

2. Before you roll out: Rewrite your update web/app code to apply to both
sets. If you use an rsync script, you can roll this into the "live"
site/app fairly quickly, in between hits. You'll want to do the schema
changes and rewritten db access anyways, to optimize use of each
engine (postgres is slow if you write mySQL style for it, i.e., redo
your statments to make use of sub-selects, joins, etc.). Plan to
use the following code migrations: 1 adds. 2 updates 3 selects. Save
selects for last as your ghost records (for testing add and update)
won't be accurate.

3. To handle updates, make sure you have "ghost" records in the new box,
until you can pull a clean dump. Ghost records are fast, as they
only need minimal data. Once you have all this setup in the test
environment, roll a hidden pilot out. Monitor it. You still won't have good
data in both, but you won't have to throw a switch and pray you
don't loose anything..... now:
Roll back and correct that horrible mistake you made that brought
it all to its knees and we won't tell anyone about ;-). It's the
first pilot, these things happen, it's why you test.
(Note: using a disk backup is really nice for building test
machines that are copies of live ones... you get exact software
configs that way)

4. Once you *can* roll forward and accept all add, selects, updates,
etc. into the test systems, plan for a brief outage. I like 3.am.,
others like mignight...just target a slow period.

5. Coffee, scary movies, whatever, roll out late at night. Test.
Go to sleep when sun comes. A cot in the machine room is helpful.

6. Watch the boxes for a week. Make sure your cron scripts work.
Make sure rarely used components are all accounted for, and fix the
forgotten ones. You will proably have a few web pages or whatever
that only get used every few days, so your new box isn't quite
"ready" until you have all these. Keep the old data box as your
"master", with everything being duplicated by your app code onto
the "slave".

7. Plan your second rollout, confidant that you have almost
everything. You proably won't, so pull an extra set of backups on
the old data box. Change your app code to not scream bloody
murder when it can't find the old db.... is sometimes helps to
have three sets: 1. Current code 2. Rolling transition code
3. New Code.

8. Rollout to point to new box as "master". Take everyone out
for drinks, congratulate them on all their help. They will not
trust the new system, so build trust in other ways. Occasionally
shut of the old box, fix what broke, until the old box can be
left off for a few days.

9. Plan to spend a few more weeks ironing out kinks like the-web
app-in-accounting-that-checks-30-records-once-a-month. Expect panic,
and be graceful.

Reduced version:
Old box live
Old box live / Old box mirrored in testing
Old box live / Old box mirror in testing, new box in testing
Old box live / Old box mirror in testing /Roll out duplication code pilot in testing
Once pilot is clean:
Roll out duplication code.
Old box live / New box Live
Old box live / fix new box ;-)
Second switch:
New box primary, old box as backup.
New Box live

-Bop
--
Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
which is currently in MacOS land.  Your bopping may vary.