Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit23d7680

Browse files
committed
pg_dump: Add a --load-via-partition-root option.
Rushabh Lathia, reviewed and somewhat revised by me. Testing byRajkumar Raghuwanshi.Discussion:http://postgr.es/m/CAGPqQf0C1he087bz9xRBOGZBuESYz9X=Fp8Ca_g+TfHgAff75g@mail.gmail.com
1 parentd2bc501 commit23d7680

File tree

6 files changed

+145
-18
lines changed

6 files changed

+145
-18
lines changed

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -888,6 +888,21 @@ PostgreSQL documentation
888888
</listitem>
889889
</varlistentry>
890890

891+
<varlistentry>
892+
<term><option>--load-via-partition-root</></term>
893+
<listitem>
894+
<para>
895+
When dumping a COPY or INSERT statement for a partitioned table,
896+
target the root of the partitioning hierarchy which contains it rather
897+
than the partition itself. This may be useful when reloading data on
898+
a server where rows do not always fall into the same partitions as
899+
they did on the original server. This could happen, for example, if
900+
the partitioning column is of type text and the two system have
901+
different definitions of the collation used to partition the data.
902+
</para>
903+
</listitem>
904+
</varlistentry>
905+
891906
<varlistentry>
892907
<term><option>--section=<replaceable class="parameter">sectionname</replaceable></option></term>
893908
<listitem>

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -430,6 +430,21 @@ PostgreSQL documentation
430430
</listitem>
431431
</varlistentry>
432432

433+
<varlistentry>
434+
<term><option>--load-via-partition-root</></term>
435+
<listitem>
436+
<para>
437+
When dumping a COPY or INSERT statement for a partitioned table,
438+
target the root of the partitioning hierarchy which contains it rather
439+
than the partition itself. This may be useful when reloading data on
440+
a server where rows do not always fall into the same partitions as
441+
they did on the original server. This could happen, for example, if
442+
the partitioning column is of type text and the two system have
443+
different definitions of the collation used to partition the data.
444+
</para>
445+
</listitem>
446+
</varlistentry>
447+
433448
<varlistentry>
434449
<term><option>--use-set-session-authorization</></term>
435450
<listitem>

‎src/bin/pg_dump/common.c

Lines changed: 35 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,7 @@ static intnumExtensions;
6666
staticExtensionMemberId*extmembers;
6767
staticintnumextmembers;
6868

