Re: Upgrading vldb from 8.4.3 to 9.1.4

On Thu, Aug 30, 2012 at 02:48:51PM -0400, Freddie Burgess wrote:
> We have successfully upgraded Postgresql from 8.4.3 to 9.1.4 in our
> environment, but we have some questions/issues.
>
> Before the upgrade:
> Postgres8 data location -- /usr/local/pgsql/data -->
> /u01/fiber/postgres843/postgres_data
> Postgres9 data location -- /opt/PostgreSQL/9.1/data -->
> /u01/fiber/postgres_data
>
> After the upgrade:
> Postgres9 (userdb schema) data location -- /usr/local/pgsql/data -->
> /u01/fiber/postgres843/postgres_data
> Postgres9 data location -- /opt/PostgreSQL/9.1/data -->
> /u01/fiber/postgres_data
>
> We successfully moved all of the data files to /u01/fiber/postgres_data in
> an effort to have all of the files located in the same area, but we are
> currently stuck with regard to the two high level "data location" points:
> /usr/local/pgsql/data and /opt/PostgreSQL/9.1/data, which are both soft
> links to the fiber channel.
>
> Is there any way in Postgres to re-define the tablespace locations, or is
> the only option available to us is to create new tablespaces w/the correct
> directory paths and then move all of the objects (i.e. tables, indexes,
> etc.) to the new tablespaces? This would take some time to script due to
> the amount of objects we have, but it is doable. Are there any other
> alternatives?
>
> How does Postgres moves the objects? Does It makes a copy of the object in
> the new location and then deletes the original. If we Multiply by 5.4 TB of
> data files, are we looking at an estimated completion time on the same order
> of magnitude as the time it takes for us to perform the backup itself, which
> takes approximately 5-7 days?
>
> Finally, when is it safe to remove the 8.4.3 objects/directory?

You can move the data directory containing the default tablespace by
just shutting down the server and moving the directory and restarting
it. For user-defined tablespaces, you have to shut down the server and
repoint the symlinks in pg_tblspc, and update the
pg_tablespace.spclocation, which is removed in PG 9.2.

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

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