Re: [GENERAL] pg_upgrade & tablespaces

On 01/12/2014 07:02 PM, Bruce Momjian wrote:
> On Sun, Jan 12, 2014 at 12:48:40PM -0500, Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
>>>> 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.
>>
>> Well, pre-9.2 pg_dumpall is going to make use of the pg_tablespace
>> entries, because it has no other choice. We could conceivably teach
>> pg_upgrade to look at the symlinks for itself, but we're not going
>> to do that in pg_dumpall. Which means that the intermediate dump
>> script would contain inconsistent location values anyway if the
>> catalog entries are wrong. So I don't see any value in changing the
>> quoted code in pg_upgrade.
>
> OK, agreed.
>
>> It does however seem reasonable for pg_upgrade to note whether any
>> of the paths are prefixed by old PGDATA and warn about the risks
>> involved.
>
> Uh, the problem is that once you rename the old PGDATA, the
> pg_tablespace contents no longer point to the current PGDATA. The
> symlinks, if they used absolute paths, wouldn't point to the current
> PGDATA either.
>

Well the problem is that it actually points to a current PGDATA just the
wrong one. To use the source installation path and the suggested upgrade
method from pg_upgrade.

Start.

/usr/local/pgsql/data/tblspc_dir

mv above to

/usr/local/pgsql_old/

install new version of Postgres to

/usr/local/pgsql/data/

In the pgsql_old installation you have symlinks pointing back to the
current default location. As well pg_tablespace points back to
/usr/local/pgsql/data/ The issue is that there is not actually anything
there in the way of a tablespace. So when pg_upgrade runs it tries to
upgrade from /usr/local/pgsql/data/tblspc_dir to
/usr/local/pgsql/data/tblspc_dir where the first directory either does
not exist. or if the user went ahead and created the directory in the
new installation, is empty. What is really wanted is to upgrade from
/usr/local/pgsql_old/data/tblspc_dir to
/usr/local/pgsql/data/tblspc_dir. Right now the only way that happens is
with user intervention.

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