Re: [GENERAL] Omitting tablespace creation from pg_dumpall...

[Chander Ganesan]

Tom Lane wrote: Chander Ganesan <[EMAIL PROTECTED]> 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