Skip site navigation (1) Skip section navigation (2)

Re: BUG #6176: pg_dump dumps pg_catalog tables

From: Chander Ganesan <chander(at)otg-nc(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6176: pg_dump dumps pg_catalog tables
Date: 2011-08-25 15:09:30
Message-ID: 4E5665AA.7080900@otg-nc.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 8/25/11 10:52 AM, Tom Lane wrote:
> "Chander Ganesan"<chander(at)otg-nc(dot)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

In response to

pgsql-bugs by date

Next:From: Robert HaasDate: 2011-08-25 16:11:35
Subject: Re: BUG #6170: hot standby wedging on full-WAL disk
Previous:From: Tom LaneDate: 2011-08-25 14:52:03
Subject: Re: BUG #6176: pg_dump dumps pg_catalog tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group