Re: pg_upgrade & tablespaces

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