Skip to content

Navigation Menu

Sign in

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
Appearance settings

Commit a563c24

Browse filesBrowse files
tglsfdctglsfdc
committed
Allow pg_dump to include/exclude child tables automatically.
This patch adds new pg_dump switches --table-and-children=pattern --exclude-table-and-children=pattern --exclude-table-data-and-children=pattern which act the same as the existing --table, --exclude-table, and --exclude-table-data switches, except that any partitions or inheritance child tables of the table(s) matching the pattern are also included or excluded. Gilles Darold, reviewed by Stéphane Tachoires Discussion: https://postgr.es/m/5aa393b5-5f67-8447-b83e-544516990ee2@migops.com
1 parent 684ffac commit a563c24
Copy full SHA for a563c24

File tree

3 files changed

+571
-96
lines changed
Filter options

3 files changed

+571
-96
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/pg_dump.sgml
+40-2Lines changed: 40 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -775,6 +775,19 @@ PostgreSQL documentation
775775
</listitem>
776776
</varlistentry>
777777

778+
<varlistentry>
779+
<term><option>--exclude-table-and-children=<replaceable class="parameter">pattern</replaceable></option></term>
780+
<listitem>
781+
<para>
782+
This is the same as
783+
the <option>-T</option>/<option>--exclude-table</option> option,
784+
except that it also excludes any partitions or inheritance child
785+
tables of the table(s) matching the
786+
<replaceable class="parameter">pattern</replaceable>.
787+
</para>
788+
</listitem>
789+
</varlistentry>
790+
778791
<varlistentry>
779792
<term><option>--exclude-table-data=<replaceable class="parameter">pattern</replaceable></option></term>
780793
<listitem>
@@ -793,6 +806,18 @@ PostgreSQL documentation
793806
</listitem>
794807
</varlistentry>
795808

809+
<varlistentry>
810+
<term><option>--exclude-table-data-and-children=<replaceable class="parameter">pattern</replaceable></option></term>
811+
<listitem>
812+
<para>
813+
This is the same as the <option>--exclude-table-data</option> option,
814+
except that it also excludes data of any partitions or inheritance
815+
child tables of the table(s) matching the
816+
<replaceable class="parameter">pattern</replaceable>.
817+
</para>
818+
</listitem>
819+
</varlistentry>
820+
796821
<varlistentry>
797822
<term><option>--extra-float-digits=<replaceable class="parameter">ndigits</replaceable></option></term>
798823
<listitem>
@@ -1142,9 +1167,9 @@ PostgreSQL documentation
11421167
Require that each
11431168
extension (<option>-e</option>/<option>--extension</option>),
11441169
schema (<option>-n</option>/<option>--schema</option>) and
1145-
table (<option>-t</option>/<option>--table</option>) qualifier
1170+
table (<option>-t</option>/<option>--table</option>) pattern
11461171
match at least one extension/schema/table in the database to be dumped.
1147-
Note that if none of the extension/schema/table qualifiers find
1172+
Note that if none of the extension/schema/table patterns find
11481173
matches, <application>pg_dump</application> will generate an error
11491174
even without <option>--strict-names</option>.
11501175
</para>
@@ -1158,6 +1183,19 @@ PostgreSQL documentation
11581183
</listitem>
11591184
</varlistentry>
11601185

1186+
<varlistentry>
1187+
<term><option>--table-and-children=<replaceable class="parameter">pattern</replaceable></option></term>
1188+
<listitem>
1189+
<para>
1190+
This is the same as
1191+
the <option>-t</option>/<option>--table</option> option,
1192+
except that it also includes any partitions or inheritance child
1193+
tables of the table(s) matching the
1194+
<replaceable class="parameter">pattern</replaceable>.
1195+
</para>
1196+
</listitem>
1197+
</varlistentry>
1198+
11611199
<varlistentry>
11621200
<term><option>--use-set-session-authorization</option></term>
11631201
<listitem>

‎src/bin/pg_dump/pg_dump.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_dump.c
+73-13Lines changed: 73 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -119,11 +119,15 @@ static SimpleStringList schema_exclude_patterns = {NULL, NULL};
119119
static SimpleOidList schema_exclude_oids = {NULL, NULL};
120120

