Re: pg_upgrade & tablespaces

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

On 12/19/2013 08: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

The only thing I have is, are the port numbers correct? I tend to use
larger numbers for newer versions which, is why I am asking.

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

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

Yes, the port numbers are correct. Both instances start by themselves on
their own jails.

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

> On 12/19/2013 08: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
>>
>
> The only thing I have is, are the port numbers correct? I tend to use
> larger numbers for newer versions which, is why I am asking.
>
>
>> 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
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

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

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

On 12/19/2013 12:27 PM, Joseph Kregloh wrote:
> Here is the output of my last test run:
>

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

So what does mount show?

>
> -Joseph
>>
>

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

I'm not sure what you mean by that question.

-Joseph

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

> On 12/19/2013 12:27 PM, Joseph Kregloh wrote:
>
>> Here is the output of my last test run:
>>
>>
>
>> 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.
>>
>
> So what does mount show?
>
>
>> -Joseph
>>
>>>
>>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh(at)sproutloud(dot)com> wrote:
> 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.

Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints, please?

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com

[pgsql(at)postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
/home/jkregloh/pg_data/data/stats_dbspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
/home/jkregloh/pg_data/data/stats_indexspace
lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
/home/jkregloh/pg_data/data/stats_staging_dbspace
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 11047392 ->
/home/jkregloh/pg_data/data/stats_staging_indexspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 22319 ->
/home/jkregloh/pg_data/data/datapipe_dbspace
lrwxr-xr-x 1 pgsql pgsql 47 Dec 19 19:53 22320 ->
/home/jkregloh/pg_data/data/datapipe_indexspace
lrwxr-xr-x 1 pgsql pgsql 46 Dec 19 19:53 22321 ->
/home/jkregloh/pg_data/data/datapipe_zlogspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 22322 ->
/home/jkregloh/pg_data/data/p3_basic_dbspace
lrwxr-xr-x 1 pgsql pgsql 47 Dec 19 19:53 22323 ->
/home/jkregloh/pg_data/data/p3_basic_indexspace
lrwxr-xr-x 1 pgsql pgsql 38 Dec 19 19:53 22324 ->
/home/jkregloh/pg_data/data/p3_dbspace
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 22325 ->
/home/jkregloh/pg_data/data/p3_indexspace
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 22326 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_001
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 22327 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_001
lrwxr-xr-x 1 pgsql pgsql 43 Dec 19 19:53 22328 ->
/home/jkregloh/pg_data/data/p3_zlog_dbspace
lrwxr-xr-x 1 pgsql pgsql 46 Dec 19 19:53 22329 ->
/home/jkregloh/pg_data/data/p3_zlog_indexspace
lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 22330 ->
/home/jkregloh/pg_data/data/sling_dbspace
lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 22331 ->
/home/jkregloh/pg_data/data/sling_indexspace
lrwxr-xr-x 1 pgsql pgsql 51 Dec 19 19:53 2260532 ->
/home/jkregloh/pg_data/data/p3_olap_staging_dbspace
lrwxr-xr-x 1 pgsql pgsql 54 Dec 19 19:53 2260533 ->
/home/jkregloh/pg_data/data/p3_olap_staging_indexspace
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 2283998 ->
/home/jkregloh/pg_data/data/p3_olap_datamart_dbspace
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 2283999 ->
/home/jkregloh/pg_data/data/p3_olap_datamart_indexspace
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327012 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_002
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327013 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_003
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327014 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_004
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327015 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_005
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327016 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_006
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327017 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_007
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327018 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_008
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327019 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_009
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 2327020 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_data_010
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327021 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_002
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327022 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_003
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327023 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_004
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327024 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_005
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327025 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_006
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327026 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_007
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327027 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_008
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327028 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_009
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 2327029 ->
/home/jkregloh/pg_data/data/p3_dd_tablespaces/list_index_010
lrwxr-xr-x 1 pgsql pgsql 42 Dec 19 19:53 2752416 ->
/home/jkregloh/pg_data/data/drupal_dbspace
lrwxr-xr-x 1 pgsql pgsql 45 Dec 19 19:53 2796385 ->
/home/jkregloh/pg_data/data/drupal_indexspace
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819045 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/january
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819046 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/february
lrwxr-xr-x 1 pgsql pgsql 53 Dec 19 19:53 5819047 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/march
lrwxr-xr-x 1 pgsql pgsql 53 Dec 19 19:53 5819048 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/april
lrwxr-xr-x 1 pgsql pgsql 51 Dec 19 19:53 5819049 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/may
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 5819050 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/june
lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 5819051 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/july
lrwxr-xr-x 1 pgsql pgsql 54 Dec 19 19:53 5819052 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/august
lrwxr-xr-x 1 pgsql pgsql 57 Dec 19 19:53 5819053 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/september
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819054 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/october
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819055 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/november
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819056 ->
/home/jkregloh/pg_data/data/p3_ord_list_dbspace/december
lrwxr-xr-x 1 pgsql pgsql 58 Dec 19 19:53 5819057 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/january
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 5819058 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/february
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819059 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/march
lrwxr-xr-x 1 pgsql pgsql 56 Dec 19 19:53 5819060 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/april
lrwxr-xr-x 1 pgsql pgsql 54 Dec 19 19:53 5819061 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/may
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819063 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/june
lrwxr-xr-x 1 pgsql pgsql 55 Dec 19 19:53 5819064 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/july
lrwxr-xr-x 1 pgsql pgsql 57 Dec 19 19:53 5819065 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/august
lrwxr-xr-x 1 pgsql pgsql 60 Dec 19 19:53 5819066 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/september
lrwxr-xr-x 1 pgsql pgsql 58 Dec 19 19:53 5819067 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/october
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 5819068 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/november
lrwxr-xr-x 1 pgsql pgsql 59 Dec 19 19:53 5819070 ->
/home/jkregloh/pg_data/data/p3_ord_list_indexspace/december
lrwxr-xr-x 1 pgsql pgsql 43 Dec 19 19:53 764614 ->
/home/jkregloh/pg_data/data/clients_dbspace
lrwxr-xr-x 1 pgsql pgsql 46 Dec 19 19:53 764617 ->
/home/jkregloh/pg_data/data/clients_indexspace
lrwxr-xr-x 1 pgsql pgsql 45 Dec 19 19:53 764620 ->
/home/jkregloh/pg_data/data/clients_zlogspace
lrwxr-xr-x 1 pgsql pgsql 47 Dec 19 19:53 9296296 ->
/home/jkregloh/pg_data/data/clients_report_data
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669440 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2006
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669441 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2007
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669442 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2008
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669443 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2009
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669444 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2010
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669445 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2011
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669446 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2012
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669447 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2013
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669448 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2014
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669449 ->
/home/jkregloh/pg_data/data_archive/dd_archive/tablespaces/2015
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669450 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2006
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669451 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2007
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669452 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2008
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669453 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2009
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669454 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2010
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669455 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2011
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669456 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2012
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669457 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2013
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669458 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2014
lrwxr-xr-x 1 pgsql pgsql 63 Dec 19 19:53 9669459 ->
/home/jkregloh/pg_data/data_archive/dd_archive/indexspaces/2015

-Joseph

On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
> <jkregloh(at)sproutloud(dot)com> wrote:
> > 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.
>
> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
> please?
>
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray(dot)ru(at)gmail(dot)com
>

On 12/19/2013 12:46 PM, Joseph Kregloh wrote:
> I'm not sure what you mean by that question.

When you run the mount command in the jail what does it show?

>
> -Joseph
>
>
>

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

Within the jail it would be:
[pgsql(at)postgres-93-upgrade ~]$ mount
sata-data/usr/jails/postgres-93-upgrade on / (zfs, local, nfsv4acls)

But I am mounting those directories from the host, which will be:
[root(at)v1 /postgres_data/p3-dev-db-93]# mount -l | grep postgres-93-upgrade
sata-data/usr/jails/postgres-93-upgrade on /usr/jails/postgres-93-upgrade
(zfs, local, nfsv4acls)
/usr/jails/basejail on /usr/jails/postgres-93-upgrade/basejail (nullfs,
local, read-only)
devfs on /usr/jails/postgres-93-upgrade/dev (devfs, local, multilabel)
fdescfs on /usr/jails/postgres-93-upgrade/dev/fd (fdescfs)
procfs on /usr/jails/postgres-93-upgrade/proc (procfs, local)
/usr/jails/postgres-90-upgrade/usr/local/bin on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_bin (nullfs, local)
/dev_db/stop_db/postgres_data on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data (nullfs, local)
/dev_db/stop_db/postgres_archive_data on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data_archive (nullfs,
local)

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

> On 12/19/2013 12:46 PM, Joseph Kregloh wrote:
>
>> I'm not sure what you mean by that question.
>>
>
> When you run the mount command in the jail what does it show?
>
>
>> -Joseph
>>
>>
>>
>>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

On Thu, Dec 19, 2013 at 11:34:24AM -0500, 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.

Why don't you do run pg_upgrade in the same jail then just move the
files over to the new jail? That should work better. I am unclear how
a cross-jail upgrade would work at all.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 12/19/2013 12:53 PM, Bruce Momjian wrote:
>> 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.
> Why don't you do run pg_upgrade in the same jail then just move the
> files over to the new jail? That should work better. I am unclear how
> a cross-jail upgrade would work at all.

or just leave the 9.3 in the 'postgres' jail, which to me makes as much
sense as anything.

80 tablespaces is a mess no matter how you slice it.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

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.

But the real problem here is with the table spaces. Because in order to
copy the relation over I would need to mount the old data to the
/usr/local/pgsql/data on the new jail. The relation would be there and
would finish successfully(I did this exercise). However the 9.3 install
would be in a different directory, say /usr/local/pgsql_93 and will not
have the data files because they now live in the old install location.

-Joseph

On Thu, Dec 19, 2013 at 3:53 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Dec 19, 2013 at 11:34:24AM -0500, 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.
>
> Why don't you do run pg_upgrade in the same jail then just move the
> files over to the new jail? That should work better. I am unclear how
> a cross-jail upgrade would work at all.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>

On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
<jkregloh(at)sproutloud(dot)com> wrote:
> On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
>> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
>> <jkregloh(at)sproutloud(dot)com> wrote:
>> > 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.
>>
>> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
>> please?
>>
> [pgsql(at)postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
> lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
> /home/jkregloh/pg_data/data/stats_dbspace
> lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
> /home/jkregloh/pg_data/data/stats_indexspace
> lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
> /home/jkregloh/pg_data/data/stats_staging_dbspace

Bruce, may be it's a silly question, but the above makes me think so.
I always keep tablespaces in locations different from the main data
dir, and never faced something like this.

Doesn't pg_upgrade do a stright replace of -d dir with -D dir
everywhere in paths?

ps. Joseph, please, don't use top-posting, see
http://en.wikipedia.org/wiki/Posting_style#Interleaved_style.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com

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.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 12/19/2013 01: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.
>
> But the real problem here is with the table spaces. Because in order to
> copy the relation over I would need to mount the old data to the
> /usr/local/pgsql/data on the new jail. The relation would be there and
> would finish successfully(I did this exercise). However the 9.3 install
> would be in a different directory, say /usr/local/pgsql_93 and will not
> have the data files because they now live in the old install location.

Not sure all of this but I do have this question:

In your original post you have:

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

Note: -d /home/jkregloh/pg_data/

In your mount info you have:

/dev_db/stop_db/postgres_data on
/usr/jails/postgres-93-upgrade/home/jkregloh/pg_data/data (nullfs, local)

If I am following correctly should it not be:

-d /home/jkregloh/pg_data/data

>
> -Joseph
>
>

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

On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote:
> On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
> <jkregloh(at)sproutloud(dot)com> wrote:
> > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
> >> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
> >> <jkregloh(at)sproutloud(dot)com> wrote:
> >> > 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.
> >>
> >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
> >> please?
> >>
> > [pgsql(at)postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
> > lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
> > /home/jkregloh/pg_data/data/stats_dbspace
> > lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
> > /home/jkregloh/pg_data/data/stats_indexspace
> > lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
> > /home/jkregloh/pg_data/data/stats_staging_dbspace
>
> Bruce, may be it's a silly question, but the above makes me think so.
> I always keep tablespaces in locations different from the main data
> dir, and never faced something like this.
>
> Doesn't pg_upgrade do a stright replace of -d dir with -D dir
> everywhere in paths?

pg_upgrade is looking at the data dir, the database oid, and relfilenode
to get the old path, and does the same for the new path. Tablespaces
point to the same location in old and new clusters --- only a
subdirectory PG_VERISON is different.

Is /home/jkregloh/pg_data/data also your default cluster directory? If
so, having tablespaces inside of there will not work well as they will
continue to be stored in the old cluster's data directory. Those will
not be renamed/relocated by pg_upgrade.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Dec 19, 2013 at 01:14:15PM -0800, John R Pierce 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.

The big question is should pg_upgrade be checking for this situation in
--check mode, and if so, what should it check for?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, Dec 19, 2013 at 1:18 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote:
>> On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
>> <jkregloh(at)sproutloud(dot)com> wrote:
>> > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
>> >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
>> >> please?
>> >>
>> > [pgsql(at)postgres-93-upgrade ~]$ ls -l /home/jkregloh/pg_data/data/pg_tblspc/
>> > lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
>> > /home/jkregloh/pg_data/data/stats_dbspace
>>
>> Doesn't pg_upgrade do a stright replace of -d dir with -D dir
>> everywhere in paths?
>
> pg_upgrade is looking at the data dir, the database oid, and relfilenode
> to get the old path, and does the same for the new path. Tablespaces
> point to the same location in old and new clusters --- only a
> subdirectory PG_VERISON is different.
>
> Is /home/jkregloh/pg_data/data also your default cluster directory? If
> so, having tablespaces inside of there will not work well as they will
> continue to be stored in the old cluster's data directory. Those will
> not be renamed/relocated by pg_upgrade.

The thing is that /home/jkregloh/pg_data/data is his 9.0's cluster
directory and /usr/local/pgsql/data/ is 9.3's one. And pg_upgrade
tries to copy /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.

In other words pg_upgrade thinks that the old tablespace is located in
the same cluster directory as the new one. That made me think that it
just replaces the cluster directory subpath everywhere.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com

On Thu, Dec 19, 2013 at 4:14 PM, John R Pierce <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.

>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

> On 12/19/2013 01: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.
>>
>> But the real problem here is with the table spaces. Because in order to
>> copy the relation over I would need to mount the old data to the
>> /usr/local/pgsql/data on the new jail. The relation would be there and
>> would finish successfully(I did this exercise). However the 9.3 install
>> would be in a different directory, say /usr/local/pgsql_93 and will not
>> have the data files because they now live in the old install location.
>>
>
> Not sure all of this but I do have this question:
>
> In your original post you have:
>
> 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
>
> Note: -d /home/jkregloh/pg_data/
>
> In your mount info you have:
>
> /dev_db/stop_db/postgres_data on /usr/jails/postgres-93-
> upgrade/home/jkregloh/pg_data/data (nullfs, local)
>
> If I am following correctly should it not be:
>
> -d /home/jkregloh/pg_data/data
>

Yes, you are correct. That's a typo on my part from copy/pasting earlier.

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

On Thu, Dec 19, 2013 at 4:18 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote:
> > On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
> > <jkregloh(at)sproutloud(dot)com> wrote:
> > > On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
> wrote:
> > >> On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
> > >> <jkregloh(at)sproutloud(dot)com> wrote:
> > >> > 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.
> > >>
> > >> Can you show what ls -l /home/jkregloh/pg_data/data/pg_tblspc/ prints,
> > >> please?
> > >>
> > > [pgsql(at)postgres-93-upgrade ~]$ ls -l
> /home/jkregloh/pg_data/data/pg_tblspc/
> > > lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
> > > /home/jkregloh/pg_data/data/stats_dbspace
> > > lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
> > > /home/jkregloh/pg_data/data/stats_indexspace
> > > lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
> > > /home/jkregloh/pg_data/data/stats_staging_dbspace
> >
> > Bruce, may be it's a silly question, but the above makes me think so.
> > I always keep tablespaces in locations different from the main data
> > dir, and never faced something like this.
> >
> > Doesn't pg_upgrade do a stright replace of -d dir with -D dir
> > everywhere in paths?
>
> pg_upgrade is looking at the data dir, the database oid, and relfilenode
> to get the old path, and does the same for the new path. Tablespaces
> point to the same location in old and new clusters --- only a
> subdirectory PG_VERISON is different.
>
> Is /home/jkregloh/pg_data/data also your default cluster directory? If
> so, having tablespaces inside of there will not work well as they will
> continue to be stored in the old cluster's data directory. Those will
> not be renamed/relocated by pg_upgrade.
>
>
No, that is not my default cluster dir. That is just the data directory of
my 9.0 install that I mounted there in order to do the pg_upgrade.
Essentially that points to /usr/local/pgsql/data on my 9.0 jail.

> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>

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

>
>
>
>

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

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
>

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

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
>

On 12/20/2013 07:32 AM, Joseph Kregloh wrote:

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

Great, one less moving part:)

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

Hmmm.

So was your latest attempt where you ended up with a doubled data/ in
the two or one jail scenario?

Can we see a directory listing for that case?

You say in the single jail case you got the same results. Which would
that be the failure, the double data/ or both ?

>
>
>
> Thanks,
> -Joseph
>
>
>
>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com <mailto:adrian(dot)klaver(at)gmail(dot)com>
> <mailto:adrian(dot)klaver(at)gmail(dot)__com <mailto:adrian(dot)klaver(at)gmail(dot)com>>
>
>
>
>
> --
> 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

On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh <jkregloh(at)sproutloud(dot)com>wrote:

> [pgsql(at)postgres-93-upgrade ~]$ ls -l
> /home/jkregloh/pg_data/data/pg_tblspc/
> lrwxr-xr-x 1 pgsql pgsql 41 Dec 19 19:53 11047389 ->
> /home/jkregloh/pg_data/data/stats_dbspace
> lrwxr-xr-x 1 pgsql pgsql 44 Dec 19 19:53 11047390 ->
> /home/jkregloh/pg_data/data/stats_indexspace
> lrwxr-xr-x 1 pgsql pgsql 49 Dec 19 19:53 11047391 ->
> /home/jkregloh/pg_data/data/stats_staging_dbspace
> lrwxr-xr-x 1 pgsql pgsql 52 Dec 19 19:53 11047392 ->
> /home/jkregloh/pg_data/data/stats_staging_indexspace
>

Can you show the same thing on the 9.3 data directory, after the failed
upgrade?

It seems to me that you guys have made a mess out of your disk layout, and
pg_upgrade is struggling to preserve the mess, but failing. What is the
point of having 80 tablespaces, especially if they just point back to the
same place?

Cheers,

Jeff

> So was your latest attempt where you ended up with a doubled data/ in the
> two or one jail scenario?
>
>
The two jails scenario. The two jail scenario is the same as the mounted
scenario.

Can we see a directory listing for that case?
>
> You say in the single jail case you got the same results. Which would that
> be the failure, the double data/ or both ?
>
>
>>
Let's break this down between the two 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

[root(at)postgres-93-upgrade /usr/local/pgsql/data/drupal_dbspace]# ls -la
drwx------ 3 pgsql pgsql 3 Dec 19 20:18 PG_9.3_201306121

[root(at)postgres-93-upgrade /usr/local/pgsql/data/drupal_dbspace]# ls -la
/home/jkregloh/pg_data/data/drupal_dbspace/
drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051

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

[pgsql(at)postgres-93-upgrade /usr/local/pgsql_93/data/drupal_dbspace]$ ls -la
/usr/local/pgsql/data/drupal_dbspace/
drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
drwx------ 3 pgsql pgsql 3 Dec 20 16:44 PG_9.3_201306121

[pgsql(at)postgres-93-upgrade /usr/local/pgsql_93/data/drupal_dbspace]$ ls -la
/usr/local/pgsql_93/data/drupal_dbspace/

drwxr-xr-x 2 pgsql pgsql 2 Dec 20 16:43 .

So it did the changes in the /usr/local/pgsql/data dir. Which contains the
9.0 install. pg_upgrade was almost successful, some stuff it did not do as
I will show at the end of this email.

It created the symlinks for the 9.3 folders:
[pgsql(at)postgres-93-upgrade /usr/local/pgsql_93/data/drupal_dbspace]$ ls -la
/usr/local/pgsql_93/data/pg_tblspc/
lrwx------ 1 pgsql pgsql 36 Dec 20 16:44 16452 ->
/usr/local/pgsql/data/drupal_dbspace

When I start Postgres 9.3:
[pgsql(at)postgres-93-upgrade /usr/local/pgsql_93/data/drupal_dbspace]$
/usr/local/bin/postgres -D /usr/local/pgsql_93/data

I am able to connect to the server however running a simple query I get:
ERROR: relation "sys_errors" does not exist
LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT 100;
^
********** Error **********

ERROR: relation "sys_errors" does not exist
SQL state: 42P01
Character: 15

Below is the output of the execution of Case B:

[pgsql(at)postgres-93-upgrade /tmp]$ 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
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
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh

Could not create a script to delete the old cluster's data
files because user-defined tablespaces exist in the old cluster
directory. The old cluster's contents must be deleted manually.
[pgsql(at)postgres-93-upgrade /tmp]$

On 12/20/2013 10:42 AM, Joseph Kregloh wrote:
>
> So was your latest attempt where you ended up with a doubled data/
> in the two or one jail scenario?
>
>
> The two jails scenario. The two jail scenario is the same as the mounted
> scenario.
>
> Can we see a directory listing for that case?
>
> You say in the single jail case you got the same results. Which
> would that be the failure, the double data/ or both ?
>
>
>
> Let's break this down between the two 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
>
> [root(at)postgres-93-upgrade /usr/local/pgsql/data/drupal_dbspace]# ls -la
> drwx------ 3 pgsql pgsql 3 Dec 19 20:18 PG_9.3_201306121
>
> [root(at)postgres-93-upgrade /usr/local/pgsql/data/drupal_dbspace]# ls -la
> /home/jkregloh/pg_data/data/drupal_dbspace/
> drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051

So that looks like it worked, or am I missing something.

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

You realize order of switches is not important, but case is, where lower
case is old version, upper is new version. I mention this because the
ports are switched again, assuming your previous statement is correct:

" But the ports I am using are 5451 for 9.3 and 5452 for 9.0."

>
> [pgsql(at)postgres-93-upgrade /usr/local/pgsql_93/data/drupal_dbspace]$ ls
> -la /usr/local/pgsql/data/drupal_dbspace/
> drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
> drwx------ 3 pgsql pgsql 3 Dec 20 16:44 PG_9.3_201306121

To me this looks crossed wires, possibly from the crossed ports above.

What has me confused is where /usr/local/pgsql_93/data comes from?
Did you actually install a Postgres 9.3 instance there?
Or is the 9.3 instance installed in the location in Case A
/usr/local/pgsql/data/ ?

The rest of the message I will leave alone as I pretty sure you are
seeing the results of a crossed install.

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

> [root(at)postgres-93-upgrade /usr/local/pgsql/data/drupal_dbspace]# ls -la
>> /home/jkregloh/pg_data/data/drupal_dbspace/
>> drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
>>
>
> So that looks like it worked, or am I missing something.
>

Yes, it works but once it gets to the step where it creates the relations I
get the error:

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

>
>
>> 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
>>
>
> You realize order of switches is not important, but case is, where lower
> case is old version, upper is new version. I mention this because the ports
> are switched again, assuming your previous statement is correct:
>
> " But the ports I am using are 5451 for 9.3 and 5452 for 9.0."
>

Thanks for pointing this out. I reset and ran:

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

Same results as I posted previously for Case B.

>> [pgsql(at)postgres-93-upgrade /usr/local/pgsql_93/data/drupal_dbspace]$ ls
>> -la /usr/local/pgsql/data/drupal_dbspace/
>> drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
>> drwx------ 3 pgsql pgsql 3 Dec 20 16:44 PG_9.3_201306121
>>
>
> To me this looks crossed wires, possibly from the crossed ports above.
>
> What has me confused is where /usr/local/pgsql_93/data comes from?
> Did you actually install a Postgres 9.3 instance there?
> Or is the 9.3 instance installed in the location in Case A
> /usr/local/pgsql/data/ ?
>
>
For Case B I switch it around the /usr/local/pgsql/data belongs to 9.0 and
/usr/local/pgsql_93/data belongs to 9.3. This I found allows me to not get
the same error as I do in Case A.

/usr/local/pgsql_93/data is the data dir of the 9.3 cluster, created using
/usr/local/bin/initdb -D /usr/local/pgsql_93/data

>
> The rest of the message I will leave alone as I pretty sure you are seeing
> the results of a crossed install.
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

On 12/20/2013 02:01 PM, Joseph Kregloh wrote:
>
> [root(at)postgres-93-upgrade
> /usr/local/pgsql/data/drupal___dbspace]# ls -la
> /home/jkregloh/pg_data/data/__drupal_dbspace/
> drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
>
>
> So that looks like it worked, or am I missing something.
>
>
> Yes, it works but once it gets to the step where it creates the
> relations I get the error:
>
> 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

Best guess is /home/jkregloh/pg_data/data/drupal_dbspace/ is pointing
back to /usr/local/pgsql/data in the other jail.

>
>
>
> 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
>
>
> You realize order of switches is not important, but case is, where
> lower case is old version, upper is new version. I mention this
> because the ports are switched again, assuming your previous
> statement is correct:
>
> " But the ports I am using are 5451 for 9.3 and 5452 for 9.0."
>
>
> Thanks for pointing this out. I reset and ran:
>
> 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
>
> Same results as I posted previously for Case B.
>
>
> [pgsql(at)postgres-93-upgrade
> /usr/local/pgsql_93/data/__drupal_dbspace]$ ls
> -la /usr/local/pgsql/data/drupal___dbspace/
> drwx------ 4 pgsql pgsql 4 Oct 20 2011 PG_9.0_201008051
> drwx------ 3 pgsql pgsql 3 Dec 20 16:44 PG_9.3_201306121
>
>
> To me this looks crossed wires, possibly from the crossed ports above.
>
> What has me confused is where /usr/local/pgsql_93/data comes from?
> Did you actually install a Postgres 9.3 instance there?
> Or is the 9.3 instance installed in the location in Case A
> /usr/local/pgsql/data/ ?
>
>
> For Case B I switch it around the /usr/local/pgsql/data belongs to 9.0
> and /usr/local/pgsql_93/data belongs to 9.3. This I found allows me to
> not get the same error as I do in Case A.

>
> /usr/local/pgsql_93/data is the data dir of the 9.3 cluster, created
> using /usr/local/bin/initdb -D /usr/local/pgsql_93/data

And /usr/local/pgsql was re-initdbed with a 9.0 cluster, because
previously it was the 9.3 cluster?

And you are sure /usr/local/bin has the 9.3 binaries?

Personally I would say at this point the relationships between versions
are so confused it would seem best to start from scratch.

My suggestions:

1) Create a new jail with a copy of the test 9.0 cluster located in
/usr/local/pgsql.

2) In that jail install a new 9.3 cluster using the --prefix= switch to
configure to have it install in a different location in the jail.

