Re: Extension table data

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