Обсуждение: pg_upgrade

So we are making progress on our performance issues, we are splitting
the data, changing the index value etc. So far having some success,
but we also want to test out some of the options  and changes in the 9
branch, but trying to dump and restore 750gb of data is not all that
fun, so I'm trying to avoid that.

So upgraded from 8.4.4 64 bit to 9.1.1 64bit.

If we upgrade a database that just uses the public table space there
are no issues, works fine. However when we try to upgrade a db that
has tablespaces defined it errors out trying to load the data from the
then now new db.

The tablespaces are hardcoded with a path, so that seems to cause issues.

Steps I'm taking

Standard location of data /data/db
Standard binary location /pgsql/bin

I'm moving the standard location to /data1/db and moving the binaries
to /pgsql8/bin

WHY: because my build scripts put my binaries and data in these
locations, so without recreating my build process, I have to move the
current data and binary locations before I install 9.11

So I move olddata to /data1/db
oldbinary to /pgsql8/bin

new 9.1.1 db goes to /data/db
newbinary installs at /pgsql/

So when I run pg_upgrade (check validates the config), however trying
to the upgrade nets;
Restoring user relation files
  /data/queue/16384/16406
error while copying queue.adm_version (/data/queue/16384/16406 to
/data/queue/PG_9.1_201105231/16407/16406): No such file or directory
Failure, exiting

As you can see, it's sticking with it's original path and not
realizing that I'm trying now to install into /data from /data1

What is the flaw here? Do I have to rebuild my build process to
install in a different location?, not sure what my choices are here. I
mean I'm telling the upgrade process where new and old are located, I
believe it should be overriding something and not allowing the
included error.

Slaps and or pointers are welcome

Tory

ever tried symlinking?

Tory M Blue wrote:
> So we are making progress on our performance issues, we are splitting
> the data, changing the index value etc. So far having some success,
> but we also want to test out some of the options  and changes in the 9
> branch, but trying to dump and restore 750gb of data is not all that
> fun, so I'm trying to avoid that.
>
> So upgraded from 8.4.4 64 bit to 9.1.1 64bit.
>
> If we upgrade a database that just uses the public table space there
> are no issues, works fine. However when we try to upgrade a db that
> has tablespaces defined it errors out trying to load the data from the
> then now new db.
>
> The tablespaces are hardcoded with a path, so that seems to cause issues.
>
> Steps I'm taking
>
> Standard location of data /data/db
> Standard binary location /pgsql/bin
>
> I'm moving the standard location to /data1/db and moving the binaries
> to /pgsql8/bin
>
> WHY: because my build scripts put my binaries and data in these
> locations, so without recreating my build process, I have to move the
> current data and binary locations before I install 9.11
>
> So I move olddata to /data1/db
> oldbinary to /pgsql8/bin
>
> new 9.1.1 db goes to /data/db
> newbinary installs at /pgsql/
>
> So when I run pg_upgrade (check validates the config), however trying
> to the upgrade nets;
> Restoring user relation files
>   /data/queue/16384/16406
> error while copying queue.adm_version (/data/queue/16384/16406 to
> /data/queue/PG_9.1_201105231/16407/16406): No such file or directory
> Failure, exiting
>
> As you can see, it's sticking with it's original path and not
> realizing that I'm trying now to install into /data from /data1
>
> What is the flaw here? Do I have to rebuild my build process to
> install in a different location?, not sure what my choices are here. I
> mean I'm telling the upgrade process where new and old are located, I
> believe it should be overriding something and not allowing the
> included error.
>
> Slaps and or pointers are welcome

Well, I am not totally clear how you are moving things around, but I do
know pg_upgrade isn't happy to have the old and new cluster be very
different.

What I think is happening is that you didn't properly move the
tablespace in the old cluster.  We don't give you a very easy way to do
that.  You need to not only move the directory, but you need to update
the symlinks in data/pg_tblspc/, and update the pg_tablespace system
table.  Did you do all of that?  Does the 8.4 server see the tablespace
properly after the move, but before pg_upgrade?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Sat, Dec 3, 2011 at 6:04 AM, Bruce Momjian <bruce@momjian.us> wrote:

> Well, I am not totally clear how you are moving things around, but I do
> know pg_upgrade isn't happy to have the old and new cluster be very
> different.
>
> What I think is happening is that you didn't properly move the
> tablespace in the old cluster.  We don't give you a very easy way to do
> that.  You need to not only move the directory, but you need to update
> the symlinks in data/pg_tblspc/, and update the pg_tablespace system
> table.  Did you do all of that?  Does the 8.4 server see the tablespace
> properly after the move, but before pg_upgrade?


Simple answer is umm no..
"http://www.postgresql.org/docs/current/static/pgupgrade.html" is
obviously lacking than :)

Soooo I can take what you have told me and see if I can't attempt to
make those things happen and try again. Makes sense, but boy that's a
large piece of info missing in the document!

Thanks again

Tory

Tory M Blue wrote:
> On Sat, Dec 3, 2011 at 6:04 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> > Well, I am not totally clear how you are moving things around, but I do
> > know pg_upgrade isn't happy to have the old and new cluster be very
> > different.
> >
> > What I think is happening is that you didn't properly move the
> > tablespace in the old cluster. ?We don't give you a very easy way to do
> > that. ?You need to not only move the directory, but you need to update
> > the symlinks in data/pg_tblspc/, and update the pg_tablespace system
> > table. ?Did you do all of that? ?Does the 8.4 server see the tablespace
> > properly after the move, but before pg_upgrade?
>
>
> Simple answer is umm no..

The "no" is an answer to which question?

> "http://www.postgresql.org/docs/current/static/pgupgrade.html" is
> obviously lacking than :)
>
> Soooo I can take what you have told me and see if I can't attempt to
> make those things happen and try again. Makes sense, but boy that's a
> large piece of info missing in the document!

You mean moving tablespaces?  That isn't something pg_upgrade deals
with.  If we need docs to move tablespaces, it is a missing piece of our
main docs, not something pg_upgrade would ever mention.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Bruce Momjian wrote:
> Tory M Blue wrote:
> > On Sat, Dec 3, 2011 at 6:04 AM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > > Well, I am not totally clear how you are moving things around, but I do
> > > know pg_upgrade isn't happy to have the old and new cluster be very
> > > different.
> > >
> > > What I think is happening is that you didn't properly move the
> > > tablespace in the old cluster. ?We don't give you a very easy way to do
> > > that. ?You need to not only move the directory, but you need to update
> > > the symlinks in data/pg_tblspc/, and update the pg_tablespace system
> > > table. ?Did you do all of that? ?Does the 8.4 server see the tablespace
> > > properly after the move, but before pg_upgrade?
> >
> >
> > Simple answer is umm no..
>
> The "no" is an answer to which question?
>
> > "http://www.postgresql.org/docs/current/static/pgupgrade.html" is
> > obviously lacking than :)
> >
> > Soooo I can take what you have told me and see if I can't attempt to
> > make those things happen and try again. Makes sense, but boy that's a
> > large piece of info missing in the document!
>
> You mean moving tablespaces?  That isn't something pg_upgrade deals
> with.  If we need docs to move tablespaces, it is a missing piece of our
> main docs, not something pg_upgrade would ever mention.

FYI, I have asked on the docs list about getting this documented.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Bruce Momjian
> Sent: Saturday, December 03, 2011 6:42 PM
> To: Tory M Blue
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] pg_upgrade
>
> Tory M Blue wrote:
> > On Sat, Dec 3, 2011 at 6:04 AM, Bruce Momjian <bruce@momjian.us>
> wrote:
> >
> > > Well, I am not totally clear how you are moving things around, but
> I do
> > > know pg_upgrade isn't happy to have the old and new cluster be very
> > > different.
> > >
> > > What I think is happening is that you didn't properly move the
> > > tablespace in the old cluster. ?We don't give you a very easy way
> to do
> > > that. ?You need to not only move the directory, but you need to
> update
> > > the symlinks in data/pg_tblspc/, and update the pg_tablespace
> system
> > > table. ?Did you do all of that? ?Does the 8.4 server see the
> tablespace
> > > properly after the move, but before pg_upgrade?
> >
> >
> > Simple answer is umm no..
>
> The "no" is an answer to which question?
>
> > "http://www.postgresql.org/docs/current/static/pgupgrade.html" is
> > obviously lacking than :)
> >
> > Soooo I can take what you have told me and see if I can't attempt to
> > make those things happen and try again. Makes sense, but boy that's a
> > large piece of info missing in the document!
>
> You mean moving tablespaces?  That isn't something pg_upgrade deals
> with.  If we need docs to move tablespaces, it is a missing piece of
> our
> main docs, not something pg_upgrade would ever mention.

