Re: [GENERAL] pg_upgrade & tablespaces

On 01/11/2014 10:55 AM, Bruce Momjian wrote:
> 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 "
>               "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+?

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

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.

>
> 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:)

-- 
Adrian Klaver
adrian.klaver@gmail.com