3) Use pg_upgrade.

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

On 12/20/2013 02:01 PM, Joseph Kregloh wrote:

Ah, nothing like taking the dog for walk to clear the mind. Led to a
thought. To amend my previous post, instead of using the 9.0 cluster you
have been using, why not create a minimal test cluster? In the crawl,
walk, run vein, start with a database with no tablespaces and run
pg_upgrade. See what happens. If that works shutdown the 9.3 cluster ,
reinit it, add a tablespace or two to the 9.0 cluster and try pg_upgrade
again. See what happens. If that works try your existing test setup.

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

On 12/20/2013 4:14 PM, Adrian Klaver wrote:
>
> Personally I would say at this point the relationships between
> versions are so confused it would seem best to start from scratch.

the 80 tablespaces aren't helping this one bit.

I am really curious what lead to creating that many tablespaces? reminds
me of 1990s Oracle databases where disks were small and you used lots of
them, and spread your tables and indexes across many different
drives/mirrors because the raid at the time had performance bottlenecks.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

>
> And /usr/local/pgsql was re-initdbed with a 9.0 cluster, because
> previously it was the 9.3 cluster?
>
> And you are sure /usr/local/bin has the 9.3 binaries?
>
> Personally I would say at this point the relationships between versions
> are so confused it would seem best to start from scratch.
>
> My suggestions:
>
> 1) Create a new jail with a copy of the test 9.0 cluster located in
> /usr/local/pgsql.
>
> 2) In that jail install a new 9.3 cluster using the --prefix= switch to
> configure to have it install in a different location in the jail.
>
> 3) Use pg_upgrade.
>
>
>
Before every test I do a ZFS rollback which resets all data on the disk
back to the last snapshot. So essentially every time it's dealing with a
new install. However I will give a try your suggestions that you had in
your next email (I can see into the future) and report back.

-Thanks

On Fri, Dec 20, 2013 at 7:42 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 12/20/2013 4:14 PM, Adrian Klaver wrote:
>
>>
>> Personally I would say at this point the relationships between versions
>> are so confused it would seem best to start from scratch.
>>
>
> the 80 tablespaces aren't helping this one bit.
>
>
> I am really curious what lead to creating that many tablespaces? reminds
> me of 1990s Oracle databases where disks were small and you used lots of
> them, and spread your tables and indexes across many different
> drives/mirrors because the raid at the time had performance bottlenecks.
>
>
>
Well the original architect started out in the 80s with banking databases
they just kept that model without revisiting if it works well or not, that
might explain it a little bit. But also given the size of our tables we use
the physical disks and filesytem advantages to improve speed and
performance of the application, but not as often as I would like. We have a
pretty big database.

-Joseph

On 12/23/2013 06:45 AM, Joseph Kregloh wrote:
>
>
> And /usr/local/pgsql was re-initdbed with a 9.0 cluster, because
> previously it was the 9.3 cluster?
>
> And you are sure /usr/local/bin has the 9.3 binaries?
>
> Personally I would say at this point the relationships between
> versions are so confused it would seem best to start from scratch.
>
> My suggestions:
>
> 1) Create a new jail with a copy of the test 9.0 cluster located in
> /usr/local/pgsql.
>
> 2) In that jail install a new 9.3 cluster using the --prefix= switch
> to configure to have it install in a different location in the jail.
>
> 3) Use pg_upgrade.
>
>
>
> Before every test I do a ZFS rollback which resets all data on the disk
> back to the last snapshot. So essentially every time it's dealing with a
> new install.

And that has been repeatably proven not to work:) I was suggesting to go
back even further and do not start from the snapshot, but start from a
totally new installation where both instances are in the same jail.

However I will give a try your suggestions that you had in
> your next email (I can see into the future) and report back.

Great, let me know what the answer is:)

>
> -Thanks
>

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

On 12/23/2013 6:50 AM, Joseph Kregloh wrote:
>
>
>
> Well the original architect started out in the 80s with banking
> databases they just kept that model without revisiting if it works
> well or not, that might explain it a little bit. But also given the
> size of our tables we use the physical disks and filesytem advantages
> to improve speed and performance of the application, but not as often
> as I would like. We have a pretty big database.

I've found these days, you're usually better off just stripping all your
mirrors into one big raid10, and letting statistics load balance your
IO. I've got stripe sets of as many as 20 small-fast drives, totalling
several terabytes, using XFS (Linux), or ZFS (Solaris, BSD), or JFS2
(AIX), all of which seem to handle the large file system quite efficiently.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

As suggested I did a couple more experiments. This time I installed
Postgres 9.0 in it's defauls location. I then installed Postgres 9.3 in
/opt. Tested that both version booted up and ran independently of each
other.

First test, Postgres 9.0 just after an initdb, so it's all clean. It
completed successfully and created the analyze_new_cluster and
delete_old_cluster scripts. So this was successful.

[pgsql(at)postgres-93-upgrade /tmp]$ time /opt/bin/pg_upgrade -d
/usr/local/pgsql/data -D /usr/local/pgsql_93/data/ -b /usr/local/bin/ -B
/opt/bin/ -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
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
delete_old_cluster.sh

real 0m8.141s
user 0m0.143s
sys 0m0.817s

Second test. I cleaned up the data folders for both installs and did initdb
on both installs. This time I created one table space. It completed the
upgrade, however it only created the analyze_new cluster script. No
delete_old_cluster script. I created the symlink from the new install
pointing to the old data folder, which is to be expected. But it left the
old data there.

[pgsql(at)postgres-93-upgrade /tmp]$ time /opt/bin/pg_upgrade -d
/usr/local/pgsql/data -D /usr/local/pgsql_93/data/ -b /usr/local/bin/ -B
/opt/bin/ -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
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh

Could not create a script to delete the old cluster's data
files because user-defined tablespaces exist in the old cluster
directory. The old cluster's contents must be deleted manually.

real 0m9.865s
user 0m0.094s
sys 0m0.908s

[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql/data/drupal_dbspace/
total 16
drwx------ 4 pgsql pgsql 4 Dec 26 15:49 .
drwx------ 14 pgsql pgsql 20 Dec 26 15:49 ..
drwx------ 2 pgsql pgsql 2 Dec 26 15:48 PG_9.0_201008051
drwx------ 2 pgsql pgsql 2 Dec 26 15:49 PG_9.3_201306121
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/pg_tblspc/
total 10
drwx------ 2 pgsql pgsql 4 Dec 26 15:48 .
drwx------ 14 pgsql pgsql 20 Dec 26 15:49 ..
lrwx------ 1 pgsql pgsql 36 Dec 26 15:48 16384 ->
/usr/local/pgsql/data/drupal_dbspace
lrwx------ 1 pgsql pgsql 39 Dec 26 15:48 16385 ->
/usr/local/pgsql/data/drupal_indexspace
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_93/data/
PG_VERSION global/ pg_hba.conf pg_multixact/
pg_serial/ pg_stat/ pg_subtrans/ pg_twophase/
postgresql.conf
base/ pg_clog/ pg_ident.conf pg_notify/
pg_snapshots/ pg_stat_tmp/ pg_tblspc/ pg_xlog/
postmaster.opts
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_93/data/pg_tblspc/
total 10
drwx------ 2 pgsql pgsql 4 Dec 26 15:49 .
drwx------ 15 pgsql pgsql 20 Dec 26 15:49 ..
lrwx------ 1 pgsql pgsql 36 Dec 26 15:49 16420 ->
/usr/local/pgsql/data/drupal_dbspace
lrwx------ 1 pgsql pgsql 39 Dec 26 15:49 16421 ->
/usr/local/pgsql/data/drupal_indexspace

While the upgrade was successful, I find it unusable and leaving me with a
lot of manual labor ahead of me. Because it leaves the old folders there,
which have to be deleted manually. The same with all the other data files,
like postgresql.conf for example. Something that uninstalling 9.0 doesn't
remove. In other words now I am left with a dirty /usr/local/pgsql/data
folder and having to modify the postgres startup script. Or manually delete
all the files and folders I don't want and reinstall Posgres 9.3 in the
default location and create new symlinks.

I have asked a few people around here as to why we have so many table
spaces, non seem to know the real reason. Some say it's to increase speed
in table partitions. For example divided up by month and/or year. I don't
thinks that reasoning would apply anymore these days. I think it was made
the norm by our then Senior DBA 8 years ago and nobody questioned that
since, they just kept on adding. In the end I think tablespaces are a pain.

-Joseph

On 12/26/2013 08:31 AM, Joseph Kregloh wrote:
> As suggested I did a couple more experiments. This time I installed
> Postgres 9.0 in it's defauls location. I then installed Postgres 9.3 in
> /opt. Tested that both version booted up and ran independently of each
> other.
>
> First test, Postgres 9.0 just after an initdb, so it's all clean. It
> completed successfully and created the analyze_new_cluster and
> delete_old_cluster scripts. So this was successful.
>

...

> Second test. I cleaned up the data folders for both installs and did
> initdb on both installs. This time I created one table space. It
> completed the upgrade, however it only created the analyze_new cluster
> script. No delete_old_cluster script. I created the symlink from the new
> install pointing to the old data folder, which is to be expected. But it
> left the old data there.
>

>
> Could not create a script to delete the old cluster's data
> files because user-defined tablespaces exist in the old cluster
> directory. The old cluster's contents must be deleted manually.
>

Here is the message on --hackers that explains the above:

http://www.postgresql.org/message-id/20130214052952.GA10606@momjian.us

>
> While the upgrade was successful, I find it unusable and leaving me with
> a lot of manual labor ahead of me. Because it leaves the old folders
> there, which have to be deleted manually. The same with all the other
> data files, like postgresql.conf for example. Something that
> uninstalling 9.0 doesn't remove. In other words now I am left with a
> dirty /usr/local/pgsql/data folder and having to modify the postgres
> startup script. Or manually delete all the files and folders I don't
> want and reinstall Posgres 9.3 in the default location and create new
> symlinks.

Well one of the options in the upgrade process is to move the old
installation out of the way into another directory and then install the
new version into the default location. That would eliminate the above
issues.

>
> -Joseph

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

>
> Here is the message on --hackers that explains the above:
>
> http://www.postgresql.org/message-id/20130214052952.GA10606@momjian.us
>
>
Let me read into this.

>
>
>> While the upgrade was successful, I find it unusable and leaving me with
>> a lot of manual labor ahead of me. Because it leaves the old folders
>> there, which have to be deleted manually. The same with all the other
>> data files, like postgresql.conf for example. Something that
>> uninstalling 9.0 doesn't remove. In other words now I am left with a
>> dirty /usr/local/pgsql/data folder and having to modify the postgres
>> startup script. Or manually delete all the files and folders I don't
>> want and reinstall Posgres 9.3 in the default location and create new
>> symlinks.
>>
>
> Well one of the options in the upgrade process is to move the old
> installation out of the way into another directory and then install the new
> version into the default location. That would eliminate the above issues.
>

No it does not because pg_upgrade doesn't seem to be able to handle
tablespaces, which is the problem I have been having all along and I keep
on proving it. Below is the error when moving the 9.0 directory with a
tablespace:

[pgsql(at)postgres-93-upgrade /tmp]$ time /opt/bin/pg_upgrade -d
/usr/local/pgsql_90/data -D /usr/local/pgsql/data/ -b /usr/local/bin/ -B
/opt/bin/ -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
.../pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790
error while copying relation "pg_catalog.pg_largeobject"
("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790" to
"/usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16421/12301"): No
such file or directory
Failure, exiting

real 0m25.486s
user 0m0.978s
sys 0m2.872s

On 12/26/2013 01:06 PM, Joseph Kregloh wrote:
> Here is the message on --hackers that explains the above:
>
> http://www.postgresql.org/__message-id/20130214052952(dot)__GA10606(at)momjian(dot)us
> <http://www.postgresql.org/message-id/20130214052952.GA10606@momjian.us>
>
>
> Let me read into this.

>
>
> No it does not because pg_upgrade doesn't seem to be able to handle
> tablespaces, which is the problem I have been having all along and I
> keep on proving it. Below is the error when moving the 9.0 directory
> with a tablespace:

So how are you moving the 9.0 directory?
What does a listing for that directory look like after the move?
What does a listing for the 9.3 directory look like?

>
> [pgsql(at)postgres-93-upgrade /tmp]$ time /opt/bin/pg_upgrade -d
> /usr/local/pgsql_90/data -D /usr/local/pgsql/data/ -b /usr/local/bin/ -B
> /opt/bin/ -p 5452 -P 5451

> ok
> Removing support functions from new cluster ok
> Copying user relation files
> .../pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790
> error while copying relation "pg_catalog.pg_largeobject"
> ("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790" to
> "/usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16421/12301"): No
> such file or directory

So what do the listings for the old and new data directories look like
after the upgrade?

> Failure, exiting

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

>
>
>> So how are you moving the 9.0 directory?
>

Just using a mv command like below:
mv /usr/local/pgsql/data /usr/local/pgsql_90/data

Then I recreated the symlinks in /pg_tbspc to point to the new directory
path.

What does a listing for that directory look like after the move?
>

[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/
total 97
drwx------ 13 pgsql pgsql 19 Dec 26 21:03 .
drwx------ 3 pgsql pgsql 3 Dec 26 20:43 ..
-rwx------ 1 pgsql pgsql 4 Dec 26 19:30 PG_VERSION
drwx------ 5 pgsql pgsql 5 Dec 26 19:30 base
drwx------ 3 pgsql pgsql 3 Dec 26 19:52 drupal_dbspace
drwx------ 2 pgsql pgsql 43 Dec 26 21:03 global
drwx------ 2 pgsql pgsql 3 Dec 26 19:30 pg_clog
-rwx------ 1 pgsql pgsql 3939 Dec 26 19:30 pg_hba.conf
-rwx------ 1 pgsql pgsql 1636 Dec 26 19:30 pg_ident.conf
drwx------ 4 pgsql pgsql 4 Dec 26 19:30 pg_multixact
drwx------ 2 pgsql pgsql 3 Dec 26 21:02 pg_notify
drwx------ 2 pgsql pgsql 2 Dec 26 21:03 pg_stat_tmp
drwx------ 2 pgsql pgsql 3 Dec 26 19:30 pg_subtrans
drwx------ 2 pgsql pgsql 3 Dec 26 21:00 pg_tblspc
drwx------ 2 pgsql pgsql 2 Dec 26 19:30 pg_twophase
drwx------ 3 pgsql pgsql 6 Dec 26 20:53 pg_xlog
-rwx------ 1 pgsql pgsql 18079 Dec 26 19:43 postgresql.conf
-rwx------ 1 pgsql pgsql 59 Dec 26 19:44 postmaster.log
-rwx------ 1 pgsql pgsql 195 Dec 26 21:02 postmaster.opts
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql_90/data/drupal_dbspace/
total 12
drwx------ 3 pgsql pgsql 3 Dec 26 19:52 .
drwx------ 13 pgsql pgsql 19 Dec 26 21:03 ..
drwx------ 3 pgsql pgsql 3 Dec 26 19:52 PG_9.0_201008051
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql_90/data/pg_tblspc/
total 9
drwx------ 2 pgsql pgsql 3 Dec 26 21:00 .
drwx------ 13 pgsql pgsql 19 Dec 26 21:03 ..
lrwxr-xr-x 1 pgsql pgsql 39 Dec 26 21:00 24658 ->
/usr/local/pgsql_90/data/drupal_dbspace

> What does a listing for the 9.3 directory look like?
>

This is right after the initdb, I also created the /drupal_dbspace

[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/
total 104
drwx------ 16 pgsql pgsql 20 Dec 27 14:48 .
drwxr-xr-x 3 pgsql pgsql 5 Dec 26 15:17 ..
-rw------- 1 pgsql pgsql 4 Dec 27 14:38 PG_VERSION
drwx------ 5 pgsql pgsql 5 Dec 27 14:39 base
drwxr-xr-x 2 pgsql pgsql 2 Dec 27 14:48 drupal_dbspace
drwx------ 2 pgsql pgsql 42 Dec 27 14:39 global
drwx------ 2 pgsql pgsql 3 Dec 27 14:38 pg_clog
-rw------- 1 pgsql pgsql 4467 Dec 27 14:38 pg_hba.conf
-rw------- 1 pgsql pgsql 1636 Dec 27 14:38 pg_ident.conf
drwx------ 4 pgsql pgsql 4 Dec 27 14:38 pg_multixact
drwx------ 2 pgsql pgsql 3 Dec 27 14:39 pg_notify
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 pg_serial
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 pg_snapshots
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 pg_stat
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 pg_stat_tmp
drwx------ 2 pgsql pgsql 3 Dec 27 14:38 pg_subtrans
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 pg_tblspc
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 pg_twophase
drwx------ 3 pgsql pgsql 4 Dec 27 14:38 pg_xlog
-rw------- 1 pgsql pgsql 20410 Dec 27 14:38 postgresql.conf
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql/data/drupal_dbspace/
total 8
drwxr-xr-x 2 pgsql pgsql 2 Dec 27 14:48 .
drwx------ 16 pgsql pgsql 20 Dec 27 14:48 ..
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/pg_tblspc/

total 8
drwx------ 2 pgsql pgsql 2 Dec 27 14:38 .
drwx------ 16 pgsql pgsql 20 Dec 27 14:48 ..

>>
> So what do the listings for the old and new data directories look like
> after the upgrade?
>
>
Here is the moved 9.0 directory, it's a listing of data, drupal_dbspace,
and pg_tblsp

[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_90/data/
total 97
drwx------ 13 pgsql pgsql 19 Dec 26 21:03 .
drwx------ 3 pgsql pgsql 3 Dec 26 20:43 ..
-rwx------ 1 pgsql pgsql 4 Dec 26 19:30 PG_VERSION
drwx------ 5 pgsql pgsql 5 Dec 26 19:30 base
drwx------ 3 pgsql pgsql 3 Dec 26 19:52 drupal_dbspace
drwx------ 2 pgsql pgsql 43 Dec 26 21:03 global
drwx------ 2 pgsql pgsql 3 Dec 26 19:30 pg_clog
-rwx------ 1 pgsql pgsql 3939 Dec 26 19:30 pg_hba.conf
-rwx------ 1 pgsql pgsql 1636 Dec 26 19:30 pg_ident.conf
drwx------ 4 pgsql pgsql 4 Dec 26 19:30 pg_multixact
drwx------ 2 pgsql pgsql 3 Dec 26 21:02 pg_notify
drwx------ 2 pgsql pgsql 2 Dec 26 21:03 pg_stat_tmp
drwx------ 2 pgsql pgsql 3 Dec 26 19:30 pg_subtrans
drwx------ 2 pgsql pgsql 3 Dec 26 21:00 pg_tblspc
drwx------ 2 pgsql pgsql 2 Dec 26 19:30 pg_twophase
drwx------ 3 pgsql pgsql 6 Dec 26 20:53 pg_xlog
-rwx------ 1 pgsql pgsql 18079 Dec 26 19:43 postgresql.conf
-rwx------ 1 pgsql pgsql 59 Dec 26 19:44 postmaster.log
-rwx------ 1 pgsql pgsql 195 Dec 26 21:02 postmaster.opts
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql_90/data/drupal_dbspace/
total 12
drwx------ 3 pgsql pgsql 3 Dec 26 19:52 .
drwx------ 13 pgsql pgsql 19 Dec 26 21:03 ..
drwx------ 3 pgsql pgsql 3 Dec 26 19:52 PG_9.0_201008051
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql_90/data/pg_tblspc/
total 9
drwx------ 2 pgsql pgsql 3 Dec 26 21:00 .
drwx------ 13 pgsql pgsql 19 Dec 26 21:03 ..
lrwxr-xr-x 1 pgsql pgsql 39 Dec 26 21:00 24658 ->
/usr/local/pgsql_90/data/drupal_dbspace

Here is a listing of the 9.3 directory in the default location, same
listings as above:

[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/
total 107
drwx------ 16 pgsql pgsql 21 Dec 26 21:03 .
drwxr-xr-x 3 pgsql pgsql 5 Dec 26 15:17 ..
-rw------- 1 pgsql pgsql 4 Dec 26 21:01 PG_VERSION
drwx------ 5 pgsql pgsql 5 Dec 26 21:01 base
drwx------ 3 pgsql pgsql 3 Dec 26 21:03 drupal_dbspace
drwx------ 2 pgsql pgsql 44 Dec 26 21:03 global
drwx------ 2 pgsql pgsql 3 Dec 26 21:03 pg_clog
-rw------- 1 pgsql pgsql 4467 Dec 26 21:01 pg_hba.conf
-rw------- 1 pgsql pgsql 1636 Dec 26 21:01 pg_ident.conf
drwx------ 4 pgsql pgsql 4 Dec 26 21:01 pg_multixact
drwx------ 2 pgsql pgsql 3 Dec 26 21:03 pg_notify
drwx------ 2 pgsql pgsql 2 Dec 26 21:01 pg_serial
drwx------ 2 pgsql pgsql 2 Dec 26 21:01 pg_snapshots
drwx------ 2 pgsql pgsql 8 Dec 26 21:03 pg_stat
drwx------ 2 pgsql pgsql 2 Dec 26 21:03 pg_stat_tmp
drwx------ 2 pgsql pgsql 3 Dec 26 21:01 pg_subtrans
drwx------ 2 pgsql pgsql 3 Dec 26 21:03 pg_tblspc
drwx------ 2 pgsql pgsql 2 Dec 26 21:01 pg_twophase
drwx------ 3 pgsql pgsql 5 Dec 26 21:03 pg_xlog
-rw------- 1 pgsql pgsql 20411 Dec 26 21:01 postgresql.conf
-rw------- 1 pgsql pgsql 236 Dec 26 21:03 postmaster.opts
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la
/usr/local/pgsql/data/drupal_dbspace/
total 12
drwx------ 3 pgsql pgsql 3 Dec 26 21:03 .
drwx------ 16 pgsql pgsql 21 Dec 26 21:03 ..
drwx------ 3 pgsql pgsql 3 Dec 26 21:03 PG_9.3_201306121
[pgsql(at)postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/pg_tblspc/

total 9
drwx------ 2 pgsql pgsql 3 Dec 26 21:03 .
drwx------ 16 pgsql pgsql 21 Dec 26 21:03 ..
lrwx------ 1 pgsql pgsql 36 Dec 26 21:03 16420 ->
/usr/local/pgsql/data/drupal_dbspace

On 12/27/2013 06:50 AM, Joseph Kregloh wrote:
>
> So how are you moving the 9.0 directory?
>
>
> Just using a mv command like below:
> mv /usr/local/pgsql/data /usr/local/pgsql_90/data
>
> Then I recreated the symlinks in /pg_tbspc to point to the new directory
> path.

Ah, now I see the problem, I think. As was noted upstream having a user
tablespace in the PGDATA would seem to be the issue.

If you do SELECT * from pg_tablespace in the 9.0 install before moving
and after you will see that spclocation does not change and points to
the original PGDATA/drupal_dbspace. Creating the Postgres 9.3 instance
in the old location then basically slides the new under the old. This is
where you get this error:

Copying user relation files
.../pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790
error while copying relation "pg_catalog.pg_largeobject"
("/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051/24659/11790" to
"/usr/local/pgsql/data/drupal_dbspace/PG_9.3_201306121/16421/12301"): No
such file or directory
Failure, exiting

Postgres is going to /usr/local/pgsql/data/drupal_dbspace/ to look for
the 9.0 files instead of /usr/local/pgsql_90/data/drupal_dbspace/ and is
trying to copy them as 9.3 versions into the new default location which
has the same path. Since the new
/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051 is empty it is
failing.

Not sure of the best solution, others may have better ideas.

On thing that came to mind is to give pg_upgrade what it wants, the 9.0
tablespace in the default
location(/usr/local/pgsql/data/drupal_dbspace/). In other words make a
symlink:

/usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051

to

/usr/local/pgsql_90/data/drupal_dbspace/PG_9.0_201008051

FYI, some testing showed that playing around with spclocation in
pg_tablespace is not recommended.

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

>
>
> Postgres is going to /usr/local/pgsql/data/drupal_dbspace/ to look for
> the 9.0 files instead of /usr/local/pgsql_90/data/drupal_dbspace/ and is
> trying to copy them as 9.3 versions into the new default location which has
> the same path. Since the new /usr/local/pgsql/data/drupal_dbspace/PG_9.0_201008051
> is empty it is failing.
>

That is exactly what is going on. I think what I am going to end up doing
is:

- Leaving 9.0 in the default location, this way it will successfully
complete PG upgrade.
- Uninstall 9.0
- Manually move the user created tablespaces into the 9.3 data folder
- Reinstall 9.3 to go into the default location, right now its installed in
/opt using the PREFIX
- Move the 9.3 data folder into the default location.
- Cleanup the old 9.0 folders

Then in theory it should start right up.

I would assume that if the user created tablespaces were created outside of
the /data folder then this would not be an issue. But again, I am not the
DBA, I clean up after everybody else.

Thanks for all your help Adrian.

-Joseph

On 12/27/2013 01:00 PM, Joseph Kregloh wrote:
>
> Postgres is going to /usr/local/pgsql/data/drupal___dbspace/ to look
> for the 9.0 files instead of
> /usr/local/pgsql_90/data/__drupal_dbspace/ and is trying to copy
> them as 9.3 versions into the new default location which has the
> same path. Since the new
> /usr/local/pgsql/data/drupal___dbspace/PG_9.0_201008051 is empty it
> is failing.
>
>
> That is exactly what is going on. I think what I am going to end up
> doing is:

I am not sure that is going to work.

>
> - Leaving 9.0 in the default location, this way it will successfully
> complete PG upgrade.

So you will have 9.3 installed in /opt correct?

> - Uninstall 9.0
> - Manually move the user created tablespaces into the 9.3 data folder

The 9.0 tablespaces correct? Why, this after the upgrade they are no
longer of use to the 9.3 installation and cannot be used by it?

> - Reinstall 9.3 to go into the default location, right now its installed
> in /opt using the PREFIX

Now 9.3 is in /usr/local/ correct?

> - Move the 9.3 data folder into the default location.

Same problem, different direction:) The 9.3 tablespaces in pg_tablespace
will be looking back at the old /opt location which does not exist

> - Cleanup the old 9.0 folders

>
> Then in theory it should start right up.
>
> I would assume that if the user created tablespaces were created outside
> of the /data folder then this would not be an issue. But again, I am not
> the DBA, I clean up after everybody else.

Well the idea behind user created tablespaces is to spread the data load
across filesystems/disks. So, yes it is generally best practice not to
put them in the default PGDATA directory.

>
> Thanks for all your help Adrian.
>
> -Joseph

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

>
> FYI, some testing showed that playing around with spclocation in
> pg_tablespace is not recommended.

Do you happen to have more information about this? Because it would
actually solve all my problems by moving the user created tablespaces out
of the /data directory. But I would like more information on the subject
before even thinking about it anymore. I did it a couple times for testing
purposes. I modified the spclocation in pg_tablespace and then move the
folder.

-Joseph

On 12/27/2013 01:56 PM, Joseph Kregloh wrote:
> FYI, some testing showed that playing around with spclocation in
> pg_tablespace is not recommended.
>
>
> Do you happen to have more information about this? Because it would
> actually solve all my problems by moving the user created tablespaces
> out of the /data directory. But I would like more information on the
> subject before even thinking about it anymore. I did it a couple times
> for testing purposes. I modified the spclocation in pg_tablespace and
> then move the folder.

Well as a general idea manually altering system catalogs is a bad idea.
I just did some quick tests on something that was not as complex as your
setup and for me it did not go well. I could make changes, but when I
tried to use the tablespaces I got all sort of errors. Could be just me,
still this is a path into deep dark places, you are warned:)

>
> -Joseph

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

On 12/27/2013 01:00 PM, Joseph Kregloh wrote:
>
> Postgres is going to /usr/local/pgsql/data/drupal___dbspace/ to look
> for the 9.0 files instead of
> /usr/local/pgsql_90/data/__drupal_dbspace/ and is trying to copy
> them as 9.3 versions into the new default location which has the
> same path. Since the new
> /usr/local/pgsql/data/drupal___dbspace/PG_9.0_201008051 is empty it
> is failing.
>
>
> That is exactly what is going on. I think what I am going to end up
> doing is:
>
> - Leaving 9.0 in the default location, this way it will successfully
> complete PG upgrade.
> - Uninstall 9.0
> - Manually move the user created tablespaces into the 9.3 data folder
> - Reinstall 9.3 to go into the default location, right now its installed
> in /opt using the PREFIX
> - Move the 9.3 data folder into the default location.

Got to thinking about this. What you could try is:

Move the 9.3 data directory, with the exception of the tablespace, into
the default location. Leave the upgraded 9.3 tablespace itself in /opt.

> - Cleanup the old 9.0 folders
>
> Then in theory it should start right up.
>
> I would assume that if the user created tablespaces were created outside
> of the /data folder then this would not be an issue. But again, I am not
> the DBA, I clean up after everybody else.
>
> Thanks for all your help Adrian.
>
> -Joseph

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

On Friday, December 27, 2013, Joseph Kregloh wrote:

> FYI, some testing showed that playing around with spclocation in
>> pg_tablespace is not recommended.
>
>
> Do you happen to have more information about this? Because it would
> actually solve all my problems by moving the user created tablespaces out
> of the /data directory. But I would like more information on the subject
> before even thinking about it anymore. I did it a couple times for testing
> purposes. I modified the spclocation in pg_tablespace and then move the
> folder.
>

spclocation no longer exists in 9.3. If the database needs to know where
the location is, it inspects the symlink in pg_tblspc to figure that out.

Cheers,

Jeff

>

On 12/27/2013 02:52 PM, Jeff Janes wrote:
> On Friday, December 27, 2013, Joseph Kregloh wrote:
>
> FYI, some testing showed that playing around with spclocation in
> pg_tablespace is not recommended.
>
>
> Do you happen to have more information about this? Because it would
> actually solve all my problems by moving the user created
> tablespaces out of the /data directory. But I would like more
> information on the subject before even thinking about it anymore. I
> did it a couple times for testing purposes. I modified the
> spclocation in pg_tablespace and then move the folder.
>
>
> spclocation no longer exists in 9.3. If the database needs to know
> where the location is, it inspects the symlink in pg_tblspc to figure
> that out.

Well the issue seems to be with 9.0. I am not exactly sure where
pg_upgrade is pulling its information, but I am guessing from the error
message that on the 9.0 side of things it is using spclocation. In the
OPs situation that is no longer valid for 9.0 once its data directory is
moved. The special circumstance here being that the user tablespace is
in PGDATA. I would welcome enlightenment on this.

>
> Cheers,
>
> Jeff
>

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

I did a bit more experimenting today. First test:

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

It completes successfully, however I still have the user defined
tablespaces inside the 9.0 data folder. So I manually moved all tablespaces
into the new 9.3 data directory. Then I deleted the 9.0 data directory and
renamed the 9.3 directory to /usr/local/pgsql/data. Now the tablespaces are
in the correct location and using the 9.3 data folder. The server starts up
just fine. On pgAdmin if I view the tables the data shows up, but if I do a
SELECT I get:

ERROR: relation "sys_errors" does not exist
LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT 100;
^
********** Error **********

ERROR: relation "sys_errors" does not exist
SQL state: 42P01
Character: 15

The second test, using the exact same pg_upgrade line. But this time I
updated the location of the tablespaces to outside the /data directory. I
updated pg_tablespace and re-created all symlinks. Now the data directory
doesn't contain the tablespaces. Again pg_upgrade completes successfully
and again I get the same error about the relation.

Any thoughts?

On 12/31/2013 12:25 PM, Joseph Kregloh wrote:
> I did a bit more experimenting today. First test:
>
> /opt/bin/pg_upgrade -d /usr/local/pgsql/data -D
> /usr/local/pgsql_93/data/ -b /usr/local/bin/ -B /opt/bin/ -p 5452 -P 5451
>
> It completes successfully, however I still have the user defined
> tablespaces inside the 9.0 data folder. So I manually moved all
> tablespaces into the new 9.3 data directory. Then I deleted the 9.0 data
> directory and renamed the 9.3 directory to /usr/local/pgsql/data. Now
> the tablespaces are in the correct location and using the 9.3 data
> folder. The server starts up just fine. On pgAdmin if I view the tables
> the data shows up, but if I do a SELECT I get:
>
> ERROR: relation "sys_errors" does not exist
> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT 100;
> ^
> ********** Error **********
>
> ERROR: relation "sys_errors" does not exist
> SQL state: 42P01
> Character: 15

sys_errors is a table in the tablespace correct?

>
> The second test, using the exact same pg_upgrade line. But this time I
> updated the location of the tablespaces to outside the /data directory.
> I updated pg_tablespace and re-created all symlinks. Now the data
> directory doesn't contain the tablespaces. Again pg_upgrade completes
> successfully and again I get the same error about the relation.
>
> Any thoughts?

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

>
>
>> ERROR: relation "sys_errors" does not exist
>> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT 100;
>> ^
>> ********** Error **********
>>
>> ERROR: relation "sys_errors" does not exist
>> SQL state: 42P01
>> Character: 15
>>
>
> sys_errors is a table in the tablespace correct?
>

Yes it is.

>
>> The second test, using the exact same pg_upgrade line. But this time I
>> updated the location of the tablespaces to outside the /data directory.
>> I updated pg_tablespace and re-created all symlinks. Now the data
>> directory doesn't contain the tablespaces. Again pg_upgrade completes
>> successfully and again I get the same error about the relation.
>>
>> Any thoughts?
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

On 12/31/2013 01:31 PM, Joseph Kregloh wrote:
>
> ERROR: relation "sys_errors" does not exist
> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT 100;
> ^
> ********** Error **********
>
> ERROR: relation "sys_errors" does not exist
> SQL state: 42P01
> Character: 15
>
>
> sys_errors is a table in the tablespace correct?
>
>
> Yes it is.

So you have not upgraded the tablespaces. What is important to remember
is Postgres uses numbers to keep track of relations. Part of the upgrade
process involves changing the numbers that point at relations. By
manually dropping a 9.0 tablespace into a 9.3 data directory you have
broken that system. You need to let pg_upgrade do the translation. See
my previous message below for a possible solution:

http://www.postgresql.org/message-id/52BDE16D.4090601@gmail.com

>
>
> The second test, using the exact same pg_upgrade line. But this
> time I
> updated the location of the tablespaces to outside the /data
> directory.
> I updated pg_tablespace and re-created all symlinks. Now the data
> directory doesn't contain the tablespaces. Again pg_upgrade
> completes
> successfully and again I get the same error about the relation.
>
> Any thoughts?
>
>
>
> --
> 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

>
> So you have not upgraded the tablespaces. What is important to remember is
>> Postgres uses numbers to keep track of relations. Part of the upgrade
>> process involves changing the numbers that point at relations. By manually
>> dropping a 9.0 tablespace into a 9.3 data directory you have broken that
>> system. You need to let pg_upgrade do the translation. See my previous
>> message below for a possible solution:
>>
>
>
Sorry, I may not have been to clear on my explenation, pg_upgrade creates
the 9.3 data directories inside the tablespace folders and those are the
ones I moved. So pg_upgrade finishes it's job.

On 12/31/2013 01:51 PM, Joseph Kregloh wrote:
> So you have not upgraded the tablespaces. What is important to
> remember is Postgres uses numbers to keep track of relations.
> Part of the upgrade process involves changing the numbers that
> point at relations. By manually dropping a 9.0 tablespace into a
> 9.3 data directory you have broken that system. You need to let
> pg_upgrade do the translation. See my previous message below
> for a possible solution:
>
>
>
> Sorry, I may not have been to clear on my explenation, pg_upgrade
> creates the 9.3 data directories inside the tablespace folders and those
> are the ones I moved. So pg_upgrade finishes it's job.

Can we see a listing of the tablespace and pg_tblspc?

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

On 12/31/2013 01:31 PM, Joseph Kregloh wrote:
>
> ERROR: relation "sys_errors" does not exist
> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT 100;
> ^
> ********** Error **********
>
> ERROR: relation "sys_errors" does not exist
> SQL state: 42P01
> Character: 15
>
>
> sys_errors is a table in the tablespace correct?
>
>
> Yes it is.

Completely different thought, is sys_errors in a schema other than PUBLIC?

If so, what is your search_path setting for the new server?

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

On Tue, Dec 31, 2013 at 5:08 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 12/31/2013 01:31 PM, Joseph Kregloh wrote:
>
>>
>> ERROR: relation "sys_errors" does not exist
>> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts DESC LIMIT
>> 100;
>> ^
>> ********** Error **********
>>
>> ERROR: relation "sys_errors" does not exist
>> SQL state: 42P01
>> Character: 15
>>
>>
>> sys_errors is a table in the tablespace correct?
>>
>>
>> Yes it is.
>>
>
> Completely different thought, is sys_errors in a schema other than PUBLIC?
>
> If so, what is your search_path setting for the new server?
>
>
I set the search_path to the same value that the 9.0 instance had and that
seemed to do the trick. I will know more on Thursday when I get some time
to play with it.

Thanks,
Happy New Year.

On 12/31/2013 04:03 PM, Joseph Kregloh wrote:
>
>
>
> On Tue, Dec 31, 2013 at 5:08 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com
> <mailto:adrian(dot)klaver(at)gmail(dot)com>> wrote:
>
> On 12/31/2013 01:31 PM, Joseph Kregloh wrote:
>
>
> ERROR: relation "sys_errors" does not exist
> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts
> DESC LIMIT 100;
> ^
> ********** Error **********
>
> ERROR: relation "sys_errors" does not exist
> SQL state: 42P01
> Character: 15
>
>
> sys_errors is a table in the tablespace correct?
>
>
> Yes it is.
>
>
> Completely different thought, is sys_errors in a schema other than
> PUBLIC?
>
> If so, what is your search_path setting for the new server?
>
>
> I set the search_path to the same value that the 9.0 instance had and
> that seemed to do the trick. I will know more on Thursday when I get
> some time to play with it.

Seems I got tunnel vision on the tablespace issue and overlooked the
simpler explanation initially. Good luck.

>
> Thanks,
> Happy New Year.
>

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

Just as a followup to this. The process that I am using to do the upgrade
is as follows:

1. Install Postgres 9.3 in /opt dir.
2. In 9.0 instance update spclocation in pg_tablespace.
3. Update the symlinks in the pg_tblspace folder.
4. Move the tablespace folders to new location.
5. Run pg_upgrade.
6. Test upgrade results on 9.3 cluster.
7. Run the sh files created by pg_upgrade.
8. Uninstall Postgres 9.3 in /opt dir.
9. Install Postgres 9.3 in default location.
10. Enjoy Postgres 9.3.

I could actually move the 9.0 cluster after moving the table spaces and
install 9.3 in the default location as the documentation shows. But I
haven't experimented with that scenario yet.

-Joseph

On Tue, Dec 31, 2013 at 7:06 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 12/31/2013 04:03 PM, Joseph Kregloh wrote:
>
>>
>>
>>
>> On Tue, Dec 31, 2013 at 5:08 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com
>> <mailto:adrian(dot)klaver(at)gmail(dot)com>> wrote:
>>
>> On 12/31/2013 01:31 PM, Joseph Kregloh wrote:
>>
>>
>> ERROR: relation "sys_errors" does not exist
>> LINE 1: SELECT * FROM sys_errors ORDER BY created_ts
>> DESC LIMIT 100;
>> ^
>> ********** Error **********
>>
>> ERROR: relation "sys_errors" does not exist
>> SQL state: 42P01
>> Character: 15
>>
>>
>> sys_errors is a table in the tablespace correct?
>>
>>
>> Yes it is.
>>
>>
>> Completely different thought, is sys_errors in a schema other than
>> PUBLIC?
>>
>> If so, what is your search_path setting for the new server?
>>
>>
>> I set the search_path to the same value that the 9.0 instance had and
>> that seemed to do the trick. I will know more on Thursday when I get
>> some time to play with it.
>>
>
>
> Seems I got tunnel vision on the tablespace issue and overlooked the
> simpler explanation initially. Good luck.
>
>
>> Thanks,
>> Happy New Year.
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

On 01/10/2014 08:40 AM, Joseph Kregloh wrote:
> Just as a followup to this. The process that I am using to do the
> upgrade is as follows:
>
> 1. Install Postgres 9.3 in /opt dir.
> 2. In 9.0 instance update spclocation in pg_tablespace.
> 3. Update the symlinks in the pg_tblspace folder.
> 4. Move the tablespace folders to new location.
> 5. Run pg_upgrade.
> 6. Test upgrade results on 9.3 cluster.
> 7. Run the sh files created by pg_upgrade.
> 8. Uninstall Postgres 9.3 in /opt dir.
> 9. Install Postgres 9.3 in default location.
> 10. Enjoy Postgres 9.3.

For completeness, the new location you are moving the tablespaces to, is
that in or out of $PGDATA?

FYI, from comments over on --hackers, I believe Bruce Momjian may offer
some insight on what is going on.

>
> I could actually move the 9.0 cluster after moving the table spaces and
> install 9.3 in the default location as the documentation shows. But I
> haven't experimented with that scenario yet.
>
> -Joseph
>
>

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

On Fri, Dec 27, 2013 at 04:10:25PM -0800, Adrian Klaver wrote:
> On 12/27/2013 02:52 PM, Jeff Janes wrote:
> >On Friday, December 27, 2013, Joseph Kregloh wrote:
> >
> > FYI, some testing showed that playing around with spclocation in
> > pg_tablespace is not recommended.
> >
> >
> > Do you happen to have more information about this? Because it would
> > actually solve all my problems by moving the user created
> > tablespaces out of the /data directory. But I would like more
> > information on the subject before even thinking about it anymore. I
> > did it a couple times for testing purposes. I modified the
> > spclocation in pg_tablespace and then move the folder.
> >
> >
> >spclocation no longer exists in 9.3. If the database needs to know
> >where the location is, it inspects the symlink in pg_tblspc to figure
> >that out.
>
> Well the issue seems to be with 9.0. I am not exactly sure where
> pg_upgrade is pulling its information, but I am guessing from the
> error message that on the 9.0 side of things it is using
> spclocation. In the OPs situation that is no longer valid for 9.0
> once its data directory is moved. The special circumstance here
> being that the user tablespace is in PGDATA. I would welcome
> enlightenment on this.

The problem is that pre-9.2 recorded the tablespace location in
pg_tablespace and in the symlink. When the pg_upgrade instructions tell
you to rename the old database cluster, it doesn't remind pre-9.2 users
to update in-PGDATA tablespaces.

Only the symlink location is used in 9.2+, so it would work fine there.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 01/10/2014 06:59 PM, Bruce Momjian wrote:
> On Fri, Dec 27, 2013 at 04:10:25PM -0800, Adrian Klaver wrote:
>> On 12/27/2013 02:52 PM, Jeff Janes wrote:
>>> On Friday, December 27, 2013, Joseph Kregloh wrote:
>>>
>>> FYI, some testing showed that playing around with spclocation in
>>> pg_tablespace is not recommended.
>>>
>>>
>>> Do you happen to have more information about this? Because it would
>>> actually solve all my problems by moving the user created
>>> tablespaces out of the /data directory. But I would like more
>>> information on the subject before even thinking about it anymore. I
>>> did it a couple times for testing purposes. I modified the
>>> spclocation in pg_tablespace and then move the folder.
>>>
>>>
>>> spclocation no longer exists in 9.3. If the database needs to know
>>> where the location is, it inspects the symlink in pg_tblspc to figure
>>> that out.
>>
>> Well the issue seems to be with 9.0. I am not exactly sure where
>> pg_upgrade is pulling its information, but I am guessing from the
>> error message that on the 9.0 side of things it is using
>> spclocation. In the OPs situation that is no longer valid for 9.0
>> once its data directory is moved. The special circumstance here
>> being that the user tablespace is in PGDATA. I would welcome
>> enlightenment on this.
>
> The problem is that pre-9.2 recorded the tablespace location in
> pg_tablespace and in the symlink. When the pg_upgrade instructions tell
> you to rename the old database cluster, it doesn't remind pre-9.2 users
> to update in-PGDATA tablespaces.

Just so I understand, this is update spclocation in pg_upgrade in the
pre-9.2 database.

>
> Only the symlink location is used in 9.2+, so it would work fine there.
>

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

On Sat, Jan 11, 2014 at 06:43:16AM -0800, Adrian Klaver wrote:
> >>Well the issue seems to be with 9.0. I am not exactly sure where
> >>pg_upgrade is pulling its information, but I am guessing from the
> >>error message that on the 9.0 side of things it is using
> >>spclocation. In the OPs situation that is no longer valid for 9.0
> >>once its data directory is moved. The special circumstance here
> >>being that the user tablespace is in PGDATA. I would welcome
> >>enlightenment on this.
> >
> >The problem is that pre-9.2 recorded the tablespace location in
> >pg_tablespace and in the symlink. When the pg_upgrade instructions tell
> >you to rename the old database cluster, it doesn't remind pre-9.2 users
> >to update in-PGDATA tablespaces.
>
> Just so I understand, this is update spclocation in pg_upgrade in
> the pre-9.2 database.

Right. I know there were multiple issue with this upgrade, jails
probably being the biggest, but a new one I had never heard is that _if_
you are placing your tablespaces in the PGDATA directory, and you are
upgrading from pre-9.2, if you rename the old data directory, you also
need to start the old server and update pg_tablespace.spclocation.

No one has ever reported that failure, but it would certainly happen. I
wonder if pg_upgrade should be modified to check that
pg_tablespace.spclocation point to real directories for pre-9.2 servers.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 01/11/2014 08:18 AM, Bruce Momjian wrote:
> On Sat, Jan 11, 2014 at 06:43:16AM -0800, Adrian Klaver wrote:
>>>> Well the issue seems to be with 9.0. I am not exactly sure where
>>>> pg_upgrade is pulling its information, but I am guessing from the
>>>> error message that on the 9.0 side of things it is using
>>>> spclocation. In the OPs situation that is no longer valid for 9.0
>>>> once its data directory is moved. The special circumstance here
>>>> being that the user tablespace is in PGDATA. I would welcome
>>>> enlightenment on this.
>>>
>>> The problem is that pre-9.2 recorded the tablespace location in
>>> pg_tablespace and in the symlink. When the pg_upgrade instructions tell
>>> you to rename the old database cluster, it doesn't remind pre-9.2 users
>>> to update in-PGDATA tablespaces.
>>
>> Just so I understand, this is update spclocation in pg_upgrade in
>> the pre-9.2 database.
>
> Right. I know there were multiple issue with this upgrade, jails
> probably being the biggest, but a new one I had never heard is that _if_
> you are placing your tablespaces in the PGDATA directory, and you are
> upgrading from pre-9.2, if you rename the old data directory, you also
> need to start the old server and update pg_tablespace.spclocation.
>
> No one has ever reported that failure, but it would certainly happen. I
> wonder if pg_upgrade should be modified to check that
> pg_tablespace.spclocation point to real directories for pre-9.2 servers.
>

I thought I was understanding, now I am not. This starts with your post
of last night. So in pre-9.2 cases the tablespace location is recorded
in two places pg_tablespace and the symlinks in pg_tblspc/. When you
upgrade pg_upgrade only looks at the pg_tablspace entry for pre-9.2
instances or does it look at the pg_tblspc symlinks also? If it looks at
the symlinks would they need to be changed also?

As to your check for directories that sounds like a good idea, though I
have one question. What constitutes a 'real' directory? I can see a
situation where someone moves an existing instance from $PGDATA to
$PGDATA.old and the installs a new version in $PGDATA. Then before they
do the upgrade they create a new tablespace directory in $PGDATA. If
they did not upgrade the spclocation in the old instance and ran the
check it would find a directory but there would be nothing in it. So
would the check look for actual tablespace data?

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

On Sat, Jan 11, 2014 at 10:40:20AM -0800, Adrian Klaver wrote:
> >Right. I know there were multiple issue with this upgrade, jails
> >probably being the biggest, but a new one I had never heard is that _if_
> >you are placing your tablespaces in the PGDATA directory, and you are
> >upgrading from pre-9.2, if you rename the old data directory, you also
> >need to start the old server and update pg_tablespace.spclocation.
> >
> >No one has ever reported that failure, but it would certainly happen. I
> >wonder if pg_upgrade should be modified to check that
> >pg_tablespace.spclocation point to real directories for pre-9.2 servers.
> >
>
> I thought I was understanding, now I am not. This starts with your
> post of last night. So in pre-9.2 cases the tablespace location is
> recorded in two places pg_tablespace and the symlinks in pg_tblspc/.

[ I am moving this discussion to hackers to get developer feedback. ]

Right.

> When you upgrade pg_upgrade only looks at the pg_tablespace entry
> for pre-9.2 instances or does it look at the pg_tblspc symlinks
> also? If it looks at the symlinks would they need to be changed
> also?

pg_upgrade looks in the pg_tablespace in pre-9.2, and uses a function in
9.2+. The query is:

snprintf(query, sizeof(query),
"SELECT %s "
"FROM pg_catalog.pg_tablespace "
"WHERE spcname != 'pg_default' AND "
" spcname != 'pg_global'",
/* 9.2 removed the spclocation column */
(GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
--> "spclocation" : "pg_catalog.pg_tablespace_location(oid) AS spclocation");

> As to your check for directories that sounds like a good idea,
> though I have one question. What constitutes a 'real' directory? I
> can see a situation where someone moves an existing instance from
> $PGDATA to $PGDATA.old and the installs a new version in $PGDATA.
> Then before they do the upgrade they create a new tablespace
> directory in $PGDATA. If they did not upgrade the spclocation in the
> old instance and ran the check it would find a directory but there
> would be nothing in it. So would the check look for actual
> tablespace data?

I would probably just look for the directory. People are not supposed
to be modifying their clusters during the upgrade, though, as stated, if
they move the old cluster, the are required to update pg_tablespace if
they have tablespaces in PGDATA, which is unfortunate.

I think the big question on adding a check is, how many users of 9.4 are
going to be upgrading from pre-9.2 and have tablespaces in PGDATA, and
will be renaming their old PGDATA directory during the upgrade? We
could add the test to 9.3 too, of course.

Having pg_tablespace and the symlinks get out of sync was the reason
Magnus removed that duplication in 9.2, but I was unaware of how
pg_upgrade really magnifies the problem for tablespaces in PGDATA by
recommending a PGDATA rename.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 01/11/2014 10:55 AM, Bruce Momjian wrote:
> On Sat, Jan 11, 2014 at 10:40:20AM -0800, Adrian Klaver wrote:
>>> Right. I know there were multiple issue with this upgrade, jails
>>> probably being the biggest, but a new one I had never heard is that _if_
>>> you are placing your tablespaces in the PGDATA directory, and you are
>>> upgrading from pre-9.2, if you rename the old data directory, you also
>>> need to start the old server and update pg_tablespace.spclocation.
>>>
>>> No one has ever reported that failure, but it would certainly happen. I
>>> wonder if pg_upgrade should be modified to check that
>>> pg_tablespace.spclocation point to real directories for pre-9.2 servers.
>>>
>>
>> I thought I was understanding, now I am not. This starts with your
>> post of last night. So in pre-9.2 cases the tablespace location is
>> recorded in two places pg_tablespace and the symlinks in pg_tblspc/.
>
> [ I am moving this discussion to hackers to get developer feedback. ]
>
> Right.
>
>> When you upgrade pg_upgrade only looks at the pg_tablespace entry
>> for pre-9.2 instances or does it look at the pg_tblspc symlinks
>> also? If it looks at the symlinks would they need to be changed
>> also?
>
> pg_upgrade looks in the pg_tablespace in pre-9.2, and uses a function in
> 9.2+. The query is:
>
> snprintf(query, sizeof(query),
> "SELECT %s "
> "FROM pg_catalog.pg_tablespace "
> "WHERE spcname != 'pg_default' AND "
> " spcname != 'pg_global'",
> /* 9.2 removed the spclocation column */
> (GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
> --> "spclocation" : "pg_catalog.pg_tablespace_location(oid) AS spclocation");

I see, though I have another question. If pg_tablespace and the symlinks
can get out of sync, as you say below, why is pg_tablespace considered
the authority? Or to put it another way, why not just look at the
symlinks as in 9.2+?

>
>> As to your check for directories that sounds like a good idea,
>> though I have one question. What constitutes a 'real' directory? I
>> can see a situation where someone moves an existing instance from
>> $PGDATA to $PGDATA.old and the installs a new version in $PGDATA.
>> Then before they do the upgrade they create a new tablespace
>> directory in $PGDATA. If they did not upgrade the spclocation in the
>> old instance and ran the check it would find a directory but there
>> would be nothing in it. So would the check look for actual
>> tablespace data?
>
> I would probably just look for the directory. People are not supposed
> to be modifying their clusters during the upgrade, though, as stated, if
> they move the old cluster, the are required to update pg_tablespace if
> they have tablespaces in PGDATA, which is unfortunate.
>
> I think the big question on adding a check is, how many users of 9.4 are
> going to be upgrading from pre-9.2 and have tablespaces in PGDATA, and
> will be renaming their old PGDATA directory during the upgrade? We
> could add the test to 9.3 too, of course.

Well it is not generally accepted that users should even be creating
tablespaces in $PGDATA, but it is allowed by the program. My inclination
is to say that it is then the programs'(Postgres) responsibility to deal
with it. The alternative is to clarify the documentation and make it the
users responsibility. As to users upgrading from 9.1- to 9.2+, I see
still a lot of users posting to --general using 9.1- versions. At some
point they will likely migrate, so I can see a fix being worthwhile.

>
> Having pg_tablespace and the symlinks get out of sync was the reason
> Magnus removed that duplication in 9.2, but I was unaware of how
> pg_upgrade really magnifies the problem for tablespaces in PGDATA by
> recommending a PGDATA rename.
>

Well it was based on the valid assumption that people would create new
tablespaces outside $PGDATA because that is really is what is meant to
happen. You know us users we like to test assumptions:)

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

On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
> >pg_upgrade looks in the pg_tablespace in pre-9.2, and uses a function in
> >9.2+. The query is:
> >
> > snprintf(query, sizeof(query),
> > "SELECT %s "
> > "FROM pg_catalog.pg_tablespace "
> > "WHERE spcname != 'pg_default' AND "
> > " spcname != 'pg_global'",
> > /* 9.2 removed the spclocation column */
> > (GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
> >--> "spclocation" : "pg_catalog.pg_tablespace_location(oid) AS spclocation");
>
>
> I see, though I have another question. If pg_tablespace and the
> symlinks can get out of sync, as you say below, why is pg_tablespace
> considered the authority? Or to put it another way, why not just
> look at the symlinks as in 9.2+?

Uh, good question. I think I used the system tables because they were
easier to access. I can't remember if we used the symlinks for some
things and pg_tablespace for other things in pre-9.2.

> >I think the big question on adding a check is, how many users of 9.4 are
> >going to be upgrading from pre-9.2 and have tablespaces in PGDATA, and
> >will be renaming their old PGDATA directory during the upgrade? We
> >could add the test to 9.3 too, of course.
>
> Well it is not generally accepted that users should even be creating
> tablespaces in $PGDATA, but it is allowed by the program. My
> inclination is to say that it is then the programs'(Postgres)
> responsibility to deal with it. The alternative is to clarify the
> documentation and make it the users responsibility. As to users
> upgrading from 9.1- to 9.2+, I see still a lot of users posting to
> --general using 9.1- versions. At some point they will likely
> migrate, so I can see a fix being worthwhile.

True.

> >Having pg_tablespace and the symlinks get out of sync was the reason
> >Magnus removed that duplication in 9.2, but I was unaware of how
> >pg_upgrade really magnifies the problem for tablespaces in PGDATA by
> >recommending a PGDATA rename.
> >
>
> Well it was based on the valid assumption that people would create
> new tablespaces outside $PGDATA because that is really is what is
> meant to happen. You know us users we like to test assumptions:)

Also true. :-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Sun, Jan 12, 2014 at 5:26 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
> > >pg_upgrade looks in the pg_tablespace in pre-9.2, and uses a function in
> > >9.2+. The query is:
> > >
> > > snprintf(query, sizeof(query),
> > > "SELECT %s "
> > > "FROM pg_catalog.pg_tablespace "
> > > "WHERE spcname != 'pg_default' AND "
> > > " spcname != 'pg_global'",
> > > /* 9.2 removed the spclocation column */
> > > (GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
> > >--> "spclocation" : "pg_catalog.pg_tablespace_location(oid) AS
> spclocation");
> >
> >
> > I see, though I have another question. If pg_tablespace and the
> > symlinks can get out of sync, as you say below, why is pg_tablespace
> > considered the authority? Or to put it another way, why not just
> > look at the symlinks as in 9.2+?
>
> Uh, good question. I think I used the system tables because they were
> easier to access. I can't remember if we used the symlinks for some
> things and pg_tablespace for other things in pre-9.2.
>

If you mean PostgreSQL internally then no, we didn't use pg_tablespace for
anything ever. We only used the symlinks. Which is why it was so easy to
remove.

If you were using it for something inside pg_upgrade I don't know, but the
backend didn't.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
>> I see, though I have another question. If pg_tablespace and the
>> symlinks can get out of sync, as you say below, why is pg_tablespace
>> considered the authority? Or to put it another way, why not just
>> look at the symlinks as in 9.2+?

> Uh, good question. I think I used the system tables because they were
> easier to access. I can't remember if we used the symlinks for some
> things and pg_tablespace for other things in pre-9.2.

Well, pre-9.2 pg_dumpall is going to make use of the pg_tablespace
entries, because it has no other choice. We could conceivably teach
pg_upgrade to look at the symlinks for itself, but we're not going
to do that in pg_dumpall. Which means that the intermediate dump
script would contain inconsistent location values anyway if the
catalog entries are wrong. So I don't see any value in changing the
quoted code in pg_upgrade.

It does however seem reasonable for pg_upgrade to note whether any
of the paths are prefixed by old PGDATA and warn about the risks
involved.

regards, tom lane

On Sun, Jan 12, 2014 at 12:48:40PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
> >> I see, though I have another question. If pg_tablespace and the
> >> symlinks can get out of sync, as you say below, why is pg_tablespace
> >> considered the authority? Or to put it another way, why not just
> >> look at the symlinks as in 9.2+?
>
> > Uh, good question. I think I used the system tables because they were
> > easier to access. I can't remember if we used the symlinks for some
> > things and pg_tablespace for other things in pre-9.2.
>
> Well, pre-9.2 pg_dumpall is going to make use of the pg_tablespace
> entries, because it has no other choice. We could conceivably teach
> pg_upgrade to look at the symlinks for itself, but we're not going
> to do that in pg_dumpall. Which means that the intermediate dump
> script would contain inconsistent location values anyway if the
> catalog entries are wrong. So I don't see any value in changing the
> quoted code in pg_upgrade.

OK, agreed.

> It does however seem reasonable for pg_upgrade to note whether any
> of the paths are prefixed by old PGDATA and warn about the risks
> involved.

Uh, the problem is that once you rename the old PGDATA, the
pg_tablespace contents no longer point to the current PGDATA. The
symlinks, if they used absolute paths, wouldn't point to the current
PGDATA either.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 01/12/2014 07:02 PM, Bruce Momjian wrote:
> On Sun, Jan 12, 2014 at 12:48:40PM -0500, Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote:
>>>> I see, though I have another question. If pg_tablespace and the
>>>> symlinks can get out of sync, as you say below, why is pg_tablespace
>>>> considered the authority? Or to put it another way, why not just
>>>> look at the symlinks as in 9.2+?
>>
>>> Uh, good question. I think I used the system tables because they were
>>> easier to access. I can't remember if we used the symlinks for some
>>> things and pg_tablespace for other things in pre-9.2.
>>
>> Well, pre-9.2 pg_dumpall is going to make use of the pg_tablespace
>> entries, because it has no other choice. We could conceivably teach
>> pg_upgrade to look at the symlinks for itself, but we're not going
>> to do that in pg_dumpall. Which means that the intermediate dump
>> script would contain inconsistent location values anyway if the
>> catalog entries are wrong. So I don't see any value in changing the
>> quoted code in pg_upgrade.
>
> OK, agreed.
>
>> It does however seem reasonable for pg_upgrade to note whether any
>> of the paths are prefixed by old PGDATA and warn about the risks
>> involved.
>
> Uh, the problem is that once you rename the old PGDATA, the
> pg_tablespace contents no longer point to the current PGDATA. The
> symlinks, if they used absolute paths, wouldn't point to the current
> PGDATA either.
>

Well the problem is that it actually points to a current PGDATA just the
wrong one. To use the source installation path and the suggested upgrade
method from pg_upgrade.

Start.

/usr/local/pgsql/data/tblspc_dir

mv above to

/usr/local/pgsql_old/

install new version of Postgres to

/usr/local/pgsql/data/

In the pgsql_old installation you have symlinks pointing back to the
current default location. As well pg_tablespace points back to
/usr/local/pgsql/data/ The issue is that there is not actually anything
there in the way of a tablespace. So when pg_upgrade runs it tries to
upgrade from /usr/local/pgsql/data/tblspc_dir to
/usr/local/pgsql/data/tblspc_dir where the first directory either does
not exist. or if the user went ahead and created the directory in the
new installation, is empty. What is really wanted is to upgrade from
/usr/local/pgsql_old/data/tblspc_dir to
/usr/local/pgsql/data/tblspc_dir. Right now the only way that happens is
with user intervention.

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

On Sun, Jan 12, 2014 at 07:58:52PM -0800, Adrian Klaver wrote:
> Well the problem is that it actually points to a current PGDATA just
> the wrong one. To use the source installation path and the suggested
> upgrade method from pg_upgrade.
>
> Start.
>
> /usr/local/pgsql/data/tblspc_dir
>
> mv above to
>
> /usr/local/pgsql_old/
>
> install new version of Postgres to
>
> /usr/local/pgsql/data/
>
>
> In the pgsql_old installation you have symlinks pointing back to the
> current default location. As well pg_tablespace points back to
> /usr/local/pgsql/data/ The issue is that there is not actually
> anything there in the way of a tablespace. So when pg_upgrade runs
> it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to
> /usr/local/pgsql/data/tblspc_dir where the first directory either
> does not exist. or if the user went ahead and created the directory
> in the new installation, is empty. What is really wanted is to
> upgrade from /usr/local/pgsql_old/data/tblspc_dir to
> /usr/local/pgsql/data/tblspc_dir. Right now the only way that
> happens is with user intervention.

Right, it points to _nothing_ in the _new_ cluster. Perhaps the
simplest approach would be to check all the pg_tablespace locations to
see if they point at real directories. If not, we would have to have
the user update pg_tablespace and the symlinks. :-( Actually, even in
9.2+, those symlinks are going to point at the same "nothing". That
would support checking the symlinks in all versions.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On 01/12/2014 08:04 PM, Bruce Momjian wrote:
> On Sun, Jan 12, 2014 at 07:58:52PM -0800, Adrian Klaver wrote:
>> Well the problem is that it actually points to a current PGDATA just
>> the wrong one. To use the source installation path and the suggested
>> upgrade method from pg_upgrade.
>>

>> In the pgsql_old installation you have symlinks pointing back to the
>> current default location. As well pg_tablespace points back to
>> /usr/local/pgsql/data/ The issue is that there is not actually
>> anything there in the way of a tablespace. So when pg_upgrade runs
>> it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to
>> /usr/local/pgsql/data/tblspc_dir where the first directory either
>> does not exist. or if the user went ahead and created the directory
>> in the new installation, is empty. What is really wanted is to
>> upgrade from /usr/local/pgsql_old/data/tblspc_dir to
>> /usr/local/pgsql/data/tblspc_dir. Right now the only way that
>> happens is with user intervention.
>
> Right, it points to _nothing_ in the _new_ cluster. Perhaps the
> simplest approach would be to check all the pg_tablespace locations to
> see if they point at real directories. If not, we would have to have
> the user update pg_tablespace and the symlinks. :-( Actually, even in
> 9.2+, those symlinks are going to point at the same "nothing". That
> would support checking the symlinks in all versions.
>

Agreed.

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

On Fri, Jan 10, 2014 at 11:53 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 01/10/2014 08:40 AM, Joseph Kregloh wrote:
>
>> Just as a followup to this. The process that I am using to do the
>> upgrade is as follows:
>>
>> 1. Install Postgres 9.3 in /opt dir.
>> 2. In 9.0 instance update spclocation in pg_tablespace.
>> 3. Update the symlinks in the pg_tblspace folder.
>> 4. Move the tablespace folders to new location.
>> 5. Run pg_upgrade.
>> 6. Test upgrade results on 9.3 cluster.
>> 7. Run the sh files created by pg_upgrade.
>> 8. Uninstall Postgres 9.3 in /opt dir.
>> 9. Install Postgres 9.3 in default location.
>> 10. Enjoy Postgres 9.3.
>>
>
> For completeness, the new location you are moving the tablespaces to, is
> that in or out of $PGDATA?
>
>
The new location is /usr/local/pgsql/tablespaces/

> FYI, from comments over on --hackers, I believe Bruce Momjian may offer
> some insight on what is going on.
>
>
>> I could actually move the 9.0 cluster after moving the table spaces and
>> install 9.3 in the default location as the documentation shows. But I
>> haven't experimented with that scenario yet.
>>
>> -Joseph
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

Right. I know there were multiple issue with this upgrade, jails
> probably being the biggest, but a new one I had never heard is that _if_
> you are placing your tablespaces in the PGDATA directory, and you are
> upgrading from pre-9.2, if you rename the old data directory, you also
> need to start the old server and update pg_tablespace.spclocation.
>
>
Just to have it on the record. I did the upgrade outside of the jail to
make sure. I also tested it within jails and it worked also.

On Mon, Jan 13, 2014 at 09:23:09AM -0500, Joseph Kregloh wrote:
>
>
>
> Right.  I know there were multiple issue with this upgrade, jails
> probably being the biggest, but a new one I had never heard is that _if_
> you are placing your tablespaces in the PGDATA directory, and you are
> upgrading from pre-9.2, if you rename the old data directory, you also
> need to start the old server and update pg_tablespace.spclocation.
>
>
>
> Just to have it on the record. I did the upgrade outside of the jail to make
> sure. I also tested it within jails and it worked also. 

OK, good to know. I thought it was the jails because I had never heard
of cross-jail upgrades, but the tablespace in PGDATA was the problem. I
will work on a way to detect this in the coming weeks. It would affect
all back branches, not just pre-9.2.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

That is excellent news. I still have 3 more environments to upgrade, our
stage, pre-prod, and production environments in the next month or so. I
would be willing to test any fix you may have.

-Joseph

On Tue, Jan 21, 2014 at 7:02 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Mon, Jan 13, 2014 at 09:23:09AM -0500, Joseph Kregloh wrote:
> >
> >
> >
> > Right. I know there were multiple issue with this upgrade, jails
> > probably being the biggest, but a new one I had never heard is that
> _if_
> > you are placing your tablespaces in the PGDATA directory, and you are
> > upgrading from pre-9.2, if you rename the old data directory, you
> also
> > need to start the old server and update pg_tablespace.spclocation.
> >
> >
> >
> > Just to have it on the record. I did the upgrade outside of the jail to
> make
> > sure. I also tested it within jails and it worked also.
>
> OK, good to know. I thought it was the jails because I had never heard
> of cross-jail upgrades, but the tablespace in PGDATA was the problem. I
> will work on a way to detect this in the coming weeks. It would affect
> all back branches, not just pre-9.2.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>

On Wed, Jan 22, 2014 at 04:33:47PM -0500, Joseph Kregloh wrote:
> That is excellent news. I still have 3 more environments to upgrade, our stage,
> pre-prod, and production environments in the next month or so. I would be
> willing to test any fix you may have.

Thanks. I will let you know when I have something to test.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Sun, Jan 12, 2014 at 11:04:41PM -0500, Bruce Momjian wrote:
> > In the pgsql_old installation you have symlinks pointing back to the
> > current default location. As well pg_tablespace points back to
> > /usr/local/pgsql/data/ The issue is that there is not actually
> > anything there in the way of a tablespace. So when pg_upgrade runs
> > it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to
> > /usr/local/pgsql/data/tblspc_dir where the first directory either
> > does not exist. or if the user went ahead and created the directory
> > in the new installation, is empty. What is really wanted is to
> > upgrade from /usr/local/pgsql_old/data/tblspc_dir to
> > /usr/local/pgsql/data/tblspc_dir. Right now the only way that
> > happens is with user intervention.
>
> Right, it points to _nothing_ in the _new_ cluster. Perhaps the
> simplest approach would be to check all the pg_tablespace locations to
> see if they point at real directories. If not, we would have to have
> the user update pg_tablespace and the symlinks. :-( Actually, even in
> 9.2+, those symlinks are going to point at the same "nothing". That
> would support checking the symlinks in all versions.

I have developed the attached patch which checks all tablespaces to make
sure the directories exist. I plan to backpatch this.

The reason we haven't seen this bug reported more frequently is that a
_database_ defined in a non-existent tablespace directory already throws
an backend error, so this check is only necessary where tables/indexes
(not databases) are defined in non-existant tablespace directories.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Wed, Apr 16, 2014 at 01:49:20PM -0400, Bruce Momjian wrote:
> On Sun, Jan 12, 2014 at 11:04:41PM -0500, Bruce Momjian wrote:
> > > In the pgsql_old installation you have symlinks pointing back to the
> > > current default location. As well pg_tablespace points back to
> > > /usr/local/pgsql/data/ The issue is that there is not actually
> > > anything there in the way of a tablespace. So when pg_upgrade runs
> > > it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to
> > > /usr/local/pgsql/data/tblspc_dir where the first directory either
> > > does not exist. or if the user went ahead and created the directory
> > > in the new installation, is empty. What is really wanted is to
> > > upgrade from /usr/local/pgsql_old/data/tblspc_dir to
> > > /usr/local/pgsql/data/tblspc_dir. Right now the only way that
> > > happens is with user intervention.
> >
> > Right, it points to _nothing_ in the _new_ cluster. Perhaps the
> > simplest approach would be to check all the pg_tablespace locations to
> > see if they point at real directories. If not, we would have to have
> > the user update pg_tablespace and the symlinks. :-( Actually, even in
> > 9.2+, those symlinks are going to point at the same "nothing". That
> > would support checking the symlinks in all versions.
>
> I have developed the attached patch which checks all tablespaces to make
> sure the directories exist. I plan to backpatch this.
>
> The reason we haven't seen this bug reported more frequently is that a
> _database_ defined in a non-existent tablespace directory already throws
> an backend error, so this check is only necessary where tables/indexes
> (not databases) are defined in non-existant tablespace directories.

Patch applied and backpatched to 9.3. I beefed up the C comment to
explain how this can happen:

Check that the tablespace path exists and is a directory.
Effectively, this is checking only for tables/indexes in
non-existent tablespace directories. Databases located
in non-existent tablespaces already throw a backend error.
Non-existent tablespace directories can occur when a data directory
that contains user tablespaces is moved as part of pg_upgrade
preparation and the symbolic links are not updated.

Thanks for the report and debugging.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Mon, Jan 13, 2014 at 09:17:41AM -0500, Joseph Kregloh wrote:
>
>
>
> On Fri, Jan 10, 2014 at 11:53 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
> wrote:
>
> On 01/10/2014 08:40 AM, Joseph Kregloh wrote:
>
> Just as a followup to this. The process that I am using to do the
> upgrade is as follows:
>
> 1. Install Postgres 9.3 in /opt dir.
> 2. In 9.0 instance update spclocation in pg_tablespace.
> 3. Update the symlinks in the pg_tblspace folder.
> 4. Move the tablespace folders to new location.
> 5. Run pg_upgrade.
> 6. Test upgrade results on 9.3 cluster.
> 7. Run the sh files created by pg_upgrade.
> 8. Uninstall Postgres 9.3 in /opt dir.
> 9. Install Postgres 9.3 in default location.
> 10. Enjoy Postgres 9.3.
>
>
> For completeness, the new location you are moving the tablespaces to, is
> that in or out of $PGDATA?

This will be fixed in the next 9.3 minor release by throwing ane error
for non-existent tablespace directores.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

> This will be fixed in the next 9.3 minor release by throwing ane error
> for non-existent tablespace directores.
>
>
Awesome! I have already upgraded my dev, stage, preprod, and production
environments to 9.3. However I do have some snapshots that I can test with.

> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>

On Thu, Apr 17, 2014 at 02:45:59PM -0400, Joseph Kregloh wrote:
>
> This will be fixed in the next 9.3 minor release by throwing ane error
> for non-existent tablespace directories.
>
>
>
> Awesome! I have already upgraded my dev, stage, preprod, and production
> environments to 9.3. However I do have some snapshots that I can test with.

Great. It was tricky to figure out what was happening but once we did,
the solution was obvious.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +