On 2020-Mar-23, Alvaro Herrera wrote:
> COPY public.ft1 (c1, c2, c3) FROM stdin;
> pg_dump: error: query failed: ERROR: foreign-data wrapper "dummy" has no
> handler
> pg_dump: error: query was: COPY (SELECT c1, c2, c3 FROM public.ft1 ) TO
> stdout;
>
> Maybe what we should do just verify that you do get that error (and no
> other errors).
Done that way. Will be pushing this shortly.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 4c9456992640431c45ed47aec488ac20cae9a4b0 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Fri, 20 Mar 2020 18:42:03 -0300
Subject: [PATCH v9 1/2] pg_dump: Allow dumping data of specific foreign
servers
Author: Luis Carril
Discussion: https://postgr.es/m/lejpr01mb0185483c0079d2f651b16231e7...@lejpr01mb0185.deuprd01.prod.outlook.de
---
doc/src/sgml/ref/pg_dump.sgml | 30 ++++++++++
src/bin/pg_dump/pg_dump.c | 110 ++++++++++++++++++++++++++++++++--
src/bin/pg_dump/pg_dump.h | 1 +
3 files changed, 136 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 13bd320b31..a9bc397165 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -767,6 +767,36 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--include-foreign-data=<replaceable class="parameter">foreignserver</replaceable></option></term>
+ <listitem>
+ <para>
+ Dump the data for any foreign table with a foreign server
+ matching <replaceable class="parameter">foreignserver</replaceable>
+ pattern. Multiple foreign servers can be selected by writing multiple
+ <option>--include-foreign-data</option> switches.
+ Also, the <replaceable class="parameter">foreignserver</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" endterm="app-psql-patterns-title"/>),
+ so multiple foreign servers 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; see
+ <xref linkend="pg-dump-examples" endterm="pg-dump-examples-title"/>.
+ The only exception is that an empty pattern is disallowed.
+ </para>
+
+ <note>
+ <para>
+ When <option>--include-foreign-data</option> is specified,
+ <application>pg_dump</application> does not check that the foreign
+ table is writeable. Therefore, there is no guarantee that the
+ results of a foreign table dump can be successfully restored.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--inserts</option></term>
<listitem>
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 959b36a95c..1849dfe3d7 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -119,6 +119,8 @@ static SimpleStringList table_exclude_patterns = {NULL, NULL};
static SimpleOidList table_exclude_oids = {NULL, NULL};
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+static SimpleStringList foreign_servers_include_patterns = {NULL, NULL};
+static SimpleOidList foreign_servers_include_oids = {NULL, NULL};
/* placeholders for the delimiters for comments */
@@ -153,6 +155,9 @@ static void expand_schema_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids,
bool strict_names);
+static void expand_foreign_server_name_patterns(Archive *fout,
+ SimpleStringList *patterns,
+ SimpleOidList *oids);
static void expand_table_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids,
@@ -385,6 +390,7 @@ main(int argc, char **argv)
{"no-sync", no_argument, NULL, 7},
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
{"rows-per-insert", required_argument, NULL, 10},
+ {"include-foreign-data", required_argument, NULL, 11},
{NULL, 0, NULL, 0}
};
@@ -600,6 +606,11 @@ main(int argc, char **argv)
dopt.dump_inserts = (int) rowsPerInsert;
break;
+ case 11: /* include foreign data */
+ simple_string_list_append(&foreign_servers_include_patterns,
+ optarg);
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -641,6 +652,12 @@ main(int argc, char **argv)
exit_nicely(1);
}
+ if (dopt.schemaOnly && foreign_servers_include_patterns.head != NULL)
+ fatal("options -s/--schema-only and --include-foreign-data cannot be used together");
+
+ if (numWorkers > 1 && foreign_servers_include_patterns.head != NULL)
+ fatal("option --include-foreign-data is not supported with parallel backup");
+
if (dopt.dataOnly && dopt.outputClean)
{
pg_log_error("options -c/--clean and -a/--data-only cannot be used together");
@@ -808,6 +825,9 @@ main(int argc, char **argv)
&tabledata_exclude_oids,
false);
+ expand_foreign_server_name_patterns(fout, &foreign_servers_include_patterns,
+ &foreign_servers_include_oids);
+
/* non-matching exclusion patterns aren't an error */
/*
@@ -1011,6 +1031,9 @@ help(const char *progname)
printf(_(" --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n"));
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
+ printf(_(" --include-foreign-data=PATTERN\n"
+ " include data of foreign tables in\n"
+ " foreign servers matching PATTERN\n"));
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --no-comments do not dump comments\n"));
@@ -1330,6 +1353,51 @@ expand_schema_name_patterns(Archive *fout,
destroyPQExpBuffer(query);
}
+/*
+ * Find the OIDs of all foreign servers matching the given list of patterns,
+ * and append them to the given OID list.
+ */
+static void
+expand_foreign_server_name_patterns(Archive *fout,
+ SimpleStringList *patterns,
+ SimpleOidList *oids)
+{
+ 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)
+ {
+ appendPQExpBuffer(query,
+ "SELECT oid FROM pg_catalog.pg_foreign_server s\n");
+ processSQLNamePattern(GetConnection(fout), query, cell->val, false,
+ false, NULL, "s.srvname", NULL, NULL);
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ if (PQntuples(res) == 0)
+ fatal("no matching foreign servers 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 tables matching the given list of patterns,
* and append them to the given OID list. See also expand_dbname_patterns()
@@ -1775,7 +1843,6 @@ selectDumpableObject(DumpableObject *dobj, Archive *fout)
* - this routine is called by the Archiver when it wants the table
* to be dumped.
*/
-
static int
dumpTableData_copy(Archive *fout, void *dcontext)
{
@@ -1806,7 +1873,12 @@ dumpTableData_copy(Archive *fout, void *dcontext)
*/
column_list = fmtCopyColumnList(tbinfo, clistBuf);
- if (tdinfo->filtercond)
+ /*
+ * Use COPY (SELECT ...) TO when dumping a foreign table's data, and when
+ * a filter condition was specified. For other cases a simple COPY
+ * suffices.
+ */
+ if (tdinfo->filtercond || tbinfo->relkind == RELKIND_FOREIGN_TABLE)
{
/* Note: this syntax is only supported in 8.2 and up */
appendPQExpBufferStr(q, "COPY (SELECT ");
@@ -1818,9 +1890,10 @@ dumpTableData_copy(Archive *fout, void *dcontext)
}
else
appendPQExpBufferStr(q, "* ");
+
appendPQExpBuffer(q, "FROM %s %s) TO stdout;",
fmtQualifiedDumpable(tbinfo),
- tdinfo->filtercond);
+ tdinfo->filtercond ? tdinfo->filtercond : "");
}
else
{
@@ -2336,8 +2409,11 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo)
/* Skip VIEWs (no data to dump) */
if (tbinfo->relkind == RELKIND_VIEW)
return;
- /* Skip FOREIGN TABLEs (no data to dump) */
- if (tbinfo->relkind == RELKIND_FOREIGN_TABLE)
+ /* Skip FOREIGN TABLEs (no data to dump) unless requested explicitly */
+ if (tbinfo->relkind == RELKIND_FOREIGN_TABLE &&
+ (foreign_servers_include_oids.head == NULL ||
+ !simple_oid_list_member(&foreign_servers_include_oids,
+ tbinfo->foreign_server)))
return;
/* Skip partitioned tables (data in partitions) */
if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
@@ -5999,6 +6075,7 @@ getTables(Archive *fout, int *numTables)
int i_toastreloptions;
int i_reloftype;
int i_relpages;
+ int i_foreignserver;
int i_is_identity_sequence;
int i_changed_acl;
int i_partkeydef;
@@ -6095,6 +6172,9 @@ getTables(Archive *fout, int *numTables)
"tc.relminmxid AS tminmxid, "
"c.relpersistence, c.relispopulated, "
"c.relreplident, c.relpages, am.amname, "
+ "CASE WHEN c.relkind = 'f' THEN "
+ "(SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) "
+ "ELSE 0 END AS foreignserver, "
"CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6185,6 +6265,9 @@ getTables(Archive *fout, int *numTables)
"c.relpersistence, c.relispopulated, "
"c.relreplident, c.relpages, "
"NULL AS amname, "
+ "CASE WHEN c.relkind = 'f' THEN "
+ "(SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) "
+ "ELSE 0 END AS foreignserver, "
"CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6235,6 +6318,9 @@ getTables(Archive *fout, int *numTables)
"c.relpersistence, c.relispopulated, "
"c.relreplident, c.relpages, "
"NULL AS amname, "
+ "CASE WHEN c.relkind = 'f' THEN "
+ "(SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) "
+ "ELSE 0 END AS foreignserver, "
"CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6285,6 +6371,9 @@ getTables(Archive *fout, int *numTables)
"c.relpersistence, c.relispopulated, "
"'d' AS relreplident, c.relpages, "
"NULL AS amname, "
+ "CASE WHEN c.relkind = 'f' THEN "
+ "(SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) "
+ "ELSE 0 END AS foreignserver, "
"CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6335,6 +6424,9 @@ getTables(Archive *fout, int *numTables)
"c.relpersistence, 't' as relispopulated, "
"'d' AS relreplident, c.relpages, "
"NULL AS amname, "
+ "CASE WHEN c.relkind = 'f' THEN "
+ "(SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) "
+ "ELSE 0 END AS foreignserver, "
"CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6383,6 +6475,7 @@ getTables(Archive *fout, int *numTables)
"'p' AS relpersistence, 't' as relispopulated, "
"'d' AS relreplident, c.relpages, "
"NULL AS amname, "
+ "NULL AS foreignserver, "
"CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6430,6 +6523,7 @@ getTables(Archive *fout, int *numTables)
"'p' AS relpersistence, 't' as relispopulated, "
"'d' AS relreplident, c.relpages, "
"NULL AS amname, "
+ "NULL AS foreignserver, "
"NULL AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6477,6 +6571,7 @@ getTables(Archive *fout, int *numTables)
"'p' AS relpersistence, 't' as relispopulated, "
"'d' AS relreplident, c.relpages, "
"NULL AS amname, "
+ "NULL AS foreignserver, "
"NULL AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6523,6 +6618,7 @@ getTables(Archive *fout, int *numTables)
"'p' AS relpersistence, 't' as relispopulated, "
"'d' AS relreplident, relpages, "
"NULL AS amname, "
+ "NULL AS foreignserver, "
"NULL AS reloftype, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
@@ -6590,6 +6686,7 @@ getTables(Archive *fout, int *numTables)
i_relispopulated = PQfnumber(res, "relispopulated");
i_relreplident = PQfnumber(res, "relreplident");
i_relpages = PQfnumber(res, "relpages");
+ i_foreignserver = PQfnumber(res, "foreignserver");
i_owning_tab = PQfnumber(res, "owning_tab");
i_owning_col = PQfnumber(res, "owning_col");
i_reltablespace = PQfnumber(res, "reltablespace");
@@ -6714,6 +6811,9 @@ getTables(Archive *fout, int *numTables)
tblinfo[i].ispartition = (strcmp(PQgetvalue(res, i, i_ispartition), "t") == 0);
tblinfo[i].partbound = pg_strdup(PQgetvalue(res, i, i_partbound));
+ /* foreign server */
+ tblinfo[i].foreign_server = atooid(PQgetvalue(res, i, i_foreignserver));
+
/*
* Read-lock target tables to make sure they aren't DROPPED or altered
* in schema before we get around to dumping them.
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e0c6444ef6..3e11166615 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -283,6 +283,7 @@ typedef struct _tableInfo
uint32 toast_minmxid; /* toast table's relminmxid */
int ncheck; /* # of CHECK expressions */
char *reloftype; /* underlying type for typed table */
+ Oid foreign_server; /* foreign server oid, if applicable */
/* these two are set only if table is a sequence owned by a column: */
Oid owning_tab; /* OID of table owning sequence */
int owning_col; /* attr # of column owning sequence */
--
2.20.1
>From b71bd9d64f06220ee4c12a66e96c96497a795370 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Tue, 24 Mar 2020 13:48:20 -0300
Subject: [PATCH v9 2/2] Add tests for the feature
---
src/bin/pg_dump/t/001_basic.pl | 14 +++++++-
src/bin/pg_dump/t/003_pg_dump_with_server.pl | 36 ++++++++++++++++++++
2 files changed, 49 insertions(+), 1 deletion(-)
create mode 100644 src/bin/pg_dump/t/003_pg_dump_with_server.pl
diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl
index 9ca8a8e608..6a78e2064b 100644
--- a/src/bin/pg_dump/t/001_basic.pl
+++ b/src/bin/pg_dump/t/001_basic.pl
@@ -4,7 +4,7 @@ use warnings;
use Config;
use PostgresNode;
use TestLib;
-use Test::More tests => 74;
+use Test::More tests => 80;
my $tempdir = TestLib::tempdir;
my $tempdir_short = TestLib::tempdir_short;
@@ -49,6 +49,18 @@ command_fails_like(
'pg_dump: options -s/--schema-only and -a/--data-only cannot be used together'
);
+command_fails_like(
+ [ 'pg_dump', '-s', '--include-foreign-data', 'xxx' ],
+ qr/\Qpg_dump: error: options -s\/--schema-only and --include-foreign-data cannot be used together\E/,
+ 'pg_dump: options -s/--schema-only and --include-foreign-data cannot be used together'
+);
+
+command_fails_like(
+ [ 'pg_dump', '-j2', '--include-foreign-data', 'xxx' ],
+ qr/\Qpg_dump: error: option --include-foreign-data is not supported with parallel backup\E/,
+ 'pg_dump: option --include-foreign-data is not supported with parallel backup'
+);
+
command_fails_like(
['pg_restore'],
qr{\Qpg_restore: error: one of -d/--dbname and -f/--file must be specified\E},
diff --git a/src/bin/pg_dump/t/003_pg_dump_with_server.pl b/src/bin/pg_dump/t/003_pg_dump_with_server.pl
new file mode 100644
index 0000000000..3573eb2fbf
--- /dev/null
+++ b/src/bin/pg_dump/t/003_pg_dump_with_server.pl
@@ -0,0 +1,36 @@
+use strict;
+use warnings;
+
+use PostgresNode;
+use TestLib;
+use Test::More tests => 3;
+
+my $tempdir = TestLib::tempdir;
+my $tempdir_short = TestLib::tempdir_short;
+
+my $node = get_new_node('main');
+my $port = $node->port;
+
+$node->init;
+$node->start;
+
+#########################################
+# Verify that dumping foreign data includes only foreign tables of
+# matching servers
+
+$node->safe_psql( 'postgres', "CREATE FOREIGN DATA WRAPPER dummy");
+$node->safe_psql( 'postgres', "CREATE SERVER s0 FOREIGN DATA WRAPPER dummy");
+$node->safe_psql( 'postgres', "CREATE SERVER s1 FOREIGN DATA WRAPPER dummy");
+$node->safe_psql( 'postgres', "CREATE SERVER s2 FOREIGN DATA WRAPPER dummy");
+$node->safe_psql( 'postgres', "CREATE FOREIGN TABLE t0 (a int) SERVER s0");
+$node->safe_psql( 'postgres', "CREATE FOREIGN TABLE t1 (a int) SERVER s1");
+my ($cmd, $stdout, $stderr, $result);
+
+command_fails_like(
+ [ "pg_dump", '-p', $port, 'postgres', '--include-foreign-data=s0' ],
+ qr/foreign-data wrapper \"dummy\" has no handler\r?\npg_dump: error: query was:.*t0/,
+ "correctly fails to dump a foreign table from a dummy FDW");
+
+command_ok(
+ [ "pg_dump", '-p', $port, 'postgres', '-a', '--include-foreign-data=s2' ] ,
+ "dump foreign server with no tables");
--
2.20.1
