FAQ
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@otg-nc.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


---------------------------(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. +

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 5 of 7 | next ›
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 16, '06 at 3:13a
activeJun 16, '06 at 9:42p
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2017 Grokbase