Re: pg_upgrade & tablespaces

On Fri, Dec 20, 2013 at 10:26 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 12/20/2013 06:54 AM, Joseph Kregloh wrote:
>
>>
>>
>>
>> On Thu, Dec 19, 2013 at 6:19 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com
>> <mailto: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>
>> <mailto: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
>> <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.
>>
>
> At this point I am confused, so I will try to summarize the issue to date
> and you can indicate whether I am correct or not
>
> 1) You are doing a test upgrade from 9.0 to 9.3 using pg_upgrade
>
> 2) You are using two BSD jails, one of which holds the 9.0 instance and
> the other the 9.3 instance.
>
> 3) The upgrade is being run from the 9.3 jail against 9.0 /bin and /data
> directories that are mounted in the 9.3 jail
>
> 4) Your original attempts failed because pg_upgrade is confused about
> which directory to copy from/to
>
> 5) Your latest attempt sort of succeeded, but left you with both 9.0 and
> 9.3 data directories in /usr/local/pgsql/data which is supposed to be the
> 9.0 /data.
>
>
Correct on all points above.

> Now my questions:
>
> 1) Still on the case of the port numbers. In your first example port 5451
> is associated with the 9.3 instance, in the second with the 9.0 instance
> and the reverse for port 5453. Is that really the case?
>
>
It should be 5452 for the old port. That was a copy/paste from one of my
first attempts. But the ports I am using are 5451 for 9.3 and 5452 for 9.0.
Sorry about that confusion.

> 2) Have you tried what has been suggested which is locating both instances
> inside one jail directly, without the mount redirection?
>

Yes I have tried that with the same results.

>
>
>> Thanks,
>> -Joseph
>>
>>
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)gmail(dot)com <mailto:adrian(dot)klaver(at)gmail(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>