Re: pg_migrator and an 8.3-compatible tsvector data type

On Thu, 28 May 2009, Alvaro Herrera wrote:

> There are so many caveats on pg_migrator (and things that need to be
> done after the migration is complete) that one starts to wonder if
> people is not better off just using parallel pg_restore.

If you've got something close to a 24x7 shop, where planned downtime can
at worst in the small number of hours, the landscape breaks down something
like this:

<100GB: Current dump/reload is probably fine
100GB-1TB: Aggressive pg_dump and parallel pg_restore might work
>1TB: Nothing but pg_migrator will save you

If you're doing a version migration, you need to block writer activity
from the time you start dumping to get a perfectly consistant snapshot,
and large databases can't be made available to users until their indexes
are rebuilt lest every client doing a "select * from x where..." kicks off
a giant sequential scan. Parallel restore only improves one section of
that work.

There is a significant difference between something being difficult or
having caveats and being it impossible, and right now there's no good
answer for TB scale upgrades unless you've lucky enough to have an app
that Slony can usefully replicate. If there's somebody out there who has
a bunch of tsvector data they need to get upgraded in-place, they should
step up to help out here. I think Bruce is right to move on to more
popular issues until that happens. This issue can all get sorted out in
user space at some later time, right?

> From Stefan's reported timings I'm not sure that pg_migrator is that
> much of a benefit in the first place ... unless copy mode can be made
> much faster.

Systems administrators have all sorts of techniques to speed up this sort
of thing, it's a well solved problem. The people who need in-place
upgrade will figure out how to either make copy mode fast enough, or how
to make link mode usable by filesystem tricks. We don't need to worry
about that for them. I think it's premature and distracting to start
talking about optimizing pg_migrator anyway, it's plenty fast for its
intended audience.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD