Re: pg_upgrade & tablespaces

On 12/27/2013 06:50 AM, Joseph Kregloh wrote:
>
> So how are you moving the 9.0 directory?
>
>
> Just using a mv command like below:
> mv /usr/local/pgsql/data /usr/local/pgsql_90/data
>
> Then I recreated the symlinks in /pg_tbspc to point to the new directory
> path.

Ah, now I see the problem, I think. As was noted upstream having a user
tablespace in the PGDATA would seem to be the issue.

If you do SELECT * from pg_tablespace in the 9.0 install before moving
and after you will see that spclocation does not change and points to
the original PGDATA/drupal_dbspace. Creating the Postgres 9.3 instance
in the old location then basically slides the new under the old. This is
where you get this error:

Copying user relation files
.../pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790
error while copying relation "pg_catalog.pg_largeobject"
("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790" to
"/usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16421/12301"): No
such file or directory
Failure, exiting

Postgres is going to /usr/local/pgsql/data/drupal_dbspace/ to look for
the 9.0 files instead of /usr/local/pgsql_90/data/drupal_dbspace/ and is
trying to copy them as 9.3 versions into the new default location which
has the same path. Since the new
/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051 is empty it is
failing.

Not sure of the best solution, others may have better ideas.

On thing that came to mind is to give pg_upgrade what it wants, the 9.0
tablespace in the default
location(/usr/local/pgsql/data/drupal_dbspace/). In other words make a
symlink:

/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051

to

/usr/local/pgsql_90/data/drupal_dbspace/PG_9.0_201008051

FYI, some testing showed that playing around with spclocation in
pg_tablespace is not recommended.

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