On Mon, Mar 15, 2021 at 11:37:02AM +0100, Guillaume Lelarge wrote: > Anyways. Yeah, I know we're near feature freeze. This feature would be nice > to have, but I don't feel strongly about it. I think this feature is > currently lacking in PostgreSQL but I don't much care if it makes it to 14 > or any future release. If you have time to work on the pg_dump test suite > and are interested, then sure, go ahead, I'm fine with this. Otherwise I'll > do it in a few weeks and if it means it'll land in v15, then be it. That's > not an issue in itself.
Okay. So I have looked at that stuff in details, and after fixing all the issues reported upthread in the code, docs and tests I am finishing with the attached. The tests have been moved out of src/bin/pg_dump/ to src/test/modules/test_pg_dump/, and include both positive and negative tests (used the trick with plpgsql for the latter to avoid the dump of the extension test_pg_dump or any data related to it). -- Michael
From ca1388a4c4441f15c02d332b8a9996408a90662c Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Tue, 30 Mar 2021 12:01:13 +0900
Subject: [PATCH] Add support for --extension in pg_dump
---
src/bin/pg_dump/pg_dump.c | 93 +++++++++++++++++++--
src/test/modules/test_pg_dump/t/001_base.pl | 53 ++++++++----
doc/src/sgml/ref/pg_dump.sgml | 43 ++++++++--
3 files changed, 161 insertions(+), 28 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index da6cc054b0..382d33f2c6 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);
@@ -335,6 +342,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'},
@@ -426,7 +434,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)
@@ -455,6 +463,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;
@@ -834,6 +847,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
@@ -1025,6 +1048,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"));
@@ -1367,6 +1391,53 @@ 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.
@@ -1793,8 +1864,9 @@ selectDumpableAccessMethod(AccessMethodInfo *method, Archive *fout)
* Built-in extensions should be skipped except for checking ACLs, since we
* assume those will already be installed in the target database. We identify
* such extensions by their having OIDs in the range reserved for initdb.
- * We dump all user-added extensions by default, or none of them if
- * include_everything is false (i.e., a --schema or --table switch was given).
+ * We dump all user-added extensions by default. No extensions are dumped
+ * if include_everything is false (i.e., a --schema or --table switch was
+ * given), except if --extension specifies a list of extensions to dump.
*/
static void
selectDumpableExtension(ExtensionInfo *extinfo, DumpOptions *dopt)
@@ -1807,9 +1879,18 @@ 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
- extinfo->dobj.dump = extinfo->dobj.dump_contains =
- dopt->include_everything ? DUMP_COMPONENT_ALL :
- DUMP_COMPONENT_NONE;
+ {
+ /* check if there is a list of extensions to dump */
+ if (extension_include_oids.head != NULL)
+ extinfo->dobj.dump = extinfo->dobj.dump_contains =
+ 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 : DUMP_COMPONENT_NONE;
+ }
}
/*
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index 501aff0920..7c053c4e49 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -194,6 +194,20 @@ my %pgdump_runs = (
'pg_dump', '--no-sync', "--file=$tempdir/section_post_data.sql",
'--section=post-data', 'postgres',
],
+ },
+ with_extension => {
+ dump_cmd => [
+ 'pg_dump', '--no-sync', "--file=$tempdir/with_extension.sql",
+ '--extension=test_pg_dump', '--no-sync', 'postgres',
+ ],
+ },
+
+ # plgsql in the list blocks the dump of extension test_pg_dump
+ without_extension => {
+ dump_cmd => [
+ 'pg_dump', '--no-sync', "--file=$tempdir/without_extension.sql",
+ '--extension=plpgsql', '--no-sync', 'postgres',
+ ],
},);
###############################################################
@@ -228,14 +242,16 @@ my %pgdump_runs = (
# Tests which are considered 'full' dumps by pg_dump, but there
# are flags used to exclude specific items (ACLs, blobs, etc).
my %full_runs = (
- binary_upgrade => 1,
- clean => 1,
- clean_if_exists => 1,
- createdb => 1,
- defaults => 1,
- exclude_table => 1,
- no_privs => 1,
- no_owner => 1,);
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_table => 1,
+ no_privs => 1,
+ no_owner => 1,
+ with_extension => 1,
+ without_extension => 1);
my %tests = (
'ALTER EXTENSION test_pg_dump' => {
@@ -261,7 +277,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { binary_upgrade => 1, },
+ unlike => { binary_upgrade => 1, without_extension => 1 },
},
'CREATE ROLE regress_dump_test_role' => {
@@ -320,6 +336,7 @@ my %tests = (
section_data => 1,
extension_schema => 1,
},
+ unlike => { without_extension => 1, },
},
'CREATE TABLE regress_pg_dump_table' => {
@@ -343,8 +360,9 @@ my %tests = (
extension_schema => 1,
},
unlike => {
- binary_upgrade => 1,
- exclude_table => 1,
+ binary_upgrade => 1,
+ exclude_table => 1,
+ without_extension => 1,
},
},
@@ -367,7 +385,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { no_privs => 1, },
+ unlike => { no_privs => 1, without_extension => 1, },
},
'REVOKE GRANT OPTION FOR UPDATE ON SEQUENCE wgo_then_regular' => {
@@ -384,7 +402,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { no_privs => 1, },
+ unlike => { no_privs => 1, without_extension => 1, },
},
'CREATE ACCESS METHOD regress_test_am' => {
@@ -404,6 +422,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
+ unlike => { without_extension => 1, },
},
'GRANT SELECT regress_pg_dump_table_added pre-ALTER EXTENSION' => {
@@ -428,7 +447,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { no_privs => 1, },
+ unlike => { no_privs => 1, without_extension => 1, },
},
'GRANT SELECT ON TABLE regress_pg_dump_table' => {
@@ -462,7 +481,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { no_privs => 1, },
+ unlike => { no_privs => 1, without_extension => 1 },
},
'GRANT USAGE ON regress_pg_dump_table_col1_seq TO regress_dump_test_role'
@@ -478,7 +497,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { no_privs => 1, },
+ unlike => { no_privs => 1, without_extension => 1, },
},
'GRANT USAGE ON regress_pg_dump_seq TO regress_dump_test_role' => {
@@ -500,7 +519,7 @@ my %tests = (
schema_only => 1,
section_pre_data => 1,
},
- unlike => { no_privs => 1, },
+ unlike => { no_privs => 1, without_extension => 1, },
},
# Objects included in extension part of a schema created by this extension */
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 989b8e2381..529b167c96 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 extensions 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>
@@ -1079,11 +1111,12 @@ PostgreSQL documentation
<term><option>--strict-names</option></term>
<listitem>
<para>
- Require that each schema
- (<option>-n</option>/<option>--schema</option>) and table
- (<option>-t</option>/<option>--table</option>) qualifier match at
- least one schema/table in the database to be dumped. Note that if
- none of the schema/table qualifiers find
+ Require that each
+ extension (<option>-e</option>/<option>--extension</option>),
+ schema (<option>-n</option>/<option>--schema</option>) and
+ table (<option>-t</option>/<option>--table</option>) qualifier
+ match at least one extension/schema/table in the database to be dumped.
+ Note that if none of the extension/schema/table qualifiers find
matches, <application>pg_dump</application> will generate an error
even without <option>--strict-names</option>.
</para>
--
2.31.0
signature.asc
Description: PGP signature
