Re: pg_upgrade - Mailing list pgsql-performance

From Bruce Momjian
Subject Re: pg_upgrade
Date
Msg-id 201112051822.pB5IMuQ24591@momjian.us
Whole thread Raw
In response to Re: pg_upgrade  (Tory M Blue <tmblue@gmail.com>)
Responses Re: pg_upgrade  (Tory M Blue <tmblue@gmail.com>)
List pgsql-performance
Tory M Blue wrote:
> Bruce is right, I didn't move tablespaces (I didn't know to be honest
> I had to, but it makes sense). I simply moved the location of the data
> files, from /data to /data1. But I did "not", change any sym links or

I was unclear if you moved the data directory or the tablespace.  Your
example showed you moving something that didn't look like data
directories:

    > So I move olddata to /data1/db
    > oldbinary to /pgsql8/bin
    >
    > new 9.1.1 db goes to /data/db
    > newbinary installs at /pgsql/
    >
    > So when I run pg_upgrade (check validates the config), however trying
    > to the upgrade nets;
    > Restoring user relation files
    >   /data/queue/16384/16406
    > error while copying queue.adm_version (/data/queue/16384/16406 to
    > /data/queue/PG_9.1_201105231/16407/16406): No such file or directory
    > Failure, exiting

/data/db and /data/queue are not data locations, or at least they are
not ones we create during the install.  Was the real data directory and
the tablespaces all under /data1?  Did you define these tablespace
locations using relative paths?

> do any other pre-steps, other than install the new binary, make sure
> that there was a new and old data location as well as a new and old
> binary location.

You can definitely move data directories around.

> Since my build processes installs data files at /data and binary at
> /pgsql/, I simply moved the old Data and binaries, before installing
> my new build. So /pgsql/ became /pgsql8/ and /data/ became /data1/

I think you can do that but your error messages don't say that.

> I do understand what you are all saying in regards to the tablespace
> links and tablespace locations.It made total sense when Bruce pointed
> it out initially. However I'm not sure if I should of known that
> pg_upgrade doesn't handle this, and or this would be a concern.
> pg_upgrade asks for old and new locations, so one would think that
> this information would be used for the upgrade process, including
> potentially changing tablespace paths during the migration step
> <shrug>, this is above my pay grade.

There is no Postgres support for moving tablespaces, so it isn't
surprising that pg_upgrade doesn't handle it.

> 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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

pgsql-performance by date:

Previous
From: Ernesto Quiñones
Date:
Subject: Re: Question about VACUUM
Next
From: Tory M Blue
Date:
Subject: Re: pg_upgrade