Re: Open Items

Gavin Sherry wrote:
> On Sun, 17 Oct 2004, Tom Lane wrote:
> 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >     o remove non-portable TABLESPACE clause from CREATE TABLE and
> > >       use a new default_tablespace SET variable
> >
> > I'm coming around to the conclusion that this is simply a bad idea.
> 
> I feel the same way for more or less the reasons you outline.
> 
> > What we might want to do is invent a --notablespace option for pg_dump,
> > comparable to --noowner, to let someone make a dump that contains no
> > TABLESPACE clauses.
> 
> That's a useful feature but I'm not sure it solves the problem people
> originally put (to me at least). User has data in a tablespace on a
> seperate device. The device crashes fatally and the user needs to restore
> the database. All the user's dumps contain tablespace clauses because the
> user did not anticipate the device dying. This, I think, is why people
> wanted to either ignore tablespace clauses, have an override or something
> else.

See my posting about using 'explicit_tablespace' and having it issue a
warning if it doesn't exist during create.

> I still think, however, that a workable solution is to bring up a new
> system, create the tablespaces on some online partition, and pg_restore
> the dump. pg_dump does not dump CREATE TABLESPACE so we wont encounter
> problems there.

pg_dumpall does dump CREATE TABLESPACE.  It depends if he is using
pg_dump on individual databases or pg_dumpall.  If he is using pg_dump
he is also pg_dumpall --globals-only and the global restore is where he
can made the adjustments.  Of course, the 'explicit_tablespace' works
with pg_dumpall too because everything destined for a non-existant
tablespace goes into the default for that object.

--  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