Re: [GENERAL] pg_upgrade & tablespaces

On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
> >pg_upgrade looks in the pg_tablespace in pre-9.2, and uses a function in
> >9.2+.  The query is:
> >
> >     snprintf(query, sizeof(query),
> >              "SELECT    %s "
> >              "FROM  pg_catalog.pg_tablespace "
> >              "WHERE spcname != 'pg_default' AND "
> >              "      spcname != 'pg_global'",
> >     /* 9.2 removed the spclocation column */
> >              (GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
> >--> "spclocation" : "pg_catalog.pg_tablespace_location(oid) AS spclocation");
> 
> 
> I see, though I have another question. If pg_tablespace and the
> symlinks can get out of sync, as you say below, why is pg_tablespace
> considered the authority? Or to put it another way, why not just
> look at the symlinks as in 9.2+?

Uh, good question.  I think I used the system tables because they were
easier to access.  I can't remember if we used the symlinks for some
things and pg_tablespace for other things in pre-9.2.

> >I think the big question on adding a check is, how many users of 9.4 are
> >going to be upgrading from pre-9.2 and have tablespaces in PGDATA, and
> >will be renaming their old PGDATA directory during the upgrade?  We
> >could add the test to 9.3 too, of course.
> 
> Well it is not generally accepted that users should even be creating
> tablespaces in $PGDATA, but it is allowed by the program. My
> inclination is to say that it is then the programs'(Postgres)
> responsibility to deal with it. The alternative is to clarify the
> documentation and make it the users responsibility. As to users
> upgrading from 9.1- to 9.2+, I see still a lot of users posting to
> --general using 9.1- versions. At some point they will likely
> migrate, so I can see a fix being worthwhile.

True.

> >Having pg_tablespace and the symlinks get out of sync was the reason
> >Magnus removed that duplication in 9.2, but I was unaware of how
> >pg_upgrade really magnifies the problem for tablespaces in PGDATA by
> >recommending a PGDATA rename.
> >
> 
> Well it was based on the valid assumption that people would create
> new tablespaces outside $PGDATA because that is really is what is
> meant to happen. You know us users we like to test assumptions:)

Also true.  :-)

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +