Add support for --extension in pg_dump

When specified, only extensions matching the given pattern are included
in dumps.  Similarly to --table and --schema, when --strict-names is
used,  a perfect match is required.  Also, like the two other options,
this new option offers no guarantee that dependent objects have been
dumped, so a restore may fail on a clean database.

Tests are added in test_pg_dump/, checking after a set of positive and
negative cases, with or without an extension's contents added to the
dump generated.

Author: Guillaume Lelarge
Reviewed-by: David Fetter, Tom Lane, Michael Paquier, Asif Rehman,
Julien Rouhaud
Discussion: https://postgr.es/m/CAECtzeXOt4cnMU5+XMZzxBPJ_wu76pNy6HZKPRBL-j7yj1E4+g@mail.gmail.com

2 files changed, 123 insertions, 23 deletions

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b72adcfbb8d..25717ce0e68 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"));
@@ -1368,6 +1392,53 @@ expand_schema_name_patterns(Archive *fout,
 }
 
 /*
+ * 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 501aff09201..7c053c4e49d 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 */