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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs