On Saturday, January 28, 2012 1:29:22 am hubert depesz lubaczewski wrote: > On Fri, Jan 27, 2012 at 03:11:32PM -0800, Adrian Klaver wrote: > > Not sure that it makes a difference, but on the chance it does, what > > are the options and are they the same for all machines? > > $ pg_config --configure > '--prefix=/opt/pgbrew/9.1.2' '--with-pgport=5910' '--enable-debug' > '--with-perl' '--enable-integer-datetimes' '--enable-thread-safety' > '--without-krb5' '--without-pam' '--without-bonjour' '--with-openssl' > '--with-readline' '--with-libxml' '--with-zlib' > > and yes - the same on all machines. > > but the databases are not the same - so i'm more inclined to think that > it's something wrong (or mistaken) on the db level. > > > I am not going to much help here, as I am still learning the > > extension mechanism. For the sake of others that might have a clue, > > what are the extensions involved? > > Also, are all the tables having their data dumped or only those that > > relate to extensions? > > this is custom extension - basically it's application database loaded as > extension. > > aside from normal pg_catalog and information_schema, this database has > two schemata: > o > and > f > (names changed to protect the guilty). > o schema has 1 table. > f schema has 7 tables. > > pg_dump -s dumps *data* for 6 tables from f schema. not all of them?! > > \dx+ fextension > shows all 7 tables from f schema. > *but* > select * from pg_extension ; > in extconfig column lists 6 oids. > > and these are oids of tables that got dumped with data?! > > is it by design that tables listed there (in extconfig column of > pg_extension) will be dumped with data, even for pg_dump --schema? > (i didn't make the extension, and up to yesterday I wasn't aware that > they used extensions to load schema to these databases). If I am following correctly then yes. I am getting on thin ice here as I am still learning this but from here: http://www.postgresql.org/docs/9.1/interactive/extend-extensions.html " 35.15.3. Extension Configuration Tables 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, for example CREATE TABLE my_config (key text, value text); SELECT pg_catalog.pg_extension_config_dump('my_config', ''); Any number of tables can be marked this way. When the second argument of pg_extension_config_dump is an empty string, the entire contents of the table are dumped by pg_dump. This is usually only correct if the table is initially empty as created by the extension script. If there is a mixture of initial data and user-provided data in the table, the second argument of pg_extension_config_dump provides a WHERE condition that selects the data to be dumped. For example, you might do CREATE TABLE my_config (key text, value text, standard_entry boolean); SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry'); and then make sure that standard_entry is true only in the rows created by the extension's script. More complicated situations, such as initially-provided rows that might be modified by users, can be handled by creating triggers on the configuration table to ensure that modified rows are marked correctly. " and from here: http://www.postgresql.org/docs/9.1/interactive/catalog-pg-extension.html " extconfig oid[] pg_class.oid Array of regclass OIDs for the extension's configuration table(s), or NULL if none " What is not explicitly stated is whether any of the above is supposed to respect the -s switch. From the evidence it is not and I can understand that behavior. If an extension has config tables and needs that info to load then it should be carried along. > > Best regards, > > depesz -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
