Slony-I for upgrades - was Re: In the belly of the beast (MySQLCon)

alvherre(at)commandprompt(dot)com (Alvaro Herrera) writes:
> Geoffrey wrote:
>> Joshua D. Drake wrote:
>>> On Fri, 18 Apr 2008 14:30:18 -0400
>>> Geoffrey <lists(at)serioustechnology(dot)com> wrote:
>>>
>>>> I don't know that slony is the answer. It was more of a question
>>>> then an answer. We are hoping to use that solution to migrate from
>>>> 7.4.19 to 8.3.1. The primary reason is to reduce downtime.
>>>
>>> Well :) That is why I asked the questions I did. It very well may be
>>> the answer. It might not. For example, what size is your database? How
>>> many transactions per hour are you doing?
>>
>> We have 13 separate databases, not terribly large, my WAG would be that
>> our largest database might have 30 million rows. I couldn't honestly
>> tell you how many trans/hour we are doing without doing a little
>> research.
>
> I find it pretty unlikely that Slony would be unable to help you upgrade
> here. Obviously you can upgrade one database at a time.
>
> Also, mostly it's not the database size what's a concern, but rather the
> size of the largest table.

Indeed.

There is the "big bang" approach where you subscribe to the Whole
Thang in One Swell Foop, and, if it takes 8h to dump data, 12h to load
it into the new DB (which will take place essentially in parallel with
the 8), and 16h to regenerate indices, then that means you've got a
transaction open against the source DB for something like 28h (e.g. -
12h + 16h), with the obvious attendant adverse effects on vacuuming
and such.

However, it is unusual for a database to consist of just one table of
that sort. If you have a case like this, it will make plenty of sense
to split this set of tables into pieces, and add them in at least
somewhat incrementally.

In a 28h subscription, there are likely to be 10 tables that are of
"significant size" that could be broken into independent
subscriptions, so that there's not the huge hit of one giant 28h
transaction.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://linuxdatabases.info/info/wp.html
Why isn't phonetic spelled the way it sounds?