Re: Omitting tablespace creation from pg_dumpall...

[Chander Ganesan <chander@xxxxxxxxxx>]

Tom Lane wrote:

Chander Ganesan <chander@xxxxxxxxxx> 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