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 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 - coelho(at)cri(dot)ensmp(dot)fr