Hi
pá 29. 5. 2020 v 20:25 odesílatel Justin Pryzby <[email protected]>
napsal:
> On Fri, May 29, 2020 at 04:21:00PM +0200, Pavel Stehule wrote:
> > one my customer has to specify dumped tables name by name. After years
> and
> > increasing database size and table numbers he has problem with too short
> > command line. He need to read the list of tables from file (or from
> stdin).
>
> +1 - we would use this.
>
> We put a regex (actually a pg_dump pattern) of tables to skip (timeseries
> partitions which are older than a few days and which are also dumped once
> not
> expected to change, and typically not redumped). We're nowhere near the
> execve() limit, but it'd be nice if the command was primarily a list of
> options
> and not a long regex.
>
> Please also support reading from file for --exclude-table=pattern.
>
> I'm drawing a parallel between this and rsync --include/--exclude and
> --filter.
>
> We'd be implementing a new --filter, which might have similar syntax to
> rsync
> (which I always forget).
>
I implemented support for all "repeated" pg_dump options.
--exclude-schemas-file=FILENAME
--exclude-tables-data-file=FILENAME
--exclude-tables-file=FILENAME
--include-foreign-data-file=FILENAME
--include-schemas-file=FILENAME
--include-tables-file=FILENAME
Regards
Pavel
I invite any help with doc. There is just very raw text
> --
> Justin
>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2f0807e912..5e39268d51 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -725,6 +725,16 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--exclude-schemas-file=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ This option allows to specify file with schemas that will not be
+ dumped. Any row of is one schema's name.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--exclude-table-data=<replaceable class="parameter">pattern</replaceable></option></term>
<listitem>
@@ -743,6 +753,24 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--exclude-tables-data-file=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not dump data of tables spefified in file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--exclude-tables-file=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not dump tables spefified in file.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--extra-float-digits=<replaceable class="parameter">ndigits</replaceable></option></term>
<listitem>
@@ -795,6 +823,33 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--include-foreign-data-file=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Include data of foreign servers specified in file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--include-schemas-file=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Dump schema(s) specified in file.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--include-tables-file=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Dump table(s) specified in file.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--inserts</option></term>
<listitem>
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index dfe43968b8..db9fb10f68 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -290,6 +290,7 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
static char *get_synchronized_snapshot(Archive *fout);
static void setupDumpWorker(Archive *AHX);
static TableInfo *getRootTableInfo(TableInfo *tbinfo);
+static bool read_options_from_file(SimpleStringList *slist, char *filename);
int
@@ -362,8 +363,13 @@ main(int argc, char **argv)
{"disable-dollar-quoting", no_argument, &dopt.disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &dopt.disable_triggers, 1},
{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
+ {"exclude-schemas-file", required_argument, NULL, 12},
{"exclude-table-data", required_argument, NULL, 4},
+ {"exclude-tables-data-file", required_argument, NULL, 14},
+ {"exclude-tables-file", required_argument, NULL, 13},
{"extra-float-digits", required_argument, NULL, 8},
+ {"include-schemas-file", required_argument, NULL, 15},
+ {"include-tables-file", required_argument, NULL, 16},
{"if-exists", no_argument, &dopt.if_exists, 1},
{"inserts", no_argument, NULL, 9},
{"lock-wait-timeout", required_argument, NULL, 2},
@@ -386,6 +392,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},
+ {"include-foreign-data-file", required_argument, NULL, 17},
{NULL, 0, NULL, 0}
};
@@ -603,6 +610,32 @@ main(int argc, char **argv)
optarg);
break;
+ case 12: /* read exclude schama names from file */
+ (void) read_options_from_file(&schema_exclude_patterns, optarg);
+ break;
+
+ case 13: /* read exclude table names from file */
+ (void) read_options_from_file(&table_exclude_patterns, optarg);
+ break;
+
+ case 14: /* read exclude table data names from file */
+ (void) read_options_from_file(&tabledata_exclude_patterns, optarg);
+ break;
+
+ case 15: /* read table names from file */
+ if (read_options_from_file(&schema_include_patterns, optarg))
+ dopt.include_everything = false;
+ break;
+
+ case 16: /* read table names from file */
+ if (read_options_from_file(&table_include_patterns, optarg))
+ dopt.include_everything = false;
+ break;
+
+ case 17: /* read exclude table data names from file */
+ (void) read_options_from_file(&foreign_servers_include_patterns, optarg);
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -1020,12 +1053,24 @@ help(const char *progname)
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --enable-row-security enable row security (dump only content user has\n"
" access to)\n"));
+ printf(_(" --exclude-schemas-file=FILENAME\n"
+ " do NOT dump schema specified in file\n"));
printf(_(" --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\n"));
+ printf(_(" --exclude-tables-data-file=FILENAME\n"
+ " do NOT dump data for tables specified in file\n"));
+ printf(_(" --exclude-tables-file=FILENAME\n"
+ " do NOT dump tables specified in file\n"));
printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n"));
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
printf(_(" --include-foreign-data=PATTERN\n"
" include data of foreign tables on foreign\n"
" servers matching PATTERN\n"));
+ printf(_(" --include-foreign-data-file=FILENAME\n"
+ " include data of foreign servers specified by file\n"));
+ printf(_(" --include-schemas-file=FILENAME\n"
+ " dump schema(s) specified in file\n"));
+ printf(_(" --include-tables-file=FILENAME\n"
+ " dump table(s) specified in file\n"));
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
printf(_(" --no-comments do not dump comments\n"));
@@ -18647,3 +18692,70 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
if (!res)
pg_log_warning("could not parse reloptions array");
}
+
+/*
+ * Read list of values from file specified by name. Returns true when
+ * at least one value was read.
+ */
+static bool
+read_options_from_file(SimpleStringList *slist, char *filename)
+{
+ FILE *f;
+ char *line;
+ ssize_t chars;
+ size_t line_size = 1024;
+ bool use_stdin = false;
+ bool result = false;
+
+ /* use "-" as symbol for stdin */
+ if (strcmp(filename, "-") != 0)
+ {
+ f = fopen(optarg, "r");
+ if (!f)
+ {
+ fprintf(stderr,
+ _("%s: could not open the input file \"%s\": %s\n"),
+ progname,
+ optarg,
+ strerror(errno));
+ exit_nicely(1);
+ }
+ }
+ else
+ {
+ f = stdin;
+ use_stdin = true;
+ }
+
+ line = malloc(line_size);
+
+ while ((chars = getline(&line, &line_size, f)) != -1)
+ {
+ if (line[chars - 1] == '\n')
+ line[chars - 1] = '\0';
+
+ /* ignore empty rows */
+ if (*line != '\0')
+ {
+ simple_string_list_append(slist, line);
+ result = true;
+ }
+ }
+
+ if (ferror(f))
+ {
+ fprintf(stderr,
+ _("%s: could not read from file \"%s\": %s\n"),
+ progname,
+ use_stdin ? "stdin" : optarg,
+ strerror(errno));
+ exit_nicely(1);
+ }
+
+ if (!use_stdin)
+ fclose(f);
+
+ free(line);
+
+ return result;
+}
