Re: Omitting tablespace creation from pg_dumpall...

Chander Ganesan wrote:
> Tom Lane wrote:
>> Chander Ganesan <chander(at)otg-nc(dot)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