> > On 28 Jun 2019, at 19:55, Luis Carril <luis.car...@swarm64.com> wrote:
> > What about providing a list of FDW servers instead of an all or nothing
> > option? In that way the user really has to do a conscious decision to dump
> > the content of the foreign tables for > > a specific server, this would
> > allow distinction if multiple FDW are being used in the same DB.
> I think this is a good option, the normal exclusion rules can then still apply
> in case not everything from a specific server is of interest.
Hi, here is a new patch to dump the data of foreign tables using pg_dump.
This time the user specifies for which foreign servers the data will be dumped,
which helps in case of having a mix of writeable and non-writeable fdw in the
database.
It would be nice to emit an error if the fdw is read-only, but that information
is not available in the catalog.
Cheers
Luis M Carril
From d24cbf4ad0852b079d0e16103486873ab6bb8b69 Mon Sep 17 00:00:00 2001
From: Luis Carril <luis.car...@swarm64.com>
Date: Fri, 28 Jun 2019 16:05:43 +0200
Subject: [PATCH] Support foreign data in pg_dump
---
src/bin/pg_dump/pg_dump.c | 107 +++++++++++++++++++++++++++++++++++---
src/bin/pg_dump/pg_dump.h | 1 +
2 files changed, 102 insertions(+), 6 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 806fc78f04..ceff6a1744 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -123,6 +123,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};
char g_opaque_type[10]; /* name for the opaque type */
@@ -159,6 +161,10 @@ 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,
+ bool strict_names);
static void expand_table_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids,
@@ -391,6 +397,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}
};
@@ -607,6 +614,10 @@ 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);
@@ -815,6 +826,15 @@ main(int argc, char **argv)
&tabledata_exclude_oids,
false);
+ if (foreign_servers_include_patterns.head != NULL)
+ {
+ expand_foreign_server_name_patterns(fout, &foreign_servers_include_patterns,
+ &foreign_servers_include_oids,
+ strict_names);
+ if (foreign_servers_include_oids.head == NULL)
+ fatal("no matching foreign servers were found");
+ }
+
/* non-matching exclusion patterns aren't an error */
/*
@@ -1038,6 +1058,9 @@ help(const char *progname)
printf(_(" --use-set-session-authorization\n"
" use SET SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" --include-foreign-data=SERVER\n"
+ " include data of foreign tables with the named\n"
+ " foreign servers in dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -d, --dbname=DBNAME database to dump\n"));
@@ -1336,6 +1359,54 @@ 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,
+ 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)
+ {
+ 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 (strict_names && 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()
@@ -1812,7 +1883,7 @@ dumpTableData_copy(Archive *fout, void *dcontext)
*/
column_list = fmtCopyColumnList(tbinfo, clistBuf);
- if (tdinfo->filtercond)
+ if (tdinfo->filtercond || tbinfo->relkind == RELKIND_FOREIGN_TABLE)
{
/* Note: this syntax is only supported in 8.2 and up */
appendPQExpBufferStr(q, "COPY (SELECT ");
@@ -1824,9 +1895,11 @@ dumpTableData_copy(Archive *fout, void *dcontext)
}
else
appendPQExpBufferStr(q, "* ");
- appendPQExpBuffer(q, "FROM %s %s) TO stdout;",
- fmtQualifiedDumpable(tbinfo),
- tdinfo->filtercond);
+
+ appendPQExpBuffer(q, "FROM %s", fmtQualifiedDumpable(tbinfo));
+ if (tdinfo->filtercond)
+ appendPQExpBuffer(q, " %s", tdinfo->filtercond);
+ appendPQExpBuffer(q, ") TO stdout;");
}
else
{
@@ -2342,8 +2415,10 @@ 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) if not requested explicitly */
+ if (tbinfo->relkind == RELKIND_FOREIGN_TABLE &&
+ (foreign_servers_include_oids.head == NULL ||
+ !simple_oid_list_member(&foreign_servers_include_oids, tbinfo->serveroid)))
return;
/* Skip partitioned tables (data in partitions) */
if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
@@ -6643,6 +6718,26 @@ 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));
+ if (tblinfo[i].relkind == RELKIND_FOREIGN_TABLE)
+ {
+ PQExpBuffer query_server = createPQExpBuffer();
+ PGresult *res_server;
+
+ /* retrieve the oid of the foreign server*/
+ appendPQExpBuffer(query_server,
+ "SELECT fs.oid "
+ "FROM pg_catalog.pg_foreign_table ft "
+ "JOIN pg_catalog.pg_foreign_server fs "
+ "ON (fs.oid = ft.ftserver) "
+ "WHERE ft.ftrelid = '%u'",
+ tblinfo[i].dobj.catId.oid);
+
+ res_server = ExecuteSqlQueryForSingleRow(fout, query_server->data);
+ tblinfo[i].serveroid = atooid(PQgetvalue(res_server, 0, 0));
+ PQclear(res_server);
+ destroyPQExpBuffer(query_server);
+ }
+
/*
* 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 c3c2ea1473..0bf3aab0ad 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -326,6 +326,7 @@ typedef struct _tableInfo
char *partbound; /* partition bound definition */
bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */
char *amname; /* relation access method */
+ Oid serveroid; /* foreign server oid */
/*
* Stuff computed only for dumpable tables.
--
2.20.1
