Re: pg_upgrade & tablespaces

On 01/11/2014 08:18 AM, Bruce Momjian wrote:
> On Sat, Jan 11, 2014 at 06:43:16AM -0800, Adrian Klaver wrote:
>>>> Well the issue seems to be with 9.0. I am not exactly sure where
>>>> pg_upgrade is pulling its information, but I am guessing from the
>>>> error message that on the 9.0 side of things it is using
>>>> spclocation. In the OPs situation that is no longer valid for 9.0
>>>> once its data directory is moved. The special circumstance here
>>>> being that the user tablespace is in PGDATA. I would welcome
>>>> enlightenment on this.
>>>
>>> The problem is that pre-9.2 recorded the tablespace location in
>>> pg_tablespace and in the symlink. When the pg_upgrade instructions tell
>>> you to rename the old database cluster, it doesn't remind pre-9.2 users
>>> to update in-PGDATA tablespaces.
>>
>> Just so I understand, this is update spclocation in pg_upgrade in
>> the pre-9.2 database.
>
> 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/. When you
upgrade pg_upgrade only looks at the pg_tablspace 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?

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?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com