pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMoving a tablespace
3+ messages / 3 participants
[nested] [flat]
* Moving a tablespace
@ 2006-08-23 01:16 Craig A. James <cjames@modgraph-usa.com>
2006-08-23 01:36 ` Re: Moving a tablespace Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Craig A. James @ 2006-08-23 01:16 UTC (permalink / raw)
To: pgsql-performance
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 can move it and put a symbolic link in for /disk2, but this is somewhat unsatisfactory since "/disk2" would have to exist forever.
Thanks,
Craig
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Moving a tablespace
2006-08-23 01:16 Moving a tablespace Craig A. James <cjames@modgraph-usa.com>
@ 2006-08-23 01:36 ` Michael Fuhr <mike@fuhr.org>
2006-08-23 02:34 ` Re: Moving a tablespace Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2006-08-23 01:36 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Moving a tablespace
2006-08-23 01:16 Moving a tablespace Craig A. James <cjames@modgraph-usa.com>
2006-08-23 01:36 ` Re: Moving a tablespace Michael Fuhr <mike@fuhr.org>
@ 2006-08-23 02:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-08-23 02:34 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-08-23 02:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-23 01:16 Moving a tablespace Craig A. James <cjames@modgraph-usa.com>
2006-08-23 01:36 ` Michael Fuhr <mike@fuhr.org>
2006-08-23 02:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox