Re: Extension table data

Just found something else rather disturbing. If you try to exclude the
schema that the extension tables are in, their data is still output.
Explicitly naming other schemas doesn't seem to dump the extension
data. So the only way to avoid getting the extension data in a
schema-only dump is to explicitly name all schemas but the one your
extension is in, which I think is another bug you had actually fixed
for 9.1.3 where extension data was always being dumped.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
443.325.1357 x251

On Sat, Jun 9, 2012 at 2:56 AM, Keith Fiske <keith(at)omniti(dot)com> wrote:
> With the current design, I understand what you're saying now.  Just
> doing some more testing, I was able to do a pg_dump -Fc -s for the
> entire database and looking through the resulting object list with
> pg_restore -l I'm actually seeing the extension table data included in
> the dump file. Doing a restore on the schema I put the extension in, I
> see the COPY commands to restore the data. I think this is a serious
> shortcoming, and a confusing state of affairs. The only way to get
> this data out seems to be to do a full database dump and the
> schema-only option to pg_dump is outputting data.
>
> Looking at the docs, I think the extension authors may have only had
> configuration data in mind for extension tables. I don't see any
> reason why we shouldn't be able to put any sort of table in our
> extensions, some having actual data, not just config. That's actually
> what I'm doing with my pg_jobmon extension, which could potentially
> have millions of rows over time. Not having the tables included in the
> extension definition would just make setting the whole thing up more
> difficult and error prone.
>
> If extensions really are going to be self contained like this as far
> as dumps, perhaps another option to pg_dump is needed, and have the
> schema-only or data-only options be honored in that case as well.
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> 443.325.1357 x251
>
>
> On Fri, Jun 8, 2012 at 11:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Keith Fiske <keith(at)omniti(dot)com> writes:
>>> I've read the documentation for extensions and how their data is not
>>> normally dumped by pg_dump and how to configure the table so it should
>>> dump its data
>>> http://www.postgresql.org/docs/9.1/static/extend-extensions.html
>>> However, after setting this option for the tables, the data is not
>>> being dumped when I do a pg_dump of either individual tables or the
>>> schema I've installed the extension to.
>>
>> IIRC, the design intention is that such a table's data would be dumped
>> when (and only when) the extension is dumped.  That is, I'd expect to
>> see a "CREATE EXTENSION foo;" and then data for the extension's tables.
>> The partial-dump scenarios you mention wouldn't dump extensions, hence
>> not extension table data either.
>>
>> Whether this design is a good one is still under debate, but I think
>> pg_dump is operating as designed here ...
>>
>>                        regards, tom lane