pg_dump design problem (bug??) - Mailing list pgsql-general

Postgresql 8.1.3

Hi,

I'm wondering if there's a problem with pg_dump --create,
or if I'm just missing something.
It does not seem to restore things like:

ALTER DATABASE foo SET "DateStyle" TO European;

Shouldn't the database that is re-created be like
the database that is being dumped?

For our purposes we do a pg_dumpall --globals-only
and then pg_dumps of each of our databases.  We like
this because we can restore blobs this way, get a
nice compressed and flexable --format=c, and are able
to restore individual databases.  But there is clearly
a problem because we lose the database meta-information
like datestyle, timezones and all that other per-db
SET stuff.  It seems the only way to get that is with
a pg_dumpall, and then it's not per-database.

What should we do to work around this problem?

Should pg_dump/pg_restore have arguments like:

--dbmeta  (the default when --create is used)
    Dumps the database's "SET" options.

--no-dbmeta (the default when --create is not used)
    Does not dump the database's "SET" options.

--dbowner (the default when --create is used)
    Dumps the database's owner.

--no-dbowner (the default when --create is not used)
    Does not dump the database's owner.


Hummm.... for complete control consider the following:

Or maybe pg_dump/pg_restore should augment/replace
--data-only --schema-only --create with:

--content=ctype[, ...]

where

ctype=db|dbowner|meta|schema|schemaowner|table|tableowner|data

db          create the database
dbowner     set the database owner as in the dumped db
meta        set the database SETs as in the dumped db
schema      create the schema (not data definitions/table structure)
             as in the dumped db
schemaowner set the schema owner as in the dumped db
table       create the table(s) as in the dumped db
tableowner  set the table owners as in the dumped db
data        load the data as in the dumped db

I'd also want to add functions, triggers, views and the other sorts
of things that go into databases to the above list, but that's
enough for now.

Thanks for listening.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings