Re: pg_upgrade

Tory M Blue wrote:
> On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> But initial response to all this, is umm we have not really made a
> >> dump/restore unnecessary with the latest releases of Postgres than, as
> >> I would have to think that there is a high percentage of users whom
> >> use tablespaces.
> >
> > Yes, but they don't change tablespace locations during the upgrade. ?In
> > fact, we have had surprisingly few (zero) request for moving
> > tablespaces, and now we are trying to implement this for Postgres 9.2.
> > The normal API will be to have the user move the tablespace before the
> > upgrade, but as I said before, it isn't easy to do now in Postgres.
>
> Okay think here is where I'm confused. "they don't change tablespace",
> okay how are they doing the upgrade? Do they leave the olddatadir in
> the default location and create a new one elsewhere, vs where I'm kind
> of doing the opposite?

If you look in a 9.0+ tablespace directory, you will see that each
cluster has its own subdirectory:

test=> create tablespace tb1 location '/u/pg/tb1';
CREATE TABLESPACE
test=> \q
$ lf /u/pg/tb1
PG_9.2_201111231/

That means if I upgrade to 9.3, there will be another subdirectory for
9.3, _inside_ the same tablespace location. This change was added in
Postgres 9.0 to allow for upgrades without having to move tablespaces.

Now, since you are upgrading from 8.4, and don't have a subdirectory,
the 9.1 cluster will be created inside the tablespace directory, so it
will look like:

323234/ 423411/ 932323/ PG_9.1_201105231/
----------------

I realize that is kind of confusing, but it works just fine, and
pg_upgrade will provide you with a script to delete the old cluster, and
its subdirectories, when you are ready.

I hope this helps clarify things.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +