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 gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
