Re: Omitting tablespace creation from pg_dumpall... - Mailing list pgsql-general

Should pg_dumpall be using the "SET default_tablespace = foo" method as
well?

---------------------------------------------------------------------------

Florian G. Pflug wrote:
> Chander Ganesan wrote:
> > Tom Lane wrote:
> >> Chander Ganesan <chander@otg-nc.com> writes:
> >>
> >>> I'd like to suggest that a feature be added to pg_dumpall to remove
> >>> tablespace definitions/creation from the output.  While the inclusion
> >>> is important for backups - it's equally painful when attempting to
> >>> migrate data from a development to production database.  Since
> >>> PostgreSQL won't create the directory that will contain the
> >>> tablespace, the tablespace creation will fail.  Following that, any
> >>> objects that are to be created in that tablespace will fail (since
> >>> the tablespace doesn't exist).
> >>
> >> If the above statements were actually true, it'd be a problem, but they
> >> are not true.  The dump only contains "SET default_tablespace = foo"
> >> commands, which may themselves fail, but they won't prevent subsequent
> >> CREATE TABLE commands from succeeding.
> >>
> >>
> > With PostgreSQL 8.1.4, if I do the following:
> >
> > create tablespace test location '/srv/tblspc';
> > create database test with tablespace = test;
> >
> > The pg_dumpall result will contain:
> > *****
> > CREATE TABLESPACE test OWNER postgres LOCATION '/srv/tblspc';
> > CREATE DATABASE test WITH TEMPLATE=template0 OWNER=postgres
> > ENCODING='utf8' TABLESPACE=test;
>
> Hm.. I guess pg_dumpall is meant to create a identical clone of a
> postgres "cluster" (Note that the term cluster refers to one
> postgres-instance serving multiple databases, and _not_ to a cluster
> in the high-availability sense). For moving a single database from one
> machine to another, pg_dump might suit you more. With pg_dump, you
> normally create the "new" database manually, and _afterwards_ restore
> your dump into this database.
>
> I'd say that pg_dumpall not supporting restoring into a different
> tablespace is compareable to not supporting database renaming. Think
> of pg_dumpall as equivalent to copying the data directory - only that
> it works while the database is online, and supports differing
> architectures on source and destination machine.
>
> greetings, Florian Pflug
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +