Re: MySQL -> pgsql

> 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:

 You da man, Bop :)

> 1. Plan any large table rollovers ahead of time on test boxes. Build
> your schema in your destination db before you migrate

 (nods)

> 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.

 Forgive me my ignorance, what's an rsync script?

> 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.).

 That's it.

> 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.

 Ghost records? Would you please elaborate  that? Also, what if we have two
machines (assuming MySQL -> PGSQL), with  pretty much identical UI part (eg
HTTP server + CGI),  and one of them is running an  old DB, whereas another
one has PGSQL as DB server. Then  we use Balance to distribute the requests
in round-robin manner between the two? Check the logs, monitor behaviour of
the new server to catch the errors.
 With this scheme: a)  you can always pull the plug on  the new server with
no downtime;  b) if new  server fails somewhere,  the user will  repeat the
request and it'll be balanced to the old server automagically with p=0.5

> 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.

 Watch the timezones.

> 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        .

 Uh? Meaning you _don't_ live in machine room? :)


--

 Well I tried to be meek
  And I have tried to be mild
 But I spat like a woman
  And I sulked like a child
 I have lived behind the walls
  That have made me alone
 Striven for peace
  Which I never have known

 Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)