Re: pg_upgrade & tablespaces

Here is the output of my last test run:

[pgsql(at)postgres-93-upgrade ~]$ time 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
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Setting oldest multixact ID on new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
ok
Removing support functions from new cluster ok
Copying user relation files
...l/data/drupal_dbspace/PG_9.0_201008051/2752430/10913518
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
Failure, exiting

real 2m10.913s
user 0m5.691s
sys 0m10.525s

--------------------------

Listing of that directory in the 9.0 folder:
[pgsql(at)postgres-93-upgrade ~]$ ls -la
/home/jkregloh/pg_data/data/drupal_dbspace/
total 19
drwx------ 4 pgsql pgsql 4 Jun 8 2013 .
drwx------ 38 pgsql pgsql 46 Dec 19 20:18 ..
drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051

--------------------------
Listing of that directory in the 9.3 folder:
[pgsql(at)postgres-93-upgrade ~]$ ls -ls /usr/local/pgsql/data/drupal_dbspace/
total 4
4 drwx------ 3 pgsql pgsql 3 Dec 19 20:18 PG_9.3_201306121

So what I get from this is that it does create the correct 9.3 files in the
new location, however it cannot copy the relation over because the old data
is in the /home/jkregloh/pg_data/data/drupal_dbspace/ not in
/usr/local/pgsql/data/drupal_dbspace/ as the pg_upgrade query tries to do.

-Joseph

On Thu, Dec 19, 2013 at 12:02 PM, Ziggy Skalski <zskalski(at)afilias(dot)info>wrote:

> On 13-12-19 11:34 AM, Joseph Kregloh wrote:
>
> Hello,
>
> I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade
> utility. I need to use pg_upgrade because my production database is 800GB+
> and with over 80 tablespaces and doing an export from 9.0 and importing to
> 9.3 would take at least 2 days.
>
> Currently I am testing on the development database which is only 100GB
> with a same number of tablespaces. I am working on FreeBSD with jails. So
> one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data
> and binary directories for the 9.0 jail.
>
> Here is the command to check:
> pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
> -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c
>
> As you can see the data and binary files for 9.0 are in
> /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides
> in the default location.
>
> When running the check it reports that both clusters are compatible.
> Once the actual process starts it will work fine until it starts up the 9.3
> to copy data over. The problem that I am having is that pg_upgrade is
> creating the 93XXXX files under the old directory and not the new one. So
> when 9.3 goes to import it doesn't find anything.
>
> Now, both versions can't share the same /data directory for obvious
> reasons. Is there any way to make pg_upgrade actually export the new 9.3
> files into the 9.3 directory supplied in the pg_upgrade command? I am also
> open to any other upgrade ideas.
>
> Thanks,
> Joseph
>
>
> Hi Joseph,
>
> Can you post your actual command syntax when you run the upgrade (not the
> check)? Maybe there'll be something wrong there we can spot.
> When I did it recently, I used something along the lines of:
>
> (PG93path)/pg_upgrade -d /opt/rg/data/pgsql90 -D /opt/rg/data/pgsql93 -b
> /(path to PG90 binaries)/bin -B /(path to 93 binaries) -v -p (oldport) -P
> (newport)
>
> Ziggy
>
>