Re: pg_upgrade & tablespaces

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.

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?

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

>
> 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