Re: Using ALTER TABLESPACE in pg_dump

I have read through this thread hoping that a solution would be found
but I see we are still poking. My ideas:

o Anything that works only for pg_restore and hence doesn't
work for ASCII dumps isn't an acceptable solution
o Creating the tablespaces before the dump is restored is
a good solution for moving tablespaces, but as Tom pointed
out, it doesn't work well for non-super-user restores
o Moving the indexes can't be dont easily after they are
created because they are not zero-length files
o The soft-failure GUC option for non-existant tablespaces
is a hack just for use by pg_dump. It doesn't fix the
problem that the tablespace clause makes the SQL nonstandard.

And the best quote from the thread:

Philip Warner wrote:
> <soapbox>
> A fact I positively loath! Relying on the 'bluder-on-regardless' approach
> is not something I'd like to enshrine.
> </soapbox>

The 'bluder-on-regardless' phrase is very funny.

---------------------------------------------------------------------------

Philip Warner wrote:
> At 05:41 AM 21/10/2004, Robert Treat wrote:
>
> >I've been thinking of an alternative solution that sounds very similar
> >to this. The idea is to output the CREATE TABLESPACE commands inside
> >pg_dump....
> >...
> >1) tablespace already exists
> >to fail, but since we no longer stop on error during restore,
>
> <soapbox>
> A fact I positively loath! Relying on the 'bluder-on-regardless' approach
> is not something I'd like to enshrine.
> </soapbox>
>
>
> >2) if you have to restore on a machine with a different disk layout,
> >give pg_restore a --override-tablespace command, which would substitute
> >pg_default tablespace into the creation command of any tablespaces that
> >get passed in. The bonus is that we would only have to parse on one
> >specific command rather than worry about parsing several different
> >commands. ...
>
> I'm still inclined to avoid any parsing if at all possible. We should hit
> the code that generates the definitions (90% in pg_dump) and turn the
> definitions into more intelligent templates.
>
>
>
> >... I was thinking that you could
> >create two logical tablespaces on the same physical directory.
>
> This is basically the virtual/fake tablespace idea.
>
>
> Sometimes I think it is worth stepping back from a problem and ask what
> would we do if we had a clean slate, then use that to inform our current
> set of decisions. I'd be very interested in other people's ideas, but my
> thinking is:
>
> - we might not have a tablespace clause inside object definitions; we could
> add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical
> now; but worth bearing in mind as an approach for future non-standard syntax.
> - we'd have a full set of ALTER xxx SET TABLESPACE commands which moved
> relevant data etc. We have most of them.
> - pg_dump would issue the alter commands after creating the object; OK, it
> moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has
> previously been discounted as a solution.
>
> *If* you accept this as a good approach in an ideal world, then I think we
> need to ask ourselves if we should implement the remaining ALTER commands
> in 8.0 and be done with it.
>
> The other solutions: magic-tablespace-var, virtual-tablespaces...all seem
> to add clunky functionality that will only be used in pg_dump. If we're
> going to add something, I'd prefer not to add clutter.
>
>
>
>
> ----------------------------------------------------------------
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 03 5330 3172 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp.mit.edu:11371 |/
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073