Re: Open Items

Gavin Sherry wrote:
> On Sun, 17 Oct 2004, Tom Lane wrote:
>
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(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