Re: Omitting tablespace creation from pg_dumpall...

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;

*****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
>