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