If I'm reading the issue correctly, and pg_upgrade gets part way through an upgrade then fails if it hits a tablespace
-it seems to me like the pg_upgrade should check for such a condition at the initial validation stage not proceed if
found.

Brad.

Nicholson, Brad (Toronto, ON, CA) wrote:
> > You mean moving tablespaces?  That isn't something pg_upgrade deals
> > with.  If we need docs to move tablespaces, it is a missing piece of
> > our
> > main docs, not something pg_upgrade would ever mention.
>
> If I'm reading the issue correctly, and pg_upgrade gets part way through
> an upgrade then fails if it hits a tablespace - it seems to me like
> the pg_upgrade should check for such a condition at the initial
> validation stage not proceed if found.

Checking for all such cases would make pg_upgrade huge and unusable.  If
you messed up your configuration, pg_upgrade can't check for every such
case.  There are thosands of ways people can mess up their configuration.

I think you should read up on how pg_upgrade attempts to be minimal:

    http://momjian.us/main/blogs/pgblog/2011.html#June_15_2011_2

On a related note, Magnus is working on code for Postgres 9.2 that would
allow for easier moving of tablespaces.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

> -----Original Message-----
> From: Bruce Momjian [mailto:bruce@momjian.us]
> Sent: Monday, December 05, 2011 10:24 AM
> To: Nicholson, Brad (Toronto, ON, CA)
> Cc: Tory M Blue; pgsql-performance@postgresql.org; Magnus Hagander
> Subject: Re: [PERFORM] pg_upgrade
>
> Nicholson, Brad (Toronto, ON, CA) wrote:
> > > You mean moving tablespaces?  That isn't something pg_upgrade deals
> > > with.  If we need docs to move tablespaces, it is a missing piece
> of
> > > our
> > > main docs, not something pg_upgrade would ever mention.
> >
> > If I'm reading the issue correctly, and pg_upgrade gets part way
> through
> > an upgrade then fails if it hits a tablespace - it seems to me like
> > the pg_upgrade should check for such a condition at the initial
> > validation stage not proceed if found.
>
> Checking for all such cases would make pg_upgrade huge and unusable.
> If
> you messed up your configuration, pg_upgrade can't check for every such
> case.  There are thosands of ways people can mess up their
> configuration.

Based on the OP this does not seem like a messed up configuration.  It sounds like the OP used a fully supported core
featureof Postgres (tablespaces) and pg_upgrade failed as a result.  I think having our upgrade utility fail under such
circumstancesis a bad thing. 

Brad.

Nicholson, Brad (Toronto, ON, CA) wrote:
> > -----Original Message-----
> > From: Bruce Momjian [mailto:bruce@momjian.us]
> > Sent: Monday, December 05, 2011 10:24 AM
> > To: Nicholson, Brad (Toronto, ON, CA)
> > Cc: Tory M Blue; pgsql-performance@postgresql.org; Magnus Hagander
> > Subject: Re: [PERFORM] pg_upgrade
> >
> > Nicholson, Brad (Toronto, ON, CA) wrote:
> > > > You mean moving tablespaces?  That isn't something pg_upgrade deals
> > > > with.  If we need docs to move tablespaces, it is a missing piece
> > of
> > > > our
> > > > main docs, not something pg_upgrade would ever mention.
> > >
> > > If I'm reading the issue correctly, and pg_upgrade gets part way
> > through
> > > an upgrade then fails if it hits a tablespace - it seems to me like
> > > the pg_upgrade should check for such a condition at the initial
> > > validation stage not proceed if found.
> >
> > Checking for all such cases would make pg_upgrade huge and unusable.
> > If
> > you messed up your configuration, pg_upgrade can't check for every such
> > case.  There are thosands of ways people can mess up their
> > configuration.
>
> Based on the OP this does not seem like a messed up configuration.  It
> sounds like the OP used a fully supported core feature of Postgres
> (tablespaces) and pg_upgrade failed as a result.  I think having our
> upgrade utility fail under such circumstances is a bad thing.

