pg_upgrade does not translate tablespace location to new cluster

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

Guillaume,

Thank you for your answer,

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.

Yes, you're right. This directory was here from my last succesfull pg_upgrade.

But the problem is that now, data of the new cluster is scattered
across two paths which is not what I expected.

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

Why dangerous ? It could be an option.

Regards,
--
Olivier LEVESQUE

On Fri, 2011-07-01 at 18:30 +0200, Olivier LEVESQUE wrote:

Guillaume,

Thank you for your answer,

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.

Yes, you're right. This directory was here from my last succesfull pg_upgrade.

But the problem is that now, data of the new cluster is scattered
across two paths which is not what I expected.

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

Why dangerous ? It could be an option.

I didn't mean the option is dangerous. My point is: I don't think it
would be interesting to add this change to the mainstream pg_upgrade
because the usecase is very limited (others will correct me if I'm
wrong), so the other option is to code it yourself. And this might be
dangerous (because of very limited tests, and so forth).

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

Guillaume Lelarge wrote:

On Fri, 2011-07-01 at 18:30 +0200, Olivier LEVESQUE wrote:

Guillaume,

Thank you for your answer,

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.

Yes, you're right. This directory was here from my last succesfull pg_upgrade.

But the problem is that now, data of the new cluster is scattered
across two paths which is not what I expected.

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

Why dangerous ? It could be an option.

I didn't mean the option is dangerous. My point is: I don't think it
would be interesting to add this change to the mainstream pg_upgrade
because the usecase is very limited (others will correct me if I'm
wrong), so the other option is to code it yourself. And this might be
dangerous (because of very limited tests, and so forth).

Guillaume, I agree with your analysis.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, 2011-07-18 at 16:32 -0400, Bruce Momjian wrote:

Guillaume Lelarge wrote:

On Fri, 2011-07-01 at 18:30 +0200, Olivier LEVESQUE wrote:

Guillaume,

Thank you for your answer,

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.

Yes, you're right. This directory was here from my last succesfull pg_upgrade.

But the problem is that now, data of the new cluster is scattered
across two paths which is not what I expected.

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

Why dangerous ? It could be an option.

I didn't mean the option is dangerous. My point is: I don't think it
would be interesting to add this change to the mainstream pg_upgrade
because the usecase is very limited (others will correct me if I'm
wrong), so the other option is to code it yourself. And this might be
dangerous (because of very limited tests, and so forth).

Guillaume, I agree with your analysis.

Thanks :)

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

On 07/18/11 1:59 PM, Guillaume Lelarge wrote:

Guillaume, I agree with your analysis.

Thanks :)

Can we please trim quotes on responses? We really don't need a
regurgitation of the entire 40 preceding messages to say 'thanks', just
the part that you are referencing will suffice.

If you use Thunderbird, a handy feature is to select just the part you
want to quote before hitting 'reply' and it will just include that, this
is even easier than deleting the parts you don't want.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast