Search Postgresql Archives

Re: Omitting tablespace creation from pg_dumpall...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
I understand why it's doing what it's doing - and I'm not disputing the usefulness of it. I just think it might be good to have a flag that allows the omission of the alternate tablespace usage (or set the default instead of including it in the create db statement), since I can see how the failures might become problematic in some environments.

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux