Re: pg_upgrade does not translate tablespace location to new cluster - Mailing list pgsql-general

On Fri, 2011-07-01 at 16:24 +0200, Olivier LEVESQUE wrote:
> Hi,
>
> I am currently testing pg_upgrade (in copy mode)  to migrate a 8.4.4
> old cluster to a new 9.0.3 one.
> It runs fine except when databases contain tablespaces
>
>
> $pg_upgrade -d /pgqdata/pgserver01/data -D /pgqdata/pgserver02/data -b
> /opt/pgsql/na/8.4.4/bin -B /opt/pgsql/na/9.0.3/bin -p 5432 -P 5433
>
> Performing Consistency Checks
> -----------------------------
> Checking old data directory (/pgqdata/pgserver01/data)      ok
> Checking old bin directory (/opt/pgsql/na/8.4.4/bin)        ok
> Checking new data directory (/pgqdata/pgserver02/data)      ok
> Checking new bin directory (/opt/pgsql/na/9.0.3/bin)        ok
> Checking for reg* system oid user data types                ok
> Checking for /contrib/isn with bigint-passing mismatch      ok
> Checking for large objects                                  ok
> Creating catalog dump                                       ok
> Checking for presence of required libraries                 ok
>
> | If pg_upgrade fails after this point, you must
> | re-initdb the new cluster before continuing.
> | You will also need to remove the ".old" suffix
> | from /pgqdata/pgserver01/data/global/pg_control.old.
>
> Performing Migration
> --------------------
> Adding ".old" suffix to old global/pg_control               ok
> Analyzing all rows in the new cluster                       ok
> Freezing all rows on the new cluster                        ok
> Deleting new commit clogs                                   ok
> Copying old commit clogs to new server                      ok
> Setting next transaction id for new cluster                 ok
> Resetting WAL archives                                      ok
> Setting frozenxid counters in new cluster                   ok
> Creating databases in the new cluster
> psql:/opt/pgsql/bin/pg_upgrade_dump_globals.sql:38: ERROR:  directory
> "/pgqdata/pgserver01/data/tbs_ptest/PG_9.0_201008051" already in use
> as a tablespace
>

That would mean that you have a 9.0 tablespace in
the /pgqdata/pgserver01/data/tbs_ptest directory. Is it true? and IIUC
your directory layout, it shouldn't.

> The failing command in pg_upgrade_dump_globals.sql is:
>
> CREATE TABLESPACE tbs_ptest OWNER ptestowner LOCATION
> '/pgqdata/pgserver01/data/tbs_ptest';
>
> ===> I would prefer that pg_upgrade translate the location to one in
> the new cluster data directory (PGDATA), i.e.
> /pgqdata/pgserver02/data/tbs_ptest
>

It would work for you if all the tablespaces in pgserver01 are in the
same base directory, and all the tablespaces in pgserver02 should be in
the same base directory. This is a very limited use case.

> Does anyone know a possible workaround for this problem ?
>

The only way is to change the code. It shouldn't be too hard, but is
potentially dangerous.

Otherwise, nope.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com