Re: pg_upgrade - Mailing list pgsql-performance

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