Re: Omitting tablespace creation from pg_dumpall...

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