Gavin Sherry wrote: > On Sun, 17 Oct 2004, Tom Lane wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
