Re: BUG #6176: pg_dump dumps pg_catalog tables

On 8/25/11 10:52 AM, Tom Lane wrote:
> "Chander Ganesan"<chander@otg-nc.com>  writes:
>> Normally, the pg_dump command ignored the pg_catalog tables when performing
>> a dump.  However, when provided the '--table' argument it fails to ignore
>> the pg_catalog table.
>> For example, suppose I had tables p1-p10 that I wanted to dump, I could use
>> the following command:
>> pg_dump test_db --table 'p*'
>> This command would dump the requested tables, but it would also dump all the
>> tables (in all schemas) that start with 'p*' .  Generally speaking, there
>> are no "excluded schemas" when using the pg_dump command with the '--table'
>> argument.
>> It is my belief that the pg_catalog tables should almost always be ignored
>> (lest restores fail miserably).
> This proposal seems overly simplistic to me: if we did this, it would be
> impossible to use pg_dump to dump a catalog's contents at all.  (I don't
> care whether the resulting script is restorable; sometimes you just need
> to see what's actually in pg_class.)
Hence the "almost always" in my proposal - I agree with you.  I think
the common use case would want to preclude the export of pg_catalog
tables, and most folks reading the documentation would end up getting
confusing output in their dumps...

At the very least, the documentation might include a caveat to warn
users of the side effect...  Especially since the default behavior is to
exclude pg_catalog
> I wonder whether it would be helpful to provide a default setting for
> --exclude-schema that lists pg_catalog, information_schema, etc.
> If we approached it that way, it'd be possible to override the default
> at need.  However, I'm not sure how that switch interacts with wildcard
> --table specs ...
I tried that, at present it seems that the --exclude-schema flag is
ignored when the --table flag is used.  I'd love to see those work
together (i.e., all tables starting with 'p' except those in schema
'old_data'.)  I'd hate to see a command like this result in pg_catalog
being dumped (which might be more backwards-incompatible than my
suggestion below):

pg_dump test_db --exclude-schema old_stuff

How about making those schemas *always* excluded except when specified
in the '--schema' flag (so one could explicity say "include
pg_catalog")  In that case the use case to dump pg_class would be:

pg_dump test_db --schema pg_catalog --table pg_class

Such  flag wouldn't break the existing behavior (which is, by default,
to exclude system schemas)

Chander