The OP has not indicated exactly what he did to move the tablespaces, so
I have to assume he changed the SQL location but not the symbolic link
location, or some other misconfiguration.  Can someone provide the steps
that caused the failure?

pg_upgrade works fine for tablespaces so there must be something
different about his configuration.  Unless I hear details, I have to
assume the tablespace move was done incorrectly.  This is the first
tablespace failure like this I have ever gotten, and I do test
tablespaces.  Perhaps something is wrong, but I can't guess what it is.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Mon, Dec 5, 2011 at 7:34 AM, Bruce Momjian <bruce@momjian.us> wrote:
> Nicholson, Brad (Toronto, ON, CA) wrote:
>>
>> Based on the OP this does not seem like a messed up configuration.  It
>> sounds like the OP used a fully supported core feature of Postgres
>> (tablespaces) and pg_upgrade failed as a result.  I think having our
>> upgrade utility fail under such circumstances is a bad thing.
>
> The OP has not indicated exactly what he did to move the tablespaces, so
> I have to assume he changed the SQL location but not the symbolic link
> location, or some other misconfiguration.  Can someone provide the steps
> that caused the failure?
>
> pg_upgrade works fine for tablespaces so there must be something
> different about his configuration.  Unless I hear details, I have to
> assume the tablespace move was done incorrectly.  This is the first
> tablespace failure like this I have ever gotten, and I do test
> tablespaces.  Perhaps something is wrong, but I can't guess what it is.
>


Sorry for the late response, I didn't mean to host a party and step out!

Bruce is right, I didn't move tablespaces (I didn't know to be honest
I had to, but it makes sense). I simply moved the location of the data
files, from /data to /data1. But I did "not", change any sym links or
do any other pre-steps, other than install the new binary, make sure
that there was a new and old data location as well as a new and old
binary location.

Since my build processes installs data files at /data and binary at
/pgsql/, I simply moved the old Data and binaries, before installing
my new build. So /pgsql/ became /pgsql8/ and /data/ became /data1/

I do understand what you are all saying in regards to the tablespace
links and tablespace locations.It made total sense when Bruce pointed
it out initially. However I'm not sure if I should of known that
pg_upgrade doesn't handle this, and or this would be a concern.
pg_upgrade asks for old and new locations, so one would think that
this information would be used for the upgrade process, including
potentially changing tablespace paths during the migration step
<shrug>, this is above my pay grade.

But initial response to all this, is umm we have not really made a
dump/restore unnecessary with the latest releases of Postgres than, as
I would have to think that there is a high percentage of users whom
use tablespaces.

Tory

Tory M Blue wrote:
> Bruce is right, I didn't move tablespaces (I didn't know to be honest
> I had to, but it makes sense). I simply moved the location of the data
> files, from /data to /data1. But I did "not", change any sym links or

I was unclear if you moved the data directory or the tablespace.  Your
example showed you moving something that didn't look like data
directories:

    > So I move olddata to /data1/db
    > oldbinary to /pgsql8/bin
    >
    > new 9.1.1 db goes to /data/db
    > newbinary installs at /pgsql/
    >
    > So when I run pg_upgrade (check validates the config), however trying
    > to the upgrade nets;
    > Restoring user relation files
    >   /data/queue/16384/16406
    > error while copying queue.adm_version (/data/queue/16384/16406 to
    > /data/queue/PG_9.1_201105231/16407/16406): No such file or directory
    > Failure, exiting

/data/db and /data/queue are not data locations, or at least they are
not ones we create during the install.  Was the real data directory and
the tablespaces all under /data1?  Did you define these tablespace
locations using relative paths?

> do any other pre-steps, other than install the new binary, make sure
> that there was a new and old data location as well as a new and old
> binary location.

You can definitely move data directories around.

> Since my build processes installs data files at /data and binary at
> /pgsql/, I simply moved the old Data and binaries, before installing
> my new build. So /pgsql/ became /pgsql8/ and /data/ became /data1/

I think you can do that but your error messages don't say that.

> I do understand what you are all saying in regards to the tablespace
> links and tablespace locations.It made total sense when Bruce pointed
> it out initially. However I'm not sure if I should of known that
> pg_upgrade doesn't handle this, and or this would be a concern.
> pg_upgrade asks for old and new locations, so one would think that
> this information would be used for the upgrade process, including
> potentially changing tablespace paths during the migration step
> <shrug>, this is above my pay grade.

There is no Postgres support for moving tablespaces, so it isn't
surprising that pg_upgrade doesn't handle it.

> But initial response to all this, is umm we have not really made a
> dump/restore unnecessary with the latest releases of Postgres than, as
> I would have to think that there is a high percentage of users whom
> use tablespaces.

Yes, but they don't change tablespace locations during the upgrade.  In
fact, we have had surprisingly few (zero) request for moving
tablespaces, and now we are trying to implement this for Postgres 9.2.
The normal API will be to have the user move the tablespace before the
upgrade, but as I said before, it isn't easy to do now in Postgres.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian <bruce@momjian.us> wrote:
>> But initial response to all this, is umm we have not really made a
>> dump/restore unnecessary with the latest releases of Postgres than, as
>> I would have to think that there is a high percentage of users whom
>> use tablespaces.
>
> Yes, but they don't change tablespace locations during the upgrade.  In
> fact, we have had surprisingly few (zero) request for moving
> tablespaces, and now we are trying to implement this for Postgres 9.2.
> The normal API will be to have the user move the tablespace before the
> upgrade, but as I said before, it isn't easy to do now in Postgres.

Okay think here is where I'm confused. "they don't change tablespace",
okay how are they doing the upgrade?  Do they leave the olddatadir in
the default location and create a new one elsewhere, vs where I'm kind
of doing the opposite?

Thanks again!
Tory

On Mon, Dec 5, 2011 at 10:31 AM, Tory M Blue <tmblue@gmail.com> wrote:
> On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian <bruce@momjian.us> wrote:
>>> But initial response to all this, is umm we have not really made a
>>> dump/restore unnecessary with the latest releases of Postgres than, as
>>> I would have to think that there is a high percentage of users whom
>>> use tablespaces.
>>
>> Yes, but they don't change tablespace locations during the upgrade.  In
>> fact, we have had surprisingly few (zero) request for moving
>> tablespaces, and now we are trying to implement this for Postgres 9.2.
>> The normal API will be to have the user move the tablespace before the
>> upgrade, but as I said before, it isn't easy to do now in Postgres.
>
> Okay think here is where I'm confused. "they don't change tablespace",
> okay how are they doing the upgrade?  Do they leave the olddatadir in
> the default location and create a new one elsewhere, vs where I'm kind
> of doing the opposite?

Okay right

So changed the symlink in pg_tblspaces, and changed the path inside
the db, and it appears to have worked. These were either the "doh
pieces" or the missing components that you helped point me to. Thank
you!

Tory

-bash-4.0$ /logs-all/temp/pg_upgrade  --old-datadir "/data1/db"
--new-datadir "/data/db" --old-bindir "/ipix/pgsql8/bin" --new-bindir
"/ipix/pgsql/bin"
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
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
Checking for large objects                                  ok
Creating catalog dump                                       ok
Checking for prepared transactions                          ok
Checking for presence of required libraries                 ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /data1/db/global/pg_control.old.

Performing Upgrade
------------------
Adding ".old" suffix to old global/pg_control               ok
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting new commit clogs                                   ok
Copying old commit clogs to new server                      ok
Setting next transaction id for new cluster                 ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Creating databases in the new cluster                       ok
Adding support functions to new cluster                     ok
Restoring database schema to new cluster                    ok
Removing support functions from new cluster                 ok
Restoring user relation files
                                                            ok
Setting next oid for new cluster                            ok
Creating script to delete old cluster                       ok
Checking for large objects                                  ok

Upgrade complete
----------------
| Optimizer statistics are not transferred by pg_upgrade
| so consider running:
|     vacuumdb --all --analyze-only
| on the newly-upgraded cluster.

| Running this script will delete the old cluster's data files:
|     /data/pgsql/delete_old_cluster.sh
-bash-4.0$

Tory M Blue wrote:
> On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian <bruce@momjian.us> wrote:
> >> But initial response to all this, is umm we have not really made a
> >> dump/restore unnecessary with the latest releases of Postgres than, as
> >> I would have to think that there is a high percentage of users whom
> >> use tablespaces.
> >
> > Yes, but they don't change tablespace locations during the upgrade. ?In
> > fact, we have had surprisingly few (zero) request for moving
> > tablespaces, and now we are trying to implement this for Postgres 9.2.
> > The normal API will be to have the user move the tablespace before the
> > upgrade, but as I said before, it isn't easy to do now in Postgres.
>
> Okay think here is where I'm confused. "they don't change tablespace",
> okay how are they doing the upgrade?  Do they leave the olddatadir in
> the default location and create a new one elsewhere, vs where I'm kind
> of doing the opposite?

If you look in a 9.0+ tablespace directory, you will see that each
cluster has its own subdirectory:

    test=> create tablespace tb1 location '/u/pg/tb1';
    CREATE TABLESPACE
    test=> \q
    $ lf /u/pg/tb1
    PG_9.2_201111231/

That means if I upgrade to 9.3, there will be another subdirectory for
9.3, _inside_ the same tablespace location.  This change was added in
Postgres 9.0 to allow for upgrades without having to move tablespaces.

Now, since you are upgrading from 8.4, and don't have a subdirectory,
the 9.1 cluster will be created inside the tablespace directory, so it
will look like:

    323234/ 423411/ 932323/ PG_9.1_201105231/
                            ----------------

I realize that is kind of confusing, but it works just fine, and
pg_upgrade will provide you with a script to delete the old cluster, and
its subdirectories, when you are ready.

I hope this helps clarify things.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Tory M Blue wrote:
> On Mon, Dec 5, 2011 at 10:31 AM, Tory M Blue <tmblue@gmail.com> wrote:
> > On Mon, Dec 5, 2011 at 10:22 AM, Bruce Momjian <bruce@momjian.us> wrote:
> >>> But initial response to all this, is umm we have not really made a
> >>> dump/restore unnecessary with the latest releases of Postgres than, as
> >>> I would have to think that there is a high percentage of users whom
> >>> use tablespaces.
> >>
> >> Yes, but they don't change tablespace locations during the upgrade. ?In
> >> fact, we have had surprisingly few (zero) request for moving
> >> tablespaces, and now we are trying to implement this for Postgres 9.2.
> >> The normal API will be to have the user move the tablespace before the
> >> upgrade, but as I said before, it isn't easy to do now in Postgres.
> >
> > Okay think here is where I'm confused. "they don't change tablespace",
> > okay how are they doing the upgrade? ?Do they leave the olddatadir in
> > the default location and create a new one elsewhere, vs where I'm kind
> > of doing the opposite?
>
> Okay right
>
> So changed the symlink in pg_tblspaces, and changed the path inside
> the db, and it appears to have worked. These were either the "doh
> pieces" or the missing components that you helped point me to. Thank
> you!

See my other email --- this might not be necessary.

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


>
> Tory
>
> -bash-4.0$ /logs-all/temp/pg_upgrade  --old-datadir "/data1/db"
> --new-datadir "/data/db" --old-bindir "/ipix/pgsql8/bin" --new-bindir
> "/ipix/pgsql/bin"
> Performing Consistency Checks
> -----------------------------
> Checking current, bin, and data directories                 ok
> 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
> Checking for large objects                                  ok
> Creating catalog dump                                       ok
> Checking for prepared transactions                          ok
> Checking for presence of required libraries                 ok
>
> | If pg_upgrade fails after this point, you must
> | re-initdb the new cluster before continuing.
> | You will also need to remove the ".old" suffix
> | from /data1/db/global/pg_control.old.
>
> Performing Upgrade
> ------------------
> Adding ".old" suffix to old global/pg_control               ok
> Analyzing all rows in the new cluster                       ok
> Freezing all rows on the new cluster                        ok
> Deleting new commit clogs                                   ok
> Copying old commit clogs to new server                      ok
> Setting next transaction id for new cluster                 ok
> Resetting WAL archives                                      ok
> Setting frozenxid counters in new cluster                   ok
> Creating databases in the new cluster                       ok
> Adding support functions to new cluster                     ok
> Restoring database schema to new cluster                    ok
> Removing support functions from new cluster                 ok
> Restoring user relation files
>                                                             ok
> Setting next oid for new cluster                            ok
> Creating script to delete old cluster                       ok
> Checking for large objects                                  ok
>
> Upgrade complete
> ----------------
> | Optimizer statistics are not transferred by pg_upgrade
> | so consider running:
> |     vacuumdb --all --analyze-only
> | on the newly-upgraded cluster.
>
> | Running this script will delete the old cluster's data files:
> |     /data/pgsql/delete_old_cluster.sh
> -bash-4.0$

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Mon, Dec 5, 2011 at 11:08 AM, Bruce Momjian <bruce@momjian.us> wrote:

