Обсуждение: How to move a tablespace?

How do you correctly move a tablespace? 

I am thinking this would be the process:
1.  Cleanly shutdown Postgres
2.  Move tablespace directory and all contents to new location
3.  Create a symlink from the new location back to the old location
4.  Restart Postgres

Would that be correct?  Or is there a better way.

Thanks,

Chris

On Friday 22 June 2007 14:14, Chris Hoover wrote:
> How do you correctly move a tablespace?
>
> I am thinking this would be the process:
> 1.  Cleanly shutdown Postgres
> 2.  Move tablespace directory and all contents to new location
> 3.  Create a symlink from the new location back to the old location
> 4.  Restart Postgres
>
> Would that be correct?  Or is there a better way.
>

ISTM it would be safer to just create a new tablspace within the db and then
move the objects in that tablespace over.

On a side note, this is one reason to make tablespaces on clean mount points,
so that you can swap the disk systems out underneath, but maintain the same
mountpoint, and not let the db care about the difference.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:
> On Friday 22 June 2007 14:14, Chris Hoover wrote:
>> How do you correctly move a tablespace?
>>
>> I am thinking this would be the process:
>> 1.  Cleanly shutdown Postgres
>> 2.  Move tablespace directory and all contents to new location
>> 3.  Create a symlink from the new location back to the old location
>> 4.  Restart Postgres

> On a side note, this is one reason to make tablespaces on clean mount points,
> so that you can swap the disk systems out underneath, but maintain the same
> mountpoint, and not let the db care about the difference.

But you'd still have to shut down the postmaster.

The fine manual sayeth

: 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 server
: is running; and after you restart the server, update the pg_tablespace
: catalog to show the new locations. (If you do not, pg_dump will continue
: to show the old tablespace locations.)

and elsewhere

: Each user-defined tablespace has a symbolic link inside the
: PGDATA/pg_tblspc directory, which points to the physical tablespace
: directory (as specified in its CREATE TABLESPACE command). The symbolic
: link is named after the tablespace's OID. Inside the physical tablespace
: directory there is a subdirectory for each database that has elements in
: the tablespace, named after the database's OID. Tables within that
: directory follow the filenode naming scheme. The pg_default tablespace
: is not accessed through pg_tblspc, but corresponds to
: PGDATA/base. Similarly, the pg_global tablespace is not accessed through
: pg_tblspc, but corresponds to PGDATA/global.

            regards, tom lane