tablespace and pg_dump/restore

Dear Tom,

> > as many dummy and unknown ... tablespaces
>
> There are any number of ways to find it out --- read the output of
> "pg_restore -s", or just try the restore and observe the errors.

Ok, you're right on this point.

But I'm looking for something cleaner than grepping pg_restore output...


> Besides which, we are talking here about the output of pg_dumpall,
> which is currently always text.

AFAIC, I was talking about pg_dump in this thread.


> > Then bad news, ISTM that altering the tablespace of an index, a sequence
> > or a schema is not implemented.
>
> Wrong, unnecessary, and trivial respectively.

(1) wrong one: you'll have to update or clarify the documentation;-)   no ALTER INDEX... or do you mean DROP/CREATE
INDEX?  Manually updating pg_class won't move the files.
 

(2) unnecessary one: if a sequence is in a tablespace that I want to   drop (maybe I need to change the disk), it seems
necessary.  I might DROP/CREATE, which might interact with the application...
 

(3) trivial one: I guess you mean update pg_namespace by hand? Sure.

For all cases I was talking about an "ALTER" syntax.  Manual DROP/CREATE
or UPDATE, or moving files, is not a nice option.


> I see this request as being exactly on a par with requests to make
> pg_dumpall output restore into a different set of databases, or
> into a different set of schemas than what was dumped from.

A schema is an application issue. The application does not change if I
move or restore it.

Changing the database is easy with pg_dump/pg_restore, which is my
concern.

However a tablespace is an administration issue. It is likely to change
from server to server.

ISTM that it is quite different.


> Sure, it would be convenient sometimes.  But it's not *necessary*

My point is that it *is* necessary (meaning really useful). As it seems
that I cannot convince people, it surely mean that I'm just wrong about
that very point;-)


> Could we have less straw-man-bashing

I'm not sure about what this means, but I'm sorry if it means that my tone
is not appropriate. I'm just trying to convince.


> and more discussion of the minimum necessary solution for this problem?

I can also do that;-) I was beginning by trying to convince people that
the problem exists and should be addressed before 8.0 is out.

. solution 0a
 hack manually the SQL stream out of pg_restore: pg_restore ... | sed 's/TABLESPACE [a-zA-Z0-9_]*//g' | psql ...


. solution 0b
 dummy tablespaces just to please pg_restore. ISTM that it are hard to reverse/clean afterwards.
 sh> pg_restore ... | grep 'ERROR:  tablespace' sh> mkdir ts1 ts2 ts3 ts4 ts5 pg> create tablespace "some-name"
location"ts1"; ... sh> pg_restore ...
 


. solution 1a
 pg_dump --ignore-tablespace option so that TABLESPACE are not appended at all in the dump. I guess the implementation
iseasy.
 


. solution 1b
 pg_restore --ignore-tablespace would be even better because you don't need to think about it a dump time (say I saved
thebase, the hard crashes but I have to restore it elsewhere), but I guess the implementation is not really simple and
mayrequire to change the dump format. Maybe with the server cooperation as in next proposal.
 


. solution 2
 add some server setting on restoration so that wrong/all tablespace directives are simply ignored, instead of leading
toan error.
 


. solution 3
 separate object creation and tablespace specification statements in pg_dump/pg_restore, so that tablespace-related
failuresdo not prevent object restoration. It needs the ALTER syntax.
 
 CREATE TABLE foo ... TABLESPACE x; vs CREATE TABLE foo ....; ALTER TABLE foo SET TABLESPACE x; -- may fail, but foo
existsanyway
 


I like v3 better. I don't like "workarounds" v0a and v0b. My taste;-)


> It's long past time to be gilding the lily for 8.0.  You can give it a
> new paint job in 8.1, if you like.

My feeling is that it is really useful for all people that would use
tablespace with 8.0. and will try to move/restore databases. Maybe too few
people to care.

As for the time, I thought a beta was meant for testing features and
reporting issues. I'm just doing that!

Thanks anyway for your answers and your time, have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr