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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +