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 solutiono
Creatingthe 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 restoreso  Moving the indexes can't be dont easily after they are   created
becausethey are not zero-length fileso  The soft-failure GUC option for non-existant tablespaces   is a hack just for
useby 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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073