Hi all,
I would like to propose a new pg_dump option called --with-child to
include or exclude from a dump all child and partition tables when a
parent table is specified using option -t/--table or -T/--exclude-table.
The whole tree is dumped with the root table.
To include all partitions or child tables with inheritance in a table
dump we usually use the wildcard, for example:
pg_dump -d mydb -t "root_tbname*" > out.sql
This suppose that all child/partition tables use the prefix root_tbname
in their object name. This is often the case but, if you are as lucky as
me, the partitions could have a total different name. No need to say
that for inheritance this is rarely the case. The other problem is that
with the wildcard you can also dump relations that are not concerned at
all by what you want to dump. Using the --with-child option will allow
to just specify the root relation and all child/partition definitions
and/or data will be parts of dump.
pg_dump -d mydb --table "root_tbname" --with-childs > out.sql
To exclude a whole inheritance tree from a dump:
pg_dump -d mydb --exclude-table "root_tbname" --with-childs > out.sql
Here in attachment the patch that adds this feature to pg_dump.
Is there is any interest for this feature?
Best regards,
--
Gilles Darold
https://www.migops.com/
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2c938cd7e1..f9635442f9 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1172,6 +1172,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--with-childs</option></term>
+ <listitem>
+ <para>
+ Include or exclude from a dump all child and partition tables when a parent
+ table is specified using option <option>-t</option>/<option>--table</option>
+ or <option>-T</option>/<option>--exclude-table</option>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-?</option></term>
<term><option>--help</option></term>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index aba780ef4b..09284c82be 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -200,6 +200,7 @@ typedef struct _dumpOptions
int sequence_data; /* dump sequence data even in schema-only mode */
int do_nothing;
+ bool with_childs;
} DumpOptions;
/*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 5e800dc79a..83c092080e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -421,6 +421,7 @@ main(int argc, char **argv)
{"on-conflict-do-nothing", no_argument, &dopt.do_nothing, 1},
{"rows-per-insert", required_argument, NULL, 10},
{"include-foreign-data", required_argument, NULL, 11},
+ {"with-childs", no_argument, NULL, 12},
{NULL, 0, NULL, 0}
};
@@ -631,6 +632,10 @@ main(int argc, char **argv)
optarg);
break;
+ case 12: /* dump child table too */
+ dopt.with_childs = true;
+ break;
+
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -810,6 +815,14 @@ main(int argc, char **argv)
false);
/* non-matching exclusion patterns aren't an error */
+ /*
+ * The include child option require that there is
+ * at least one table inclusion
+ */
+ if (dopt.with_childs && table_include_patterns.head == NULL
+ && table_exclude_patterns.head == NULL)
+ pg_fatal("option --with-childs requires option -t/--table or -T/--exclude-table");
+
/* Expand table selection patterns into OID lists */
if (table_include_patterns.head != NULL)
{
@@ -1088,6 +1101,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(_(" --with-childs include or exclude from a dump all child and partition\n"
+ " tables when a parent table is specified using\n"
+ " -t/--table or -T/--exclude-table\n"));
printf(_("\nConnection options:\n"));
printf(_(" -d, --dbname=DBNAME database to dump\n"));
@@ -1520,6 +1536,15 @@ expand_table_name_patterns(Archive *fout,
PQExpBufferData dbbuf;
int dotcnt;
+ /*
+ * With --include_child we look recursively to the inheritance
+ * tree to find the childs tables of the matching include filter
+ */
+ if (fout->dopt->with_childs)
+ {
+ appendPQExpBuffer(query, "WITH RECURSIVE child_tree (relid) AS (\n");
+ }
+
/*
* Query must remain ABSOLUTELY devoid of unqualified names. This
* would be unnecessary given a pg_table_is_visible() variant taking a
@@ -1547,6 +1572,20 @@ expand_table_name_patterns(Archive *fout,
prohibit_crossdb_refs(GetConnection(fout), dbbuf.data, cell->val);
termPQExpBuffer(&dbbuf);
+ if (fout->dopt->with_childs)
+ {
+ appendPQExpBuffer(query, "\n UNION ALL"
+ "\n SELECT c.oid AS relid"
+ "\n FROM child_tree AS p"
+ "\n JOIN pg_catalog.pg_inherits AS i"
+ "\n ON (p.relid OPERATOR(pg_catalog.=) i.inhparent)"
+ "\n JOIN pg_catalog.pg_class AS c"
+ "\n ON (c.oid OPERATOR(pg_catalog.=) i.inhrelid)"
+ "\n)"
+ "\nSELECT relid FROM child_tree");
+
+ }
+
ExecuteSqlStatement(fout, "RESET search_path");
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
PQclear(ExecuteSqlQueryForSingleRow(fout,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 2eeef2a478..6f4ecc4210 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -319,6 +319,17 @@ my %pgdump_runs = (
'--exclude-table=dump_test.test_table', 'postgres',
],
},
+ exclude_child_table => {
+ dump_cmd => [
+ 'pg_dump',
+ '--no-sync',
+ '--with-childs',
+ "--file=$tempdir/exclude_child_table.sql",
+ '--exclude-table=dump_test.measurement',
+ 'postgres',
+ ],
+ },
+
exclude_test_table_data => {
dump_cmd => [
'pg_dump',
@@ -413,6 +424,18 @@ my %pgdump_runs = (
'postgres',
],
},
+ include_child_table => {
+ dump_cmd => [
+ 'pg_dump',
+ '--no-sync',
+ '--with-childs',
+ "--file=$tempdir/include_child_table.sql",
+ '--table=dump_test.measurement',
+ '--lock-wait-timeout='
+ . (1000 * $PostgreSQL::Test::Utils::timeout_default),
+ 'postgres',
+ ],
+ },
role => {
dump_cmd => [
'pg_dump',
@@ -541,6 +564,7 @@ my %full_runs = (
compression => 1,
createdb => 1,
defaults => 1,
+ exclude_child_table => 1,
exclude_dump_test_schema => 1,
exclude_test_table => 1,
exclude_test_table_data => 1,
@@ -936,6 +960,10 @@ my %tests = (
role => 1,
section_pre_data => 1,
binary_upgrade => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -1025,11 +1053,16 @@ my %tests = (
'ALTER TABLE measurement OWNER TO' => {
regexp => qr/^\QALTER TABLE dump_test.measurement OWNER TO \E.+;/m,
- like =>
- { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_pre_data => 1,
+ include_child_table => 1,
+ },
unlike => {
exclude_dump_test_schema => 1,
no_owner => 1,
+ exclude_child_table => 1,
},
},
@@ -1040,8 +1073,12 @@ my %tests = (
%full_runs,
role => 1,
section_pre_data => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ no_owner => 1,
+ exclude_child_table => 1,
},
- unlike => { no_owner => 1, },
},
'ALTER FOREIGN TABLE foreign_table OWNER TO' => {
@@ -2793,11 +2830,16 @@ my %tests = (
\)\n
\QPARTITION BY RANGE (logdate);\E\n
/xm,
- like =>
- { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_pre_data => 1,
+ include_child_table => 1,
+ },
unlike => {
binary_upgrade => 1,
exclude_dump_test_schema => 1,
+ exclude_child_table => 1,
},
},
@@ -2824,6 +2866,10 @@ my %tests = (
section_pre_data => 1,
role => 1,
binary_upgrade => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -2838,10 +2884,14 @@ my %tests = (
\QEXECUTE FUNCTION dump_test.trigger_func();\E
/xm,
like => {
- %full_runs, %dump_test_schema_runs, section_post_data => 1,
+ %full_runs,
+ %dump_test_schema_runs,
+ section_post_data => 1,
+ include_child_table => 1,
},
unlike => {
exclude_dump_test_schema => 1,
+ exclude_child_table => 1,
},
},
@@ -2869,6 +2919,10 @@ my %tests = (
section_post_data => 1,
role => 1,
binary_upgrade => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -2881,6 +2935,10 @@ my %tests = (
section_post_data => 1,
role => 1,
binary_upgrade => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -2893,6 +2951,10 @@ my %tests = (
section_post_data => 1,
role => 1,
binary_upgrade => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -3250,6 +3312,7 @@ my %tests = (
schema_only => 1,
section_post_data => 1,
test_schema_plus_large_objects => 1,
+ include_child_table => 1,
},
unlike => {
exclude_dump_test_schema => 1,
@@ -3258,6 +3321,7 @@ my %tests = (
pg_dumpall_globals_clean => 1,
role => 1,
section_pre_data => 1,
+ exclude_child_table => 1,
},
},
@@ -3271,9 +3335,16 @@ my %tests = (
\QALTER TABLE ONLY dump_test.measurement\E \n^\s+
\QADD CONSTRAINT measurement_pkey PRIMARY KEY (city_id, logdate);\E
/xm,
- like =>
- { %full_runs, %dump_test_schema_runs, section_post_data => 1, },
- unlike => { exclude_dump_test_schema => 1, },
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_post_data => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ exclude_child_table => 1,
+ },
},
'CREATE INDEX ... ON measurement_y2006_m2' => {
@@ -3284,6 +3355,10 @@ my %tests = (
%full_runs,
role => 1,
section_post_data => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -3295,6 +3370,10 @@ my %tests = (
%full_runs,
role => 1,
section_post_data => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ exclude_child_table => 1,
},
},
@@ -3324,6 +3403,7 @@ my %tests = (
role => 1,
schema_only => 1,
section_post_data => 1,
+ include_child_table => 1,
},
unlike => {
only_dump_test_schema => 1,
@@ -3332,6 +3412,7 @@ my %tests = (
pg_dumpall_globals_clean => 1,
section_pre_data => 1,
test_schema_plus_large_objects => 1,
+ exclude_child_table => 1,
},
},
@@ -3614,11 +3695,16 @@ my %tests = (
TO regress_dump_test_role;',
regexp =>
qr/^\QGRANT SELECT ON TABLE dump_test.measurement TO regress_dump_test_role;\E/m,
- like =>
- { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_pre_data => 1,
+ include_child_table => 1,
+ },
unlike => {
exclude_dump_test_schema => 1,
no_privs => 1,
+ exclude_child_table => 1,
},
},
@@ -3636,8 +3722,12 @@ my %tests = (
%full_runs,
role => 1,
section_pre_data => 1,
+ include_child_table => 1,
+ },
+ unlike => {
+ no_privs => 1,
+ exclude_child_table => 1,
},
- unlike => { no_privs => 1, },
},
'GRANT ALL ON LARGE OBJECT ...' => {
@@ -3884,6 +3974,7 @@ my %tests = (
only_dump_test_table => 1,
role => 1,
section_pre_data => 1,
+ include_child_table => 1,
},
unlike => { no_privs => 1, },
},
@@ -4106,6 +4197,13 @@ $node->command_fails_like(
qr/pg_dumpall: error: improper qualified name \(too many dotted names\): myhost\.mydb/,
'pg_dumpall: option --exclude-database rejects multipart database names');
+#########################################
+# Test invalid use of --with-childs
+$node->command_fails_like(
+ [ 'pg_dump', '-p', "$port", '--with-childs' ],
+ qr/pg_dump: error: option --with-childs requires option -t\/--table or -T\/--exclude-table/,
+ 'pg_dump: option --with-childs require inclusion or exclusion of tables');
+
#########################################
# Test valid database exclusion patterns
