pg_upgrade & tablespaces

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com>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

Here is the output of my last test run:

[pgsql@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@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@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@afilias.info>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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

-Joseph

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

On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.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.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[pgsql@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.ru@gmail.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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Within the jail it would be:
[pgsql@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@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.klaver@gmail.com>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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.us> wrote:

On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
<jkregloh@sproutloud.com> wrote:

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

On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.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@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.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@sproutloud.com> wrote:

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

On Thu, Dec 19, 2013 at 12:27 PM, Joseph Kregloh
<jkregloh@sproutloud.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@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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 19, 2013 at 1:18 PM, Bruce Momjian <bruce@momjian.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@sproutloud.com> wrote:

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

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

[pgsql@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.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Thu, Dec 19, 2013 at 4:16 PM, Adrian Klaver <adrian.klaver@gmail.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.

On Thu, Dec 19, 2013 at 4:18 PM, Bruce Momjian <bruce@momjian.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@sproutloud.com> wrote:

On Thu, Dec 19, 2013 at 3:46 PM, Sergey Konoplev <gray.ru@gmail.com>

wrote:

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

On 12/19/2013 01:50 PM, Joseph Kregloh wrote:

On Thu, Dec 19, 2013 at 4:14 PM, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 19, 2013 at 6:19 PM, Adrian Klaver <adrian.klaver@gmail.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@hogranch.com
<mailto:pierce@hogranch.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

On 12/20/2013 06:54 AM, Joseph Kregloh wrote:

On Thu, Dec 19, 2013 at 6:19 PM, Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.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@hogranch.com <mailto:pierce@hogranch.com>
<mailto:pierce@hogranch.com <mailto:pierce@hogranch.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&gt;

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.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On 12/20/2013 06:54 AM, Joseph Kregloh wrote:

On Thu, Dec 19, 2013 at 6:19 PM, Adrian Klaver <adrian.klaver@gmail.com
<mailto:adrian.klaver@gmail.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@hogranch.com <mailto:pierce@hogranch.com>
<mailto:pierce@hogranch.com <mailto:pierce@hogranch.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&gt;

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.

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.klaver@gmail.com <mailto:adrian.klaver@gmail.com>
<mailto:adrian.klaver@gmail.__com <mailto:adrian.klaver@gmail.com>>

--
Adrian Klaver
adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh <jkregloh@sproutloud.com>wrote:

[pgsql@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@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@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@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@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@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@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@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@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@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@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@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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[root@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@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

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

[root@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@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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@hogranch.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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

/messages/by-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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

/messages/by-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@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.__GA10606@momjian.us
</messages/by-id/20130214052952.GA10606@momjian.us&gt;

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@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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@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@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@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@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@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@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@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@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@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@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@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@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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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:

/messages/by-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.klaver@gmail.com <mailto:adrian.klaver@gmail.com>

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 31, 2013 at 5:08 PM, Adrian Klaver <adrian.klaver@gmail.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.klaver@gmail.com
<mailto:adrian.klaver@gmail.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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.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?

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.klaver@gmail.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jan 12, 2014 at 5:26 AM, Bruce Momjian <bruce@momjian.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@momjian.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jan 12, 2014 at 12:48:40PM -0500, Tom Lane wrote:

Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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@momjian.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.klaver@gmail.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.klaver@gmail.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 10, 2014 at 11:53 AM, Adrian Klaver <adrian.klaver@gmail.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/

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.us> wrote:

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@momjian.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.klaver@gmail.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general