>
> If you look in a 9.0+ tablespace directory, you will see that each
> cluster has its own subdirectory:
>
>        test=> create tablespace tb1 location '/u/pg/tb1';
>        CREATE TABLESPACE
>        test=> \q
>        $ lf /u/pg/tb1
>        PG_9.2_201111231/
>
> That means if I upgrade to 9.3, there will be another subdirectory for
> 9.3, _inside_ the same tablespace location.  This change was added in
> Postgres 9.0 to allow for upgrades without having to move tablespaces.
>
> Now, since you are upgrading from 8.4, and don't have a subdirectory,
> the 9.1 cluster will be created inside the tablespace directory, so it
> will look like:
>
>        323234/ 423411/ 932323/ PG_9.1_201105231/
>                                ----------------
>
> I realize that is kind of confusing, but it works just fine, and
> pg_upgrade will provide you with a script to delete the old cluster, and
> its subdirectories, when you are ready.
>
> I hope this helps clarify things.
>

Well I could see the PG_9.1 or whatever directory being created,
however I would still get a fail. Once I modified the internal
tablespace path and the filesystem symlink, it worked just fine.
Having to create 6-10 symlinks is kind of cruddy and altering the
paths (although that is not bad). But it's working.

So I at least have a method to make this work :)

Tory

From my last report I had success but it was successful due to lots of
manual steps. I figured it may be safer to just create a new rpm,
installing to pgsql9 specific directories and a new data directory.

This allows pg_upgrade to complete successfully (so it says). However
my new data directory is empty and the old data directory now has what
appears to be 8.4 data and the 9.1 data.

/data is olddatadir original data dir

[root@devqdb03 queue]# ll /data/queue
total 12
drwx------ 2 postgres dba 4096 2011-12-07 09:44 16384
drwx------ 3 postgres dba 4096 2011-12-07 11:34 PG_9.1_201105231
-rw------- 1 postgres dba    4 2011-12-07 09:44 PG_VERSION

/data1 is the new 9.1 installed location.
[root@devqdb03 queue]# ll /data1/queue/
total 0

Do I have to manually move the new PG_9.1..... data to /data1 or. I'm
just confused at what I'm looking at here.

If I don't move anything and start up the DB , I get this

psql (8.4.4, server 9.1.1)
WARNING: psql version 8.4, server version 9.1.
         Some psql features might not work.
Type "help" for help.

Sorry my upgrade process has been an ugly mess :)

Tory

Tory M Blue wrote:
> >From my last report I had success but it was successful due to lots of
> manual steps. I figured it may be safer to just create a new rpm,
> installing to pgsql9 specific directories and a new data directory.
>
> This allows pg_upgrade to complete successfully (so it says). However
> my new data directory is empty and the old data directory now has what
> appears to be 8.4 data and the 9.1 data.
>
> /data is olddatadir original data dir
>
> [root@devqdb03 queue]# ll /data/queue
> total 12
> drwx------ 2 postgres dba 4096 2011-12-07 09:44 16384
> drwx------ 3 postgres dba 4096 2011-12-07 11:34 PG_9.1_201105231
> -rw------- 1 postgres dba    4 2011-12-07 09:44 PG_VERSION

That sure looks like a tablespace to me, not a data directory.

>
> /data1 is the new 9.1 installed location.
> [root@devqdb03 queue]# ll /data1/queue/
> total 0
>
> Do I have to manually move the new PG_9.1..... data to /data1 or. I'm
> just confused at what I'm looking at here.
>
> If I don't move anything and start up the DB , I get this
>
> psql (8.4.4, server 9.1.1)
> WARNING: psql version 8.4, server version 9.1.
>          Some psql features might not work.
> Type "help" for help.
>
> Sorry my upgrade process has been an ugly mess :)

You are using an 8.4.4 psql to connect to a 9.1.1 server.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +