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)