Skip to content

Commit a563c24

Browse 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

File tree

3 files changed

+571
-96
lines changed

3 files changed

+571
-96
lines changed

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

Lines 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

Lines 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)