121121
static SimpleStringList table_include_patterns = {NULL, NULL};
122+
static SimpleStringList table_include_patterns_and_children = {NULL, NULL};
122123
static SimpleOidList table_include_oids = {NULL, NULL};
123124
static SimpleStringList table_exclude_patterns = {NULL, NULL};
125+
static SimpleStringList table_exclude_patterns_and_children = {NULL, NULL};
124126
static SimpleOidList table_exclude_oids = {NULL, NULL};
125127
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
128+
static SimpleStringList tabledata_exclude_patterns_and_children = {NULL, NULL};
126129
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
130+
127131
static SimpleStringList foreign_servers_include_patterns = {NULL, NULL};
128132
static SimpleOidList foreign_servers_include_oids = {NULL, NULL};
129133

@@ -180,7 +184,8 @@ static void expand_foreign_server_name_patterns(Archive *fout,
180184
static void expand_table_name_patterns(Archive *fout,
181185
SimpleStringList *patterns,
182186
SimpleOidList *oids,
183-
bool strict_names);
187+
bool strict_names,
188+
bool with_child_tables);
184189
static void prohibit_crossdb_refs(PGconn *conn, const char *dbname,
185190
const char *pattern);
186191

@@ -421,6 +426,9 @@ main(int argc, char **argv)
421426
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
422427
{"rows-per-insert", required_argument, NULL, 10},
423428
{"include-foreign-data", required_argument, NULL, 11},
429+
{"table-and-children", required_argument, NULL, 12},
430+
{"exclude-table-and-children", required_argument, NULL, 13},
431+
{"exclude-table-data-and-children", required_argument, NULL, 14},
424432

425433
{NULL, 0, NULL, 0}
426434
};
@@ -631,6 +639,22 @@ main(int argc, char **argv)
631639
optarg);
632640
break;
633641

642+
case 12: /* include table(s) and their children */
643+
simple_string_list_append(&table_include_patterns_and_children,
644+
optarg);
645+
dopt.include_everything = false;
646+
break;
647+
648+
case 13: /* exclude table(s) and their children */
649+
simple_string_list_append(&table_exclude_patterns_and_children,
650+
optarg);
651+
break;
652+
653+
case 14: /* exclude data of table(s) and children */
654+
simple_string_list_append(&tabledata_exclude_patterns_and_children,
655+
optarg);
656+
break;
657+
634658
default:
635659
/* getopt_long already emitted a complaint */
636660
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -810,21 +834,30 @@ main(int argc, char **argv)
810834
/* non-matching exclusion patterns aren't an error */
811835

812836
/* Expand table selection patterns into OID lists */
813-
if (table_include_patterns.head != NULL)
814-
{
815-
expand_table_name_patterns(fout, &table_include_patterns,
816-
&table_include_oids,
817-
strict_names);
818-
if (table_include_oids.head == NULL)
819-
pg_fatal("no matching tables were found");
820-
}
837+
expand_table_name_patterns(fout, &table_include_patterns,
838+
&table_include_oids,
839+
strict_names, false);
840+
expand_table_name_patterns(fout, &table_include_patterns_and_children,
841+
&table_include_oids,
842+
strict_names, true);
843+
if ((table_include_patterns.head != NULL ||
844+
table_include_patterns_and_children.head != NULL) &&
845+
table_include_oids.head == NULL)
846+
pg_fatal("no matching tables were found");
847+
821848
expand_table_name_patterns(fout, &table_exclude_patterns,
822849
&table_exclude_oids,
823-
false);
850+
false, false);
851+
expand_table_name_patterns(fout, &table_exclude_patterns_and_children,
852+
&table_exclude_oids,
853+
false, true);
824854

825855
expand_table_name_patterns(fout, &tabledata_exclude_patterns,
826856
&tabledata_exclude_oids,
827-
false);
857+
false, false);
858+
expand_table_name_patterns(fout, &tabledata_exclude_patterns_and_children,
859+
&tabledata_exclude_oids,
860+
false, true);
828861

829862
expand_foreign_server_name_patterns(fout, &foreign_servers_include_patterns,
830863
&foreign_servers_include_oids);
@@ -1051,7 +1084,7 @@ help(const char *progname)
10511084
" plain-text format\n"));
10521085
printf(_(" -s, --schema-only dump only the schema, no data\n"));
10531086
printf(_(" -S, --superuser=NAME superuser user name to use in plain-text format\n"));
1054-
printf(_(" -t, --table=PATTERN dump the specified table(s) only\n"));
1087+
printf(_(" -t, --table=PATTERN dump only the specified table(s)\n"));
10551088
printf(_(" -T, --exclude-table=PATTERN do NOT dump the specified table(s)\n"));
10561089
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
10571090
printf(_(" --binary-upgrade for use by upgrade utilities only\n"));
@@ -1060,7 +1093,13 @@ help(const char *progname)
10601093
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
10611094
printf(_(" --enable-row-security enable row security (dump only content user has\n"
10621095
" access to)\n"));
1096+
printf(_(" --exclude-table-and-children=PATTERN\n"
1097+
" do NOT dump the specified table(s),\n"
1098+
" including child and partition tables\n"));
10631099
printf(_(" --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
1100+
printf(_(" --exclude-table-data-and-children=PATTERN\n"
1101+
" do NOT dump data for the specified table(s),\n"
1102+
" including child and partition tables\n"));
10641103
printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n"));
10651104
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
10661105
printf(_(" --include-foreign-data=PATTERN\n"
@@ -1084,6 +1123,8 @@ help(const char *progname)
10841123
printf(_(" --snapshot=SNAPSHOT use given snapshot for the dump\n"));
10851124
printf(_(" --strict-names require table and/or schema include patterns to\n"
10861125
" match at least one entity each\n"));
1126+
printf(_(" --table-and-children=PATTERN dump only the specified table(s),\n"
1127+
" including child and partition tables\n"));
10871128
printf(_(" --use-set-session-authorization\n"
10881129
" use SET SESSION AUTHORIZATION commands instead of\n"
10891130
" ALTER OWNER commands to set ownership\n"));
@@ -1497,7 +1538,7 @@ expand_foreign_server_name_patterns(Archive *fout,
14971538
static void
14981539
expand_table_name_patterns(Archive *fout,
14991540
SimpleStringList *patterns, SimpleOidList *oids,
1500-
bool strict_names)
1541+
bool strict_names, bool with_child_tables)
15011542
{
15021543
PQExpBuffer query;
15031544
PGresult *res;
@@ -1523,7 +1564,15 @@ expand_table_name_patterns(Archive *fout,
15231564
* Query must remain ABSOLUTELY devoid of unqualified names. This
15241565
* would be unnecessary given a pg_table_is_visible() variant taking a
15251566
* search_path argument.
1567+
*
1568+
* For with_child_tables, we start with the basic query's results and
1569+
* recursively search the inheritance tree to add child tables.
15261570
*/
1571+
if (with_child_tables)
1572+
{
1573+
appendPQExpBuffer(query, "WITH RECURSIVE partition_tree (relid) AS (\n");
1574+
}
1575+
15271576
appendPQExpBuffer(query,
15281577
"SELECT c.oid"
15291578
"\nFROM pg_catalog.pg_class c"
@@ -1546,6 +1595,17 @@ expand_table_name_patterns(Archive *fout,
15461595
prohibit_crossdb_refs(GetConnection(fout), dbbuf.data, cell->val);
15471596
termPQExpBuffer(&dbbuf);
15481597

1598+
if (with_child_tables)
1599+
{
1600+
appendPQExpBuffer(query, "UNION"
1601+
"\nSELECT i.inhrelid"
1602+
"\nFROM partition_tree p"
1603+
"\n JOIN pg_catalog.pg_inherits i"
1604+
"\n ON p.relid OPERATOR(pg_catalog.=) i.inhparent"
1605+
"\n)"
1606+
"\nSELECT relid FROM partition_tree");
1607+
}
1608+
15491609
ExecuteSqlStatement(fout, "RESET search_path");
15501610
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
15511611
PQclear(ExecuteSqlQueryForSingleRow(fout,

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.