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 is easy.
. 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 the base, the hard crashes
but I have to restore it elsewhere), but I guess the implementation
is not really simple and may require 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 to an error.
. solution 3
separate object creation and tablespace specification statements
in pg_dump/pg_restore, so that tablespace-related failures do 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 exists anyway
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 - [EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
