Re: pg_dump -s dumps data?! - Mailing list pgsql-general

On Monday, January 30, 2012 5:08:41 am hubert depesz lubaczewski wrote:
> On Sat, Jan 28, 2012 at 01:10:39PM -0500, Tom Lane wrote:
> > hubert depesz lubaczewski <depesz@depesz.com> writes:
> > > is it by design that tables listed there (in extconfig column of
> > > pg_extension) will be dumped with data, even for pg_dump --schema?
> >
> > Um, yes.  Read the manual.
> > http://www.postgresql.org/docs/9.1/static/extend-extensions.html#AEN51966
>
> Yes, but:
>
> If you create table using extensions, and the you insert data to it
> - this data will *not* be dumped using pg_dump.
> If you mark the table with pg_extension_config_dump() - the data fro the
> table will be dumped *always* - even for -s dump of another table.
> I.e. pg_dump -s -t a will dump data of table b!

Well this is a different problem report from your original post:) In your
original report you where asking why data was dumped in a complete database
pg_dump -s

>
> So long story short - with currently released versions it is either:
> - you will never get data for given table in dumps
> or
> - you will get data for this table in dump, always, even for schema-only
> dumps of unrelated tables.

Well that is spelled out as such in the docs, except for the part about dumping
data from an unrelated  table.

Short version, it is up to the extension to take care of table creation.

Long version:

"Some extensions include configuration tables, which contain data that might be
added or changed by the user after installation of the extension. Ordinarily, if
a table is part of an extension, neither the table's definition nor its content
will be dumped by pg_dump. But that behavior is undesirable for a configuration
table; any data changes made by the user need to be included in dumps, or the
extension will behave differently after a dump and reload.

To solve this problem, an extension's script file can mark a table it has created
as a configuration table, which will cause pg_dump to include the table's
contents (not its definition) in dumps. To do that, call the function
pg_extension_config_dump(regclass, text) after creating the table..."

>
> Now.
>
> I have since made some tests, and it looks like the dumping thing is
> fixed in 9.2devel from git HEAD, which seems to suggest that it will
> work sanely in 9.1.3 - so the point is kind of moot.
>
> But the fact that is the newest released Pg provides mechanism that
> completely breaks pg_dump functionality.

Breaks certain cases when using pg_dump -s.  Some of what you highlight above is
designed behavior. What is happening is covered by my second rule of life 'Easy
is difficult'. In this case it is the desire for a built in 'packaging' system
that makes extending Postgres easier for the end user.  To get that leads to
more complexity in the backend and a new learning curve for those that have to
deal with it.

>
> Best regards,
>
> depesz

--
Adrian Klaver
adrian.klaver@gmail.com