Re: BUG #6640: pg_dump does not always dump data of tables marked as editable in extension

The following bug has been logged on the website:

Bug reference: 6640
Logged by: Regina Obe
Email address: lr(at)pcorp(dot)us
PostgreSQL version: 9.1.3
Operating system: Any
Description:

This seems to be only an issue if a user individually chooses to backup a
table marked as editable in an extension, but works fine if full backup is
chosen.

The particular extension in question here is PostGIS.

Please refer to:
http://trac.osgeo.org/postgis/ticket/1815

for details.
In short -- if I do a full backup of a database, a table marked as editable
gets its data backed up as expected, but if I try to backup a schema the
table is in or the table itself, pg_dump does not dump anything.

lr(at)pcorp(dot)us writes:
> This seems to be only an issue if a user individually chooses to backup a
> table marked as editable in an extension, but works fine if full backup is
> chosen.

AFAICT this is a misunderstanding of the intended behavior of extension
configuration tables. The data in them will be dumped when, and only when,
the extension itself is included in the dump. Moreover, if you have a
mixture of extension-provided and user-provided data in the table, as
seems to be the case here, you really need to take measures to keep
those separate so that you can dump only the user-provided entries.
This is not a "fire and forget" type of feature, it requires nontrivial
effort on the part of the extension author to make it useful. Please
see
http://www.postgresql.org/docs/9.1/static/extend-extensions.html#AEN51978

regards, tom lane

Tom,

Okay understood. We had planned to do something along this line of having a
where condition for the extension or putting the custom spatial_ref_sys in a
separate table but hand't decided which way to go. So that will take care of
the handling custom records issue.

So I guess the more major issue is a won't fix on both sides since a user
will not be able to backup custom records without backing up the whole
database. I don't see any option in 9.1 or 9.2 pg_dump to explicitly backup
an extension and even backing up the schema an extension is installed in
doesn't seem to do the trick.

I would have expected backing up the schema an extension is installed in
would backup the extension. Is that expected behavior that it doesn't?

Thanks,
Regina

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Sunday, May 20, 2012 1:28 PM
> To: lr(at)pcorp(dot)us
> Cc: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] BUG #6640: pg_dump does not always dump
> data of tables marked as editable in extension
>
> lr(at)pcorp(dot)us writes:
> > This seems to be only an issue if a user individually chooses to
> > backup a table marked as editable in an extension, but
> works fine if
> > full backup is chosen.
>
> AFAICT this is a misunderstanding of the intended behavior of
> extension configuration tables. The data in them will be
> dumped when, and only when, the extension itself is included
> in the dump. Moreover, if you have a mixture of
> extension-provided and user-provided data in the table, as
> seems to be the case here, you really need to take measures
> to keep those separate so that you can dump only the
> user-provided entries.
> This is not a "fire and forget" type of feature, it requires
> nontrivial effort on the part of the extension author to make
> it useful. Please see
> http://www.postgresql.org/docs/9.1/static/extend-extensions.ht
ml#AEN51978
>
> regards, tom lane
>

On Sun, May 20, 2012 at 1:53 PM, Paragon Corporation <lr(at)pcorp(dot)us> wrote:
> Okay understood.  We had planned to do something along this line of having a
> where condition for the extension or putting the custom spatial_ref_sys in a
> separate table but hand't decided which way to go. So that will take care of
> the handling custom records issue.
>
> So I guess the more major issue is a won't fix on both sides since a user
> will not be able to backup custom records without backing up the whole
> database. I don't see any option in 9.1 or 9.2 pg_dump to explicitly backup
> an extension and even backing up the schema an extension is installed in
> doesn't seem to do the trick.
>
> I would have expected backing up the schema an extension is installed in
> would backup the extension.  Is that expected behavior that it doesn't?

Extensions are not schema objects.

But it seems like maybe we need a pg_dump --extension=XYZ option.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company