Thread: Moving a tablespace

Suppose, hypothetically of course, someone lacked foresight, and put a tablespace somewhere with a dumb name, like
"/disk2",instead of using a symbolic link with a more descriptive name.   And then /disk2 needs to be renamed, say to
"/postgres_data",and this (hypothetical) DBA realizes he has made a dumb mistake. 

Is there a way to move a tablespace to a new location without a dump/restore?  I, er, this hypothetical guy, knows he
canmove it and put a symbolic link in for /disk2, but this is somewhat unsatisfactory since "/disk2" would have to
existforever. 

Thanks,
Craig

On Tue, Aug 22, 2006 at 06:16:54PM -0700, Craig A. James wrote:
> Is there a way to move a tablespace to a new location without a
> dump/restore?  I, er, this hypothetical guy, knows he can move it and put a
> symbolic link in for /disk2, but this is somewhat unsatisfactory since
> "/disk2" would have to exist forever.

The last paragraph of the Tablespaces documentation might be helpful:

http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html

"The directory $PGDATA/pg_tblspc contains symbolic links that point
to each of the non-built-in tablespaces defined in the cluster.
Although not recommended, it is possible to adjust the tablespace
layout by hand by redefining these links.  Two warnings: do not do
so while the postmaster is running; and after you restart the
postmaster, update the pg_tablespace catalog to show the new
locations.  (If you do not, pg_dump will continue to show the old
tablespace locations.)"

I just tested this and it appeared to work, but this hypothetical
DBA might want to wait for others to comment before proceeding.  He
might also want to initdb and populate a test cluster and practice
the procedure before doing it for real.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Aug 22, 2006 at 06:16:54PM -0700, Craig A. James wrote:
>> Is there a way to move a tablespace to a new location without a
>> dump/restore?

> The last paragraph of the Tablespaces documentation might be helpful:
> http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html

> I just tested this and it appeared to work, but this hypothetical
> DBA might want to wait for others to comment before proceeding.

AFAIK it works fine.  Shut down postmaster, move tablespace's directory
tree somewhere else, fix the symbolic link in $PGDATA/pg_tblspc, start
postmaster, update the pg_tablespace entry.  There isn't anyplace else
in Postgres that knows where that link leads.  But if you are running
a hot PITR backup, see the caveats in TFM about what will happen on the
backup machine.

> He might also want to initdb and populate a test cluster and practice
> the procedure before doing it for real.

"Always mount a scratch monkey" ...

            regards, tom lane