Re: Moving tablespaces

On Sun, Dec 4, 2011 at 17:12, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Magnus Hagander wrote:
>> On Sun, Dec 4, 2011 at 00:43, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > Do we have any documentation about how to move a tablespace to a new
>> > directory? ?If not, I think we should write some.
>>
>> Do we have any support for doing it? (Yes, it works, but anything that
>> requires manual hacking of system catalogs really can't be considered
>> supported, can it?)
>
> True.  It is something we just don't support?  They have to dump, edit
> the dump, and reload, to change a tablespace directory?  Yikes.  Is that
> the state we are in?  Has no one complained about this?  They just use
> symlinks?

AFAIK, we don't. What you can do is take the db offline, change the
symlink, start it up agin and manually change
pg_tablespace.spclocation. That seems quite ugly though. And if you
forget one step, everything seems to work, but you have two
inconsistent definitions of the tablespace.

And IIRC, we don't actually *use* spclocation anywhere. How about we
just get rid of them as independents? We could either:

1) Remove the column. Rely on the symlink. Create a
pg_get_tablespace_location(oid) function, that could be used by
pg_dumpall and friends, that just reads the symlink.

2) Forcibly update the spclocation column when we start the server to
be whatever the symlink points to. That will at least automatically
restore the system to being consistent.

Option 1 would also make it a lot easier to in a supported way allow
tablespaces to have different locations on replica masters and slaves.
A tool like pg_basebackup could easily provide for something like
--relocate-tablespace mytblspc=/new/path and just rewrite the symlink
on the fly. But we cannot modify the pg_tablespace system catalog to
be different on the slave and the master...

It does seem rather obvious to me that this would be a win, so I'm
most likely missing something here. So please shoot a hole in the
theory for me ;)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/