Обсуждение: 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

Hello,

I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade utility. I need to use pg_upgrade because my production database is 800GB+ and with over 80 tablespaces and doing an export from 9.0 and importing to 9.3 would take at least 2 days.

Currently I am testing on the development database which is only 100GB with a same number of tablespaces. I am working on FreeBSD with jails. So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the data and binary directories for the 9.0 jail.

Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ -d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c

As you can see the data and binary files for 9.0 are in /home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 resides in the default location.

When running the check it reports that both clusters are compatible. Once the actual process starts it will work fine until it starts up the 9.3 to copy data over. The problem that I am having is that pg_upgrade is creating the 93XXXX files under the old directory and not the new one. So when 9.3 goes to import it doesn't find anything.

Now, both versions can't share the same /data directory for obvious reasons. Is there any way to make pg_upgrade actually export the new 9.3 files into the 9.3 directory supplied in the pg_upgrade command? I am also open to any other upgrade ideas.

Thanks,

Joseph

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

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

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

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

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

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

On 12/19/2013 1:06 PM, Joseph Kregloh wrote:
> It's easier to keep things segregated. It is not anymore different
> than doing the upgrade in the same jail. Which at the end of the day
> you are doing the upgrade in the same jail, because at the end of the
> day pg_upgrade just needs the old data an binary to start and create
> some dump files.

pg_upgrade needs to access the old data AND all the tablespaces at the
same paths as the old server sees them AND the new data and tablespaces
at the same path as the NEW server sees them.   if the two servers are
in different jails, I don't see how you could make that work... if you
run pg_upgrade in the host system, then all the paths are different for
both sets of data and tablespaces.





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

On 12/19/2013 01:06 PM, Joseph Kregloh wrote:
> It's easier to keep things segregated. It is not anymore different than
> doing the upgrade in the same jail. Which at the end of the day you are
> doing the upgrade in the same jail, because at the end of the day
> pg_upgrade just needs the old data an binary to start and create some
> dump files.
>
> But the real problem here is with the table spaces. Because in order to
> copy the relation over I would need to mount the old data to the
> /usr/local/pgsql/data on the new jail. The relation would be there and
> would finish successfully(I did this exercise). However the 9.3 install
> would be in a different directory, say /usr/local/pgsql_93 and will not
> have the data files because they now live in the old install location.

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

In your original post you have:

pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c

Note:  -d /home/jkregloh/pg_data/

In your mount info you have:

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

If I am following correctly should it not be:

-d /home/jkregloh/pg_data/data

>
> -Joseph
>
>



--
Adrian Klaver
adrian.klaver@gmail.com

On Thu, Dec 19, 2013 at 01:08:18PM -0800, Sergey Konoplev wrote:
> On Thu, Dec 19, 2013 at 12:49 PM, Joseph Kregloh
> <jkregloh@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. +

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

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

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

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

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

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

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

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

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

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

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.

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

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

...

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

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


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

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


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

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




>
> -Joseph


--
Adrian Klaver
adrian.klaver@gmail.com

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
>     <http://www.postgresql.org/message-id/20130214052952.GA10606@momjian.us>
>
>
> Let me read into this.

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

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

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

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

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

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

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

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.

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

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

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

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

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

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


--
Adrian Klaver
adrian.klaver@gmail.com

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

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

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

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

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

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

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

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

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

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

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

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