Обсуждение: 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