69-
staticvoidflagInhTables(TableInfo*tbinfo,intnumTables,
69+
staticvoidflagInhTables(Archive*fout,TableInfo*tbinfo,intnumTables,
7070
InhInfo*inhinfo,intnumInherits);
7171
staticvoidflagInhAttrs(DumpOptions*dopt,TableInfo*tblinfo,intnumTables);
7272
staticDumpableObject**buildIndexArray(void*objArray,intnumObjs,
@@ -243,7 +243,7 @@ getSchemaData(Archive *fout, int *numTablesPtr)
243243
/* Link tables to parents, mark parents of target tables interesting */
244244
if (g_verbose)
245245
write_msg(NULL,"finding inheritance relationships\n");
246-
flagInhTables(tblinfo,numTables,inhinfo,numInherits);
246+
flagInhTables(fout,tblinfo,numTables,inhinfo,numInherits);
247247

248248
if (g_verbose)
249249
write_msg(NULL,"reading column info for interesting tables\n");
@@ -294,8 +294,8 @@ getSchemaData(Archive *fout, int *numTablesPtr)
294294
}
295295

296296
/* flagInhTables -
297-
* Fill in parent link fields ofevery target table, and mark
298-
* parents of target tables as interesting
297+
* Fill in parent link fields oftables for which we need that information,
298+
*and markparents of target tables as interesting
299299
*
300300
* Note that only direct ancestors of targets are marked interesting.
301301
* This is sufficient; we don't much care whether they inherited their
@@ -304,34 +304,53 @@ getSchemaData(Archive *fout, int *numTablesPtr)
304304
* modifies tblinfo
305305
*/
306306
staticvoid
307-
flagInhTables(TableInfo*tblinfo,intnumTables,
307+
flagInhTables(Archive*fout,TableInfo*tblinfo,intnumTables,
308308
InhInfo*inhinfo,intnumInherits)
309309
{
310+
DumpOptions*dopt=fout->dopt;
310311
inti,
311312
j;
312-
intnumParents;
313-
TableInfo**parents;
314313

315314
for (i=0;i<numTables;i++)
316315
{
316+
boolfind_parents= true;
317+
boolmark_parents= true;
318+
317319
/* Some kinds never have parents */
318320
if (tblinfo[i].relkind==RELKIND_SEQUENCE||
319321
tblinfo[i].relkind==RELKIND_VIEW||
320322
tblinfo[i].relkind==RELKIND_MATVIEW)
321323
continue;
322324

323-
/* Don't bother computing anything for non-target tables, either */
325+
/*
326+
* Normally, we don't bother computing anything for non-target tables,
327+
* but if load-via-partition-root is specified, we gather information
328+
* on every partition in the system so that getRootTableInfo can trace
329+
* from any given to leaf partition all the way up to the root. (We
330+
* don't need to mark them as interesting for getTableAttrs, though.)
331+
*/
324332
if (!tblinfo[i].dobj.dump)
325-
continue;
333+
{
334+
mark_parents= false;
326335

327-
/* Find all the immediate parent tables */
328-
findParentsByOid(&tblinfo[i],inhinfo,numInherits);
336+
if (!dopt->load_via_partition_root||
337+
!tblinfo[i].ispartition)
338+
find_parents= false;
339+
}
340+
341+
/* If needed, find all the immediate parent tables. */
342+
if (find_parents)
343+
findParentsByOid(&tblinfo[i],inhinfo,numInherits);
329344

330-
/* Mark the parents as interesting for getTableAttrs */
331-
numParents=tblinfo[i].numParents;
332-
parents=tblinfo[i].parents;
333-
for (j=0;j<numParents;j++)
334-
parents[j]->interesting= true;
345+
/* If needed, mark the parents as interesting for getTableAttrs. */
346+
if (mark_parents)
347+
{
348+
intnumParents=tblinfo[i].numParents;
349+
TableInfo**parents=tblinfo[i].parents;
350+
351+
for (j=0;j<numParents;j++)
352+
parents[j]->interesting= true;
353+
}
335354
}
336355
}
337356

‎src/bin/pg_dump/pg_backup.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -157,6 +157,7 @@ typedef struct _dumpOptions
157157
intoutputNoTablespaces;
158158
intuse_setsessauth;
159159
intenable_row_security;
160+
intload_via_partition_root;
160161

161162
/* default, if no "inclusion" switches appear, is to dump everything */
162163
boolinclude_everything;

‎src/bin/pg_dump/pg_dump.c

Lines changed: 74 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -269,6 +269,7 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
269269
const char *prefix, Archive *fout);
270270
static char *get_synchronized_snapshot(Archive *fout);
271271
static void setupDumpWorker(Archive *AHX);
272+
static TableInfo *getRootTableInfo(TableInfo *tbinfo);
272273

273274

274275
int
@@ -345,6 +346,7 @@ main(int argc, char **argv)
345346
{"lock-wait-timeout", required_argument, NULL, 2},
346347
{"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
347348
{"quote-all-identifiers", no_argument, &quote_all_identifiers, 1},
349+
{"load-via-partition-root", no_argument, &dopt.load_via_partition_root, 1},
348350
{"role", required_argument, NULL, 3},
349351
{"section", required_argument, NULL, 5},
350352
{"serializable-deferrable", no_argument, &dopt.serializable_deferrable, 1},
@@ -959,6 +961,7 @@ help(const char *progname)
959961
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
960962
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
961963
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
964+
printf(_(" --load-via-partition-root load partitions via the root table\n"));
962965
printf(_(" --section=SECTION dump named section (pre-data, data, or post-data)\n"));
963966
printf(_(" --serializable-deferrable wait until the dump can run without anomalies\n"));
964967
printf(_(" --snapshot=SNAPSHOT use given snapshot for the dump\n"));
@@ -1902,8 +1905,32 @@ dumpTableData_insert(Archive *fout, void *dcontext)
19021905
if (insertStmt == NULL)
19031906
{
19041907
insertStmt = createPQExpBuffer();
1908+
1909+
/*
1910+
* When load-via-partition-root is set, get the root table
1911+
* name for the partition table, so that we can reload data
1912+
* through the root table.
1913+
*/
1914+
if (dopt->load_via_partition_root && tbinfo->ispartition)
1915+
{
1916+
TableInfo *parentTbinfo;
1917+
1918+
parentTbinfo = getRootTableInfo(tbinfo);
1919+
1920+
/*
1921+
* When we loading data through the root, we will qualify
1922+
* the table name. This is needed because earlier
1923+
* search_path will be set for the partition table.
1924+
*/
1925+
classname = (char *) fmtQualifiedId(fout->remoteVersion,
1926+
parentTbinfo->dobj.namespace->dobj.name,
1927+
parentTbinfo->dobj.name);
1928+
}
1929+
else
1930+
classname = fmtId(tbinfo->dobj.name);
1931+
19051932
appendPQExpBuffer(insertStmt, "INSERT INTO %s ",
1906-
fmtId(classname));
1933+
classname);
19071934

19081935
/* corner case for zero-column table */
19091936
if (nfields == 0)
@@ -2025,6 +2052,27 @@ dumpTableData_insert(Archive *fout, void *dcontext)
20252052
return 1;
20262053
}
20272054

2055+
/*
2056+
* getRootTableInfo:
2057+
* get the root TableInfo for the given partition table.
2058+
*/
2059+
static TableInfo *
2060+
getRootTableInfo(TableInfo *tbinfo)
2061+
{
2062+
TableInfo *parentTbinfo;
2063+
2064+
Assert(tbinfo->ispartition);
2065+
Assert(tbinfo->numParents == 1);
2066+
2067+
parentTbinfo = tbinfo->parents[0];
2068+
while (parentTbinfo->ispartition)
2069+
{
2070+
Assert(parentTbinfo->numParents == 1);
2071+
parentTbinfo = parentTbinfo->parents[0];
2072+
}
2073+
2074+
return parentTbinfo;
2075+
}
20282076

20292077
/*
20302078
* dumpTableData -
@@ -2041,14 +2089,38 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
20412089
PQExpBuffer clistBuf = createPQExpBuffer();
20422090
DataDumperPtr dumpFn;
20432091
char *copyStmt;
2092+
const char *copyFrom;
20442093

20452094
if (!dopt->dump_inserts)
20462095
{
20472096
/* Dump/restore using COPY */
20482097
dumpFn = dumpTableData_copy;
2098+
2099+
/*
2100+
* When load-via-partition-root is set, get the root table name for
2101+
* the partition table, so that we can reload data through the root
2102+
* table.
2103+
*/
2104+
if (dopt->load_via_partition_root && tbinfo->ispartition)
2105+
{
2106+
TableInfo *parentTbinfo;
2107+
2108+
parentTbinfo = getRootTableInfo(tbinfo);
2109+
2110+
/*
2111+
* When we load data through the root, we will qualify the table
2112+
* name, because search_path is set for the partition.
2113+
*/
2114+
copyFrom = fmtQualifiedId(fout->remoteVersion,
2115+
parentTbinfo->dobj.namespace->dobj.name,
2116+
parentTbinfo->dobj.name);
2117+
}
2118+
else
2119+
copyFrom = fmtId(tbinfo->dobj.name);
2120+
20492121
/* must use 2 steps here 'cause fmtId is nonreentrant */
20502122
appendPQExpBuffer(copyBuf, "COPY %s ",
2051-
fmtId(tbinfo->dobj.name));
2123+
copyFrom);
20522124
appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
20532125
fmtCopyColumnList(tbinfo, clistBuf),
20542126
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -80,6 +80,7 @@ static intno_subscriptions = 0;
8080
staticintno_unlogged_table_data=0;
8181
staticintno_role_passwords=0;
8282
staticintserver_version;
83+
staticintload_via_partition_root=0;
8384

8485
staticcharrole_catalog[10];
8586
#definePG_AUTHID "pg_authid"
@@ -128,6 +129,7 @@ main(int argc, char *argv[])
128129
{"lock-wait-timeout",required_argument,NULL,2},
129130
{"no-tablespaces",no_argument,&no_tablespaces,1},
130131
{"quote-all-identifiers",no_argument,&quote_all_identifiers,1},
132+
{"load-via-partition-root",no_argument,&load_via_partition_root,1},
131133
{"role",required_argument,NULL,3},
132134
{"use-set-session-authorization",no_argument,&use_setsessauth,1},
133135
{"no-publications",no_argument,&no_publications,1},
@@ -385,6 +387,8 @@ main(int argc, char *argv[])
385387
appendPQExpBufferStr(pgdumpopts," --no-tablespaces");
386388
if (quote_all_identifiers)
387389
appendPQExpBufferStr(pgdumpopts," --quote-all-identifiers");
390+
if (load_via_partition_root)
391+
appendPQExpBufferStr(pgdumpopts," --load-via-partition-root");
388392
if (use_setsessauth)
389393
appendPQExpBufferStr(pgdumpopts," --use-set-session-authorization");
390394
if (no_publications)
@@ -606,6 +610,7 @@ help(void)
606610
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
607611
printf(_(" --no-unlogged-table-data do not dump unlogged table data\n"));
608612
printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n"));
613+
printf(_(" --load-via-partition-root load partitions via the root table\n"));
609614
printf(_(" --use-set-session-authorization\n"
610615
" use SET SESSION AUTHORIZATION commands instead of\n"
611616
" ALTER OWNER commands to set ownership\n"));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp