BUG #6176: pg_dump dumps pg_catalog tables

"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.)

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

regards, tom lane

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