Re: MySQL -> pgsql

KuroiNeko wrote:
> > 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?

rsync: It's an r-services piece of software (like rsh, rcp, etc.) designed
to do file/dircectory syncronizations. It runs over shh, as well. You set
up rsync on each server (basically, you install/compile it) and you can
sync entire directory trees on a code-line by code-line basis. It can only
send file changes, so it's very efficient. You can run it in a low
enough user level that all permissions, dates, etc. remain unchanged...
I use it for code rollouts and server backups. Updating a backup of a 36
Gb server, over a frac T-1, takes 5-10 minutes. It's not real suitable
for backing up/rolling forward a postgres database, but for html files,
web code, anythign static, it's just dandy.

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

It's to speed up the testing cycle. Rather than grabbing a full data dump, on
each and every record,
'Joe Q Longname III' 'jlong'  'Joe likes to vacation in Brazil' '2/5/70'
1 55678299 367773 'Manager of Departmental Services, Western region' 1211
'Cell Phone' 'Coffe Cake' 22344628288826
(times 100,000 records)

You can make simplified, smaller, "ghost" records, which are faster to
move around and test on. For example, in the above record, much of the
data may be completely irrelevant to most testing, and you can use a much
smaller version....
'J Lo' 'jlong'  'Joe li' '2/5/70' 1 55 3667773 'Man' 1211 'Cell' 'Coff' 223

With only the important join keys being used in the test version. You don't
even have to use a subsection of the live data for all records:
'A' 'a'  'a' '2/5/70' 1 1 3667773 'a' 1 'a' 'a' 1

Will let you test your important join, your basic fields types, your add
and update code, and to a certain extent, your selects. It is helpful to
start with _some_ percentage of your live data (to test for field lengths)
but you don't need the whole data set. I call them "ghost" records because
they're only a vague representation of the real data set... and when you're
shoving around 200Mb data files, it's nice to only have a 2Mb test set
for fast drop/reloading, for general testing.

> 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

Well, this could work, but:
You risk a request going to the new server, winding up in a bit-bucket, and
the user/customer/whatever giving up and leaving. I'm not sure how it is for
others, but I know I also have users who assume errors are only there for
aesthetic reasons (i.e, they assumed it worked, even if they had a blinking,
<H1>FAILURE</H1> error, just because it always worked before...<sigh>)

It all depends on your goals, your environment.... I do some updates this way,
for less mission critical stuff (or errors that users _can't_ ignore) just
because it _IS_ easier and faster. More dangerous, though (what if the new
server has captured the transaction wrong, and the same requests never
make is to the old server?).

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

Yup. Use traffic monitoring for your definition of "slow". Poland is peaking
on my db's at 3am, but they're slower traffic and less demanding.

-Bop

--
Brought to you from boop!, the dual boot Linux/Win95 Compaq Presario 1625
laptop, currently running RedHat 6.1. Your bopping may vary.