Re: [GENERAL] pg_upgrade & tablespaces

On Sat, Jan 11, 2014 at 10:40:20AM -0800, Adrian Klaver wrote:
> >Right.  I know there were multiple issue with this upgrade, jails
> >probably being the biggest, but a new one I had never heard is that _if_
> >you are placing your tablespaces in the PGDATA directory, and you are
> >upgrading from pre-9.2, if you rename the old data directory, you also
> >need to start the old server and update pg_tablespace.spclocation.
> >
> >No one has ever reported that failure, but it would certainly happen.  I
> >wonder if pg_upgrade should be modified to check that
> >pg_tablespace.spclocation point to real directories for pre-9.2 servers.
> >
> 
> I thought I was understanding, now I am not. This starts with your
> post of last night. So in pre-9.2 cases the tablespace location is
> recorded in two places pg_tablespace and the symlinks in pg_tblspc/.

[ I am moving this discussion to hackers to get developer feedback. ]

Right.

> When you upgrade pg_upgrade only looks at the pg_tablespace  entry
> for pre-9.2 instances or does it look at the pg_tblspc symlinks
> also? If it looks at the symlinks would they need to be changed
> also?

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 "
"WHEREspcname != '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");

> As to your check for directories that sounds like a good idea,
> though I have one question. What constitutes a 'real' directory? I
> can see a situation where someone moves an existing instance from
> $PGDATA to $PGDATA.old and the installs a new version in $PGDATA.
> Then before they do the upgrade they create a new tablespace
> directory in $PGDATA. If they did not upgrade the spclocation in the
> old instance and ran the check it would find a directory but there
> would be nothing in it. So would the check look for actual
> tablespace data?

I would probably just look for the directory.  People are not supposed
to be modifying their clusters during the upgrade, though, as stated, if
they move the old cluster, the are required to update pg_tablespace if
they have tablespaces in PGDATA, which is unfortunate.

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.

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.

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