Thread: Omitting tablespace creation from pg_dumpall...

Omitting tablespace creation from pg_dumpall...

From

Chander Ganesan

Date:

16 June 2006, 03:14:00

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

This means that one could potentially have to search and replace every instance of a tablespace name in a dump file...

Perhaps a flag can be added to simply comment out tablespace definitions (both the creation, and their use in CREATE DATABASE, etc. statements). This would allow the statement to be uncommented if needed, and allow DBA's to search for and modify tablespace names as needed...

I apologize if this has already been mentioned/suggested :-)

--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Re: Omitting tablespace creation from pg_dumpall...

From

Tom Lane

Date:

16 June 2006, 03:38:34

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.

            regards, tom lane

Re: Omitting tablespace creation from pg_dumpall...

From

Chander Ganesan

Date:

16 June 2006, 04:21:43

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;

When this is executed on a load, the create database statement will fail with a 'ERROR: tablespace test does not exist'. This error occurs due to the fact that the initial create tablespace statement fails...because the location isn't pre-created.

Perhaps the feature you mention (SET default_tablespace) is a feature that is to be added post PostgreSQL 8.1 ?

The set default_tablespace method definitely sounds like the ideal solution here...although its potentially misleading if a DBA doesn't realize that the tablespace wasn't actually created...

Subsequent create statements inside the database will fail, since the database create will fail.

-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Omitting tablespace creation from pg_dumpall...

From

"Florian G. Pflug"

Date:

16 June 2006, 13:09:52

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

Re: Omitting tablespace creation from pg_dumpall...

From

Bruce Momjian

Date:

16 June 2006, 15:19:14

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

Re: Omitting tablespace creation from pg_dumpall...

From

Chander Ganesan

Date:

16 June 2006, 16:47:00

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
I understand why it's doing what it's doing - and I'm not disputing the
usefulness of it.  I just think it might be good to have a flag that
allows the omission of the alternate tablespace usage (or set the
default instead of including it in the create db statement), since I can
see how the failures might become problematic in some environments.

--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Re: Omitting tablespace creation from pg_dumpall...

From

Tom Lane

Date:

16 June 2006, 21:42:28

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Should pg_dumpall be using the "SET default_tablespace = foo" method as
> well?

That would mean changing the semantics of CREATE DATABASE; currently it
copies the default tablespace from the template database, rather than
looking at default_tablespace.  I'm unsure if that's a good idea or not.
None of the other properties of a database are handled that way.

            regards, tom lane