Re: pg_upgrade & tablespaces

On Thu, Dec 19, 2013 at 6:19 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 12/19/2013 01:50 PM, Joseph Kregloh wrote:
>
>> On Thu, Dec 19, 2013 at 4:14 PM, John R Pierce <pierce(at)hogranch(dot)com
>> <mailto:pierce(at)hogranch(dot)com>> wrote:
>>
>> On 12/19/2013 1:06 PM, Joseph Kregloh wrote:
>>
>> It's easier to keep things segregated. It is not anymore
>> different than doing the upgrade in the same jail. Which at the
>> end of the day you are doing the upgrade in the same jail,
>> because at the end of the day pg_upgrade just needs the old data
>> an binary to start and create some dump files.
>>
>>
>> pg_upgrade needs to access the old data AND all the tablespaces at
>> the same paths as the old server sees them AND the new data and
>> tablespaces at the same path as the NEW server sees them. if the
>> two servers are in different jails, I don't see how you could make
>> that work... if you run pg_upgrade in the host system, then all the
>> paths are different for both sets of data and tablespaces.
>>
>>
>> I understand that it will need to access the old data and new data data
>> as it sees it, but it is seeing everything as /usr/local/pgsql/data. Now
>> lets say I have both versions 9.0 and 9.3 installed in the same jail.
>> They will both need to use /usr/local/pgsql/data to access the physical
>> data. But that will not work because all of the Postgres related files
>> are in there, so you can only have 9.0 OR 9.3 use the
>> /usr/local/pgsql/data directory.
>>
>
> No, that is not the case. The data directory can be different for
> different instances, it is a configure option. In fact the pg_upgrade docs
> point that out:
>
> http://www.postgresql.org/docs/9.3/interactive/pgupgrade.html
>
> See:
>
> Usage
>
> Steps 1-3
>
>
>>
That is exactly how I have been running the upgrades. These are two of my
test cases:

Case A:

pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -d
/home/jkregloh/pg_data/data -D /usr/local/pgsql/data/ -p 5452 -P 5451

I end up with the error:

error while copying relation "pg_catalog.pg_largeobject"
("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518"
to "/usr/local/pgsql/data/drupal_dbspace PG_9.3_201306121/16499/12301"): No
such file or directory

Because PG_9.0_201008051/2752430/10913518 is actually in the old cluster
(/home/jkregloh/pg_data/data). I am unsure if pg_upgrade is supposed to
copy those folders to the new cluster or read them from the old location.
Neither of which happens.

Case B:

pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -D
/usr/local/pgsql_93/data -d /usr/local/pgsql/data/ -P 5452 -p 5451

In this case, the OLD cluster is in the default location and the new one in
/usr/local/pgsql_93/. This will complete successfully. HOWEVER in
/usr/local/pgsql/data I will be left with the PG_9.0 and PG_9.3 folders,
then the 9.3 cluster does not have access to any of this because it's
install location is /usr/local/pgsql_93/data. I would either have to copy
all of the data over to the new /usr/local/pgsql_93/data or the inverse.
Which in any case would be pretty messy and error prone.

I am open to suggestions if anyone has any ideas of what to try.

Thanks,
-Joseph

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