Skip site navigation (1) Skip section navigation (2)

Re: pg_migrator and an 8.3-compatible tsvector data type

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <stark(at)enterprisedb(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>, Teodor Sigaev <teodor(at)sigaev(dot)ru>
Subject: Re: pg_migrator and an 8.3-compatible tsvector data type
Date: 2009-05-29 01:23:28
Message-ID: alpine.GSO.2.01.0905282059070.14027@westnet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2009-05-29 01:25:46
Subject: Re: pg_migrator and an 8.3-compatible tsvector data type
Previous:From: Caleb WeltonDate: 2009-05-29 01:14:36
Subject: Re: Python 3.0 does not work with PL/Python

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group