Should pg_dumpall be using the "SET default_tablespace = foo" method as well? --------------------------------------------------------------------------- Florian G. Pflug wrote: > Chander Ganesan wrote: > > 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; > > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
