Hi,
Le sam. 23 mai 2020 à 14:53, Guillaume Lelarge <guilla...@lelarge.info> a
écrit :
> Le mer. 20 mai 2020 à 16:39, Tom Lane <t...@sss.pgh.pa.us> a écrit :
>
>> Guillaume Lelarge <guilla...@lelarge.info> writes:
>> > Le mer. 20 mai 2020 à 11:26, Daniel Gustafsson <dan...@yesql.se> a
>> écrit :
>> >> The question is what --extensions should do: only dump any
>> >> extensions that objects in the schema depend on; require a pattern and
>> only
>> >> dump matching extensions; dump all extensions (probably not) or
>> something
>> >> else?
>>
>> > Actually, "dump all extensions" (#3) would make sense to me, and has my
>> > vote.
>>
>> I think that makes no sense at all. By definition, a dump that's been
>> restricted with --schema, --table, or any similar switch is incomplete
>> and may not restore on its own. Typical examples include foreign key
>> references to tables in other schemas, views using functions in other
>> schemas, etc etc. I see no reason for extension dependencies to be
>> treated differently from those cases.
>>
>>
> Agreed.
>
> In any use of selective dump, it's the user's job to select a set of
>> objects that she wants dumped (or restored). Trying to second-guess that
>> is mostly going to make the feature less usable for power-user cases.
>>
>>
> Agreed, though right now he has no way to do this for extensions.
>
> As a counterexample, what if you want the dump to be restorable on a
>> system that doesn't have all of the extensions available on the source?
>> You carefully pick out the tables that you need, which don't require the
>> unavailable extensions ... and then pg_dump decides you don't know what
>> you're doing and includes all the problematic extensions anyway.
>>
>>
> That's true.
>
> I could get behind an "--extensions=PATTERN" switch to allow selective
>> addition of extensions to a selective dump, but I don't want to see us
>> overruling the user's choices about what to dump.
>>
>>
> With all your comments, I can only agree to your views. I'll try to work
> on this anytime soon.
>
>
"Anytime soon" was a long long time ago, and I eventually completely forgot
this, sorry. As nobody worked on it yet, I took a shot at it. See attached
patch.
I don't know if I should add this right away in the commit fest app. If
yes, I guess it should go on the next commit fest (2021-03), right?
--
Guillaume.
commit 97c41b05b4aa20f4187aedbed79d67874d2cbbbd
Author: Guillaume Lelarge <guilla...@lelarge.info>
Date: Mon Jan 25 14:25:53 2021 +0100
Add a --extension flag to dump specific extensions
Version 1.
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index bcbb7a25fb..95d45fabfb 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -215,6 +215,38 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-e <replaceable class="parameter">pattern</replaceable></option></term>
+ <term><option>--extension=<replaceable class="parameter">pattern</replaceable></option></term>
+ <listitem>
+ <para>
+ Dump only extensions matching <replaceable
+ class="parameter">pattern</replaceable>. When this option is not
+ specified, all non-system extensions in the target database will be
+ dumped. Multiple schemas can be selected by writing multiple
+ <option>-e</option> switches. The <replaceable
+ class="parameter">pattern</replaceable> parameter is interpreted as a
+ pattern according to the same rules used by
+ <application>psql</application>'s <literal>\d</literal> commands (see
+ <xref linkend="app-psql-patterns"/>), so multiple extensions can also
+ be selected by writing wildcard characters in the pattern. When using
+ wildcards, be careful to quote the pattern if needed to prevent the
+ shell from expanding the wildcards.
+ </para>
+
+ <note>
+ <para>
+ When <option>-e</option> is specified,
+ <application>pg_dump</application> makes no attempt to dump any other
+ database objects that the selected extension(s) might depend upon.
+ Therefore, there is no guarantee that the results of a
+ specific-extension dump can be successfully restored by themselves
+ into a clean database.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-E <replaceable class="parameter">encoding</replaceable></option></term>
<term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term>
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 798d14580e..3c1203e85c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -123,6 +123,9 @@ static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
static SimpleStringList foreign_servers_include_patterns = {NULL, NULL};
static SimpleOidList foreign_servers_include_oids = {NULL, NULL};
+static SimpleStringList extension_include_patterns = {NULL, NULL};
+static SimpleOidList extension_include_oids = {NULL, NULL};
+
static const CatalogId nilCatalogId = {0, 0};
/* override for standard extra_float_digits setting */
@@ -151,6 +154,10 @@ static void expand_schema_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids,
bool strict_names);
+static void expand_extension_name_patterns(Archive *fout,
+ SimpleStringList *patterns,
+ SimpleOidList *oids,
+ bool strict_names);
static void expand_foreign_server_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids);
@@ -334,6 +341,7 @@ main(int argc, char **argv)
{"clean", no_argument, NULL, 'c'},
{"create", no_argument, NULL, 'C'},
{"dbname", required_argument, NULL, 'd'},
+ {"extension", required_argument, NULL, 'e'},
{"file", required_argument, NULL, 'f'},
{"format", required_argument, NULL, 'F'},
{"host", required_argument, NULL, 'h'},
@@ -424,7 +432,7 @@ main(int argc, char **argv)
InitDumpOptions(&dopt);
- while ((c = getopt_long(argc, argv, "abBcCd:E:f:F:h:j:n:N:Op:RsS:t:T:U:vwWxZ:",
+ while ((c = getopt_long(argc, argv, "abBcCd:e:E:f:F:h:j:n:N:Op:RsS:t:T:U:vwWxZ:",
long_options, &optindex)) != -1)
{
switch (c)
@@ -453,6 +461,11 @@ main(int argc, char **argv)
dopt.cparams.dbname = pg_strdup(optarg);
break;
+ case 'e': /* include extension(s) */
+ simple_string_list_append(&extension_include_patterns, optarg);
+ dopt.include_everything = false;
+ break;
+
case 'E': /* Dump encoding */
dumpencoding = pg_strdup(optarg);
break;
@@ -832,6 +845,16 @@ main(int argc, char **argv)
/* non-matching exclusion patterns aren't an error */
+ /* Expand extension selection patterns into OID lists */
+ if (extension_include_patterns.head != NULL)
+ {
+ expand_extension_name_patterns(fout, &extension_include_patterns,
+ &extension_include_oids,
+ strict_names);
+ if (extension_include_oids.head == NULL)
+ fatal("no matching extensions were found");
+ }
+
/*
* Dumping blobs is the default for dumps where an inclusion switch is not
* used (an "include everything" dump). -B can be used to exclude blobs
@@ -1019,6 +1042,7 @@ help(const char *progname)
printf(_(" -B, --no-blobs exclude large objects in dump\n"));
printf(_(" -c, --clean clean (drop) database objects before recreating\n"));
printf(_(" -C, --create include commands to create database in dump\n"));
+ printf(_(" -e, --extension=PATTERN dump the specified extension(s) only\n"));
printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n"));
printf(_(" -n, --schema=PATTERN dump the specified schema(s) only\n"));
printf(_(" -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)\n"));
@@ -1360,6 +1384,54 @@ expand_schema_name_patterns(Archive *fout,
destroyPQExpBuffer(query);
}
+/*
+ * Find the OIDs of all extensions matching the given list of patterns,
+ * and append them to the given OID list.
+ */
+static void
+expand_extension_name_patterns(Archive *fout,
+ SimpleStringList *patterns,
+ SimpleOidList *oids,
+ bool strict_names)
+{
+ PQExpBuffer query;
+ PGresult *res;
+ SimpleStringListCell *cell;
+ int i;
+
+ if (patterns->head == NULL)
+ return; /* nothing to do */
+
+ query = createPQExpBuffer();
+
+ /*
+ * The loop below runs multiple SELECTs might sometimes result in
+ * duplicate entries in the OID list, but we don't care.
+ */
+
+ for (cell = patterns->head; cell; cell = cell->next)
+ {
+ appendPQExpBufferStr(query,
+ "SELECT oid FROM pg_catalog.pg_extension e\n");
+ processSQLNamePattern(GetConnection(fout), query, cell->val, false,
+ false, NULL, "e.extname", NULL, NULL);
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ if (strict_names && PQntuples(res) == 0)
+ fatal("no matching extensions were found for pattern \"%s\"", cell->val);
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ simple_oid_list_append(oids, atooid(PQgetvalue(res, i, 0)));
+ }
+
+ PQclear(res);
+ resetPQExpBuffer(query);
+ }
+
+ destroyPQExpBuffer(query);
+}
+
/*
* Find the OIDs of all foreign servers matching the given list of patterns,
* and append them to the given OID list.
@@ -1799,6 +1871,11 @@ selectDumpableExtension(ExtensionInfo *extinfo, DumpOptions *dopt)
*/
if (extinfo->dobj.catId.oid <= (Oid) g_last_builtin_oid)
extinfo->dobj.dump = extinfo->dobj.dump_contains = DUMP_COMPONENT_ACL;
+ else if (extension_include_oids.head != NULL)
+ extinfo->dobj.dump_contains = extinfo->dobj.dump =
+ simple_oid_list_member(&extension_include_oids,
+ extinfo->dobj.catId.oid) ?
+ DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
else
extinfo->dobj.dump = extinfo->dobj.dump_contains =
dopt->include_everything ? DUMP_COMPONENT_ALL :
