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@xxxxxxxxxxxxx> wrote: > Keith Fiske <keith@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
