ne 19. 3. 2023 v 15:01 odesÃlatel Justin Pryzby <pry...@telsasoft.com>
napsal:
> On Thu, Mar 16, 2023 at 01:05:41PM +0100, Pavel Stehule wrote:
> > rebase + enhancing about related option from a563c24
>
> Thanks.
>
> It looks like this doesn't currently handle extensions, which were added
> at 6568cef26e.
>
> > + <literal>table_and_children</literal>: tables, works like
> > + <option>-t</option>/<option>--table</option>, except that
> > + it also includes any partitions or inheritance child
> > + tables of the table(s) matching the
> > + <replaceable class="parameter">pattern</replaceable>.
>
> Why doesn't this just say "works like --table-and-children" ?
>
changed
>
> I think as you wrote log_invalid_filter_format(), the messages wouldn't
> be translated, because they're printed via %s. One option is to call
> _() on the message.
>
fixed
>
> > +ok($dump !=~ qr/^CREATE TABLE public\.bootab/m, "exclude dumped
> children table");
>
> !=~ is being interpretted as as numeric "!=" and throwing warnings.
> It should be a !~ b, right ?
> It'd be nice if perl warnings during the tests were less easy to miss.
>
should be fixed by you
>
> > + * char is not alpha. The char '_' is allowed too (exclude first
> position).
>
>
> Why is it treated specially? Could it be treated the same as alpha?
>
It is usual behaviour in Postgres for keywords. Important is the complete
sentence "Returns NULL when the buffer is empty or the first char is not
alpha."
In this case this implementation has no big impact on behaviour - probably
you got a message "unknown keyword" instead of "missing keyword". But I
would
implement behaviour consistent with other places. My opinion in this case
is not extra strong - we can define the form of keywords like we want, just
this is consistent
with other parsers in Postgres.
>
> > + log_invalid_filter_format(&fstate,
> > +
> "\"include\" table data filter is not allowed");
> > + log_invalid_filter_format(&fstate,
> > +
> "\"include\" table data and children filter is not allowed");
>
> For these, it might be better to write the literal option:
>
> > +
> "include filter for \"table_data_and_children\" is not allowed");
>
> Because the option is a literal and shouldn't be translated.
> And it's probably better to write that using %s, like:
>
> > +
> "include filter for \"%s\" is not allowed");
>
done
>
> That makes shorter and fewer strings.
>
> Find attached a bunch of other corrections as 0002.txt
>
merged
Regards
Pavel
From 844b54e0dcdb65dbf7f86a75b977db16dad07254 Mon Sep 17 00:00:00 2001
From: "ok...@github.com" <pavel.steh...@gmail.com>
Date: Thu, 16 Mar 2023 08:18:08 +0100
Subject: [PATCH] possibility to read options for dump from file
---
doc/src/sgml/ref/pg_dump.sgml | 107 +++
doc/src/sgml/ref/pg_dumpall.sgml | 22 +
doc/src/sgml/ref/pg_restore.sgml | 25 +
src/bin/pg_dump/Makefile | 5 +-
src/bin/pg_dump/filter.c | 509 ++++++++++++++
src/bin/pg_dump/filter.h | 57 ++
src/bin/pg_dump/meson.build | 2 +
src/bin/pg_dump/pg_dump.c | 114 ++++
src/bin/pg_dump/pg_dumpall.c | 60 +-
src/bin/pg_dump/pg_restore.c | 110 +++
src/bin/pg_dump/t/005_pg_dump_filterfile.pl | 701 ++++++++++++++++++++
src/tools/msvc/Mkvcbuild.pm | 1 +
12 files changed, 1710 insertions(+), 3 deletions(-)
create mode 100644 src/bin/pg_dump/filter.c
create mode 100644 src/bin/pg_dump/filter.h
create mode 100644 src/bin/pg_dump/t/005_pg_dump_filterfile.pl
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index d6b1faa804..f3e287b75a 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -829,6 +829,99 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specify a filename from which to read patterns for objects to include
+ or exclude from the dump. The patterns are interpreted according to the
+ same rules as the corresponding options:
+ <option>-t</option>/<option>--table</option>,
+ <option>--table-and-children</option>,
+ <option>--exclude-table-and-children</option> or
+ <option>-T</option> for tables,
+ <option>-n</option>/<option>--schema</option> for schemas,
+ <option>--include-foreign-data</option> for data on foreign servers and
+ <option>--exclude-table-data</option>,
+ <option>--exclude-table-data-and-children</option> for table data.
+ To read from <literal>STDIN</literal>, use <filename>-</filename> as the
+ filename. The <option>--filter</option> option can be specified in
+ conjunction with the above listed options for including or excluding
+ objects, and can also be specified more than once for multiple filter
+ files.
+ </para>
+
+ <para>
+ The file lists one object pattern per row, with the following format:
+<synopsis>
+{ include | exclude } { table | table_and_children | schema | foreign_data | table_data | table_data_and_children } <replaceable class="parameter">PATTERN</replaceable>
+</synopsis>
+ </para>
+
+ <para>
+ The first keyword specifies whether the objects matched by the pattern
+ are to be included or excluded. The second keyword specifies the type
+ of object to be filtered using the pattern:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>table</literal>: tables, works like
+ <option>-t</option>/<option>--table</option>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>table_and_children</literal>: tables, works like
+ <option>--table-and-childrent</option>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>schema</literal>: schemas, works like
+ <option>-n</option>/<option>--schema</option>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>foreign_data</literal>: data on foreign servers, works like
+ <option>--include-foreign-data</option>. This keyword can only be
+ used with the <literal>include</literal> keyword.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>table_data</literal>: table data, works like
+ <option>--exclude-table-data</option>. This keyword can only be
+ used with the <literal>exclude</literal> keyword.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>table_data_and_children</literal>: table data of any
+ partitions or inheritance child, works like
+ <option>--exclude-table-data-and-children</option>. This keyword can only be
+ used with the <literal>exclude</literal> keyword.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ Lines starting with <literal>#</literal> are considered comments and
+ ignored. Comments can be placed after filter as well. Blank lines
+ are also ignored. See <xref linkend="app-psql-patterns"/> for how to
+ perform quoting in patterns.
+ </para>
+
+ <para>
+ Example files are listed below in the <xref linkend="pg-dump-examples"/>
+ section.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--if-exists</option></term>
<listitem>
@@ -1159,6 +1252,7 @@ PostgreSQL documentation
schema (<option>-n</option>/<option>--schema</option>) and
table (<option>-t</option>/<option>--table</option>) pattern
match at least one extension/schema/table in the database to be dumped.
+ This also applies to filters used with <option>--filter</option>.
Note that if none of the extension/schema/table patterns find
matches, <application>pg_dump</application> will generate an error
even without <option>--strict-names</option>.
@@ -1581,6 +1675,19 @@ CREATE DATABASE foo WITH TEMPLATE template0;
<screen>
<prompt>$</prompt> <userinput>pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql</userinput>
+</screen></para>
+
+ <para>
+ To dump all tables with names starting with mytable, except for table
+ <literal>mytable2</literal>, specify a filter file
+ <filename>filter.txt</filename> like:
+<programlisting>
+include table mytable*
+exclude table mytable2
+</programlisting>
+
+<screen>
+<prompt>$</prompt> <userinput>pg_dump --filter=filter.txt mydb > db.sql</userinput>
</screen></para>
</refsect1>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index e219a79858..547fe3803f 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -122,6 +122,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specify a filename from which to read patterns for databases excluded
+ from the dump. The patterns are interpretted according to the same rules
+ as <option>--exclude-database</option>.
+ To read from <literal>STDIN</literal>, use <filename>-</filename> as the
+ filename. The <option>--filter</option> option can be specified in
+ conjunction with the above listed options for excluding databases,
+ and can also be specified more than once for multiple filter files.
+ </para>
+
+ <para>
+ The file lists one database pattern per row, with the following format:
+<synopsis>
+exclude database <replaceable class="parameter">PATTERN</replaceable>
+</synopsis>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-g</option></term>
<term><option>--globals-only</option></term>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 47bd7dbda0..ffeb564c52 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -188,6 +188,31 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specify a filename from which to read patterns for objects excluded
+ or included from restore. The patterns are interpretted according to the
+ same rules as <option>--schema</option>, <option>--exclude-schema</option>,
+ <option>--function</option>, <option>--index</option>, <option>--table</option>
+ or <option>--trigger</option>.
+ To read from <literal>STDIN</literal>, use <filename>-</filename> as the
+ filename. The <option>--filter</option> option can be specified in
+ conjunction with the above listed options for including or excluding
+ objects, and can also be specified more than once for multiple filter
+ files.
+ </para>
+
+ <para>
+ The file lists one database pattern per row, with the following format:
+<synopsis>
+{ include | exclude } { function | index | schema | table | trigger } <replaceable class="parameter">PATTERN</replaceable>
+</synopsis>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-F <replaceable class="parameter">format</replaceable></option></term>
<term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile
index eb8f59459a..bff55b6b1a 100644
--- a/src/bin/pg_dump/Makefile
+++ b/src/bin/pg_dump/Makefile
@@ -30,6 +30,7 @@ OBJS = \
compress_lz4.o \
compress_none.o \
dumputils.o \
+ filter.o \
parallel.o \
pg_backup_archiver.o \
pg_backup_custom.o \
@@ -47,8 +48,8 @@ pg_dump: pg_dump.o common.o pg_dump_sort.o $(OBJS) | submake-libpq submake-libpg
pg_restore: pg_restore.o $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils
$(CC) $(CFLAGS) pg_restore.o $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
-pg_dumpall: pg_dumpall.o dumputils.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils
- $(CC) $(CFLAGS) pg_dumpall.o dumputils.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
+pg_dumpall: pg_dumpall.o dumputils.o filter.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils
+ $(CC) $(CFLAGS) pg_dumpall.o dumputils.o filter.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
install: all installdirs
$(INSTALL_PROGRAM) pg_dump$(X) '$(DESTDIR)$(bindir)'/pg_dump$(X)
diff --git a/src/bin/pg_dump/filter.c b/src/bin/pg_dump/filter.c
new file mode 100644
index 0000000000..ef77459038
--- /dev/null
+++ b/src/bin/pg_dump/filter.c
@@ -0,0 +1,509 @@
+/*-------------------------------------------------------------------------
+ *
+ * Implementation of simple filter file parser
+ *
+ * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/bin/pg_dump/filter.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres_fe.h"
+
+#include "common/fe_memutils.h"
+#include "common/logging.h"
+#include "common/string.h"
+#include "filter.h"
+#include "lib/stringinfo.h"
+#include "pqexpbuffer.h"
+
+#define is_keyword_str(cstr, str, bytes) \
+ ((strlen(cstr) == (bytes)) && (pg_strncasecmp((cstr), (str), (bytes)) == 0))
+
+/*
+ * Following routines are called from pg_dump, pg_dumpall and pg_restore.
+ * Unfortunately, the implementation of exit_nicely in pg_dump and pg_restore is
+ * different from the one in pg_dumpall, so instead of calling exit_nicely we
+ * have to return some error flag (in this case NULL), and exit_nicely will be
+ * executed from caller's routine.
+ */
+
+/*
+ * Opens filter's file and initialize fstate structure.
+ * Returns true on success.
+ */
+bool
+filter_init(FilterStateData *fstate, const char *filename)
+{
+ fstate->filename = filename;
+ fstate->lineno = 0;
+ initStringInfo(&fstate->linebuff);
+
+ if (strcmp(filename, "-") != 0)
+ {
+ fstate->fp = fopen(filename, "r");
+ if (!fstate->fp)
+ {
+ pg_log_error("could not open filter file \"%s\": %m", filename);
+ return false;
+ }
+ }
+ else
+ fstate->fp = stdin;
+
+ fstate->is_error = false;
+
+ return true;
+}
+
+/*
+ * Release allocated resources for the given filter.
+ */
+void
+filter_free(FilterStateData *fstate)
+{
+ free(fstate->linebuff.data);
+ fstate->linebuff.data = NULL;
+
+ if (fstate->fp && fstate->fp != stdin)
+ {
+ if (fclose(fstate->fp) != 0)
+ pg_log_error("could not close filter file \"%s\": %m", fstate->filename);
+
+ fstate->fp = NULL;
+ }
+}
+
+/*
+ * Translate FilterObjectType enum to string. It is designed for formatting
+ * of error message in log_unsupported_filter_object_type routine.
+ */
+static const char *
+filter_object_type_name(FilterObjectType fot)
+{
+ switch (fot)
+ {
+ case FILTER_OBJECT_TYPE_NONE:
+ return "comment or empty line";
+ case FILTER_OBJECT_TYPE_TABLE_DATA:
+ return "table data";
+ case FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN:
+ return "table data and children";
+ case FILTER_OBJECT_TYPE_DATABASE:
+ return "database";
+ case FILTER_OBJECT_TYPE_FOREIGN_DATA:
+ return "foreign data";
+ case FILTER_OBJECT_TYPE_FUNCTION:
+ return "function";
+ case FILTER_OBJECT_TYPE_INDEX:
+ return "index";
+ case FILTER_OBJECT_TYPE_SCHEMA:
+ return "schema";
+ case FILTER_OBJECT_TYPE_TABLE:
+ return "table";
+ case FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN:
+ return "table and children";
+ case FILTER_OBJECT_TYPE_TRIGGER:
+ return "trigger";
+ }
+
+ /* should never get here */
+ pg_unreachable();
+}
+
+/*
+ * Emit error message "invalid format in filter file ..."
+ *
+ * This is mostly a convenience routine to avoid duplicating file closing code
+ * in multiple callsites.
+ */
+static void
+log_invalid_filter_format(FilterStateData *fstate, char *message)
+{
+ if (fstate->fp != stdin)
+ {
+ pg_log_error("invalid format in filter file \"%s\" on line %d: %s",
+ fstate->filename,
+ fstate->lineno,
+ message);
+ }
+ else
+ pg_log_error("invalid format in filter on line %d: %s",
+ fstate->lineno,
+ message);
+
+ fstate->is_error = true;
+}
+
+/*
+ * Emit error message "The application doesn't support filter for object type ..."
+ *
+ * This is mostly a convenience routine to avoid duplicating file closing code
+ * in multiple callsites.
+ */
+void
+log_unsupported_filter_object_type(FilterStateData *fstate,
+ const char *appname,
+ FilterObjectType fot)
+{
+ PQExpBuffer str = createPQExpBuffer();
+
+ printfPQExpBuffer(str,
+ _("\"%s\" doesn't support filter for object type \"%s\"."),
+ appname,
+ filter_object_type_name(fot));
+
+ log_invalid_filter_format(fstate, str->data);
+}
+
+/*
+ * Emit error message "exclude" or "include" filter for filter type
+ * is not allowed.
+ */
+void
+log_unallowed_filter_type(FilterStateData *fstate,
+ FilterObjectType fot,
+ bool is_include)
+{
+ PQExpBuffer str = createPQExpBuffer();
+
+ printfPQExpBuffer(str,
+ _("%s filter for \"%s\" is not allowed."),
+ is_include ? "include" : "exclude",
+ filter_object_type_name(fot));
+
+ log_invalid_filter_format(fstate, str->data);
+}
+
+/*
+ * filter_get_keyword - read the next filter keyword from buffer
+ *
+ * Search for keywords (limited to ascii alphabetic characters) in
+ * the passed in line buffer. Returns NULL when the buffer is empty or the first
+ * char is not alpha. The char '_' is allowed, except as the first character.
+ * The length of the found keyword is returned in the size parameter.
+ */
+static const char *
+filter_get_keyword(const char **line, int *size)
+{
+ const char *ptr = *line;
+ const char *result = NULL;
+
+ /* Set returnlength preemptively in case no keyword is found */
+ *size = 0;
+
+ /* Skip initial whitespace */
+ while (isspace(*ptr))
+ ptr++;
+
+ if (isalpha(*ptr))
+ {
+ result = ptr++;
+
+ while (isalpha(*ptr) || *ptr == '_')
+ ptr++;
+
+ *size = ptr - result;
+ }
+
+ *line = ptr;
+
+ return result;
+}
+
+/*
+ * read_quoted_pattern - read quoted possibly multi lined string.
+ *
+ * Returns pointer to next char after ending double quotes or NULL on error.
+ */
+static const char *
+read_quoted_string(FilterStateData *fstate,
+ const char *str,
+ PQExpBuffer pattern)
+{
+ appendPQExpBufferChar(pattern, '"');
+ str++;
+
+ while (1)
+ {
+ /*
+ * We can ignore \r or \n chars because the string is read by
+ * pg_get_line_buf, so these chars should be just trailing chars.
+ */
+ if (*str == '\r' || *str == '\n')
+ {
+ str++;
+ continue;
+ }
+
+ if (*str == '\0')
+ {
+ Assert(fstate->linebuff.data);
+
+ if (!pg_get_line_buf(fstate->fp, &fstate->linebuff))
+ {
+ if (ferror(fstate->fp))
+ {
+ pg_log_error("could not read from filter file \"%s\": %m",
+ fstate->filename);
+ fstate->is_error = true;
+ }
+ else
+ log_invalid_filter_format(fstate, _("unexpected end of file"));
+
+ return NULL;
+ }
+
+ str = fstate->linebuff.data;
+
+ appendPQExpBufferChar(pattern, '\n');
+ fstate->lineno++;
+ }
+
+ if (*str == '"')
+ {
+ appendPQExpBufferChar(pattern, '"');
+ str++;
+
+ if (*str == '"')
+ {
+ appendPQExpBufferChar(pattern, '"');
+ str++;
+ }
+ else
+ break;
+ }
+ else if (*str == '\\')
+ {
+ str++;
+ if (*str == 'n')
+ appendPQExpBufferChar(pattern, '\n');
+ else if (*str == '\\')
+ appendPQExpBufferChar(pattern, '\\');
+
+ str++;
+ }
+ else
+ appendPQExpBufferChar(pattern, *str++);
+ }
+
+ return str;
+}
+
+/*
+ * read_pattern - reads on object pattern from input
+ *
+ * This function will parse any valid identifier (quoted or not, qualified or
+ * not), which can also includes the full signature for routines.
+ * Note that this function takes special care to sanitize the detected
+ * identifier (removing extraneous whitespaces or other unnecessary
+ * characters). This is necessary as most backup/restore filtering functions
+ * only recognize identifiers if they are written exactly the same way as
+ * they are output by the server.
+ *
+ * Returns a pointer to next character after the found identifier, or NULL on
+ * error.
+ */
+static const char *
+read_pattern(FilterStateData *fstate, const char *str, PQExpBuffer pattern)
+{
+ bool skip_space = true;
+ bool found_space = false;
+
+ /* Skip initial whitespace */
+ while (isspace(*str))
+ str++;
+
+ if (*str == '\0')
+ {
+ log_invalid_filter_format(fstate, _("missing object name pattern"));
+ return NULL;
+ }
+
+ while (*str && *str != '#')
+ {
+ while (*str && !isspace(*str) && !strchr("#,.()\"", *str))
+ {
+ /*
+ * Append space only when it is allowed, and when it was found
+ * in original string.
+ */
+ if (!skip_space && found_space)
+ {
+ appendPQExpBufferChar(pattern, ' ');
+ skip_space = true;
+ }
+
+ appendPQExpBufferChar(pattern, *str++);
+ }
+
+ skip_space = false;
+
+ if (*str == '"')
+ {
+ if (found_space)
+ appendPQExpBufferChar(pattern, ' ');
+
+ str = read_quoted_string(fstate, str, pattern);
+ if (!str)
+ return NULL;
+ }
+ else if (*str == ',')
+ {
+ appendPQExpBufferStr(pattern, ", ");
+ skip_space = true;
+ str++;
+ }
+ else if (*str && strchr(".()", *str))
+ {
+ appendPQExpBufferChar(pattern, *str++);
+ skip_space = true;
+ }
+
+ found_space = false;
+
+ /* skip ending whitespaces */
+ while (isspace(*str))
+ {
+ found_space = true;
+ str++;
+ }
+ }
+
+ return str;
+}
+
+/*
+ * filter_read_item - Read command/type/pattern triplet from a filter file
+ *
+ * This will parse one filter item from the filter file, and while it is a
+ * row based format a pattern may span more than one line due to how object
+ * names can be constructed. The expected format of the filter file is:
+ *
+ * <command> <object_type> <pattern>
+ *
+ * command can be "include" or "exclude"
+ * object_type can one of: "table", "table_and_children", "schema", "foreign_data",
+ * "table_data", "table_and_children", "database", "function", "trigger" or "index"
+ * pattern can be any possibly-quoted and possibly-qualified identifier. It
+ * follows the same rules as other object include and exclude functions so it
+ * can also use wildcards.
+ *
+ * Returns true when one filter item was successfully read and parsed. When
+ * object name contains \n chars, then more than one line from input file can
+ * be processed. Returns false when the filter file reaches EOF. In case of
+ * error, the function will emit an appropriate error message before returning
+ * false.
+ */
+bool
+filter_read_item(FilterStateData *fstate,
+ bool *is_include,
+ char **objname,
+ FilterObjectType *objtype)
+{
+ Assert(!fstate->is_error);
+
+ if (pg_get_line_buf(fstate->fp, &fstate->linebuff))
+ {
+ const char *str = fstate->linebuff.data;
+ const char *keyword;
+ int size;
+ PQExpBufferData pattern;
+
+ fstate->lineno++;
+
+ /* Skip initial white spaces */
+ while (isspace(*str))
+ str++;
+
+ /*
+ * Skip empty lines or lines where the first non-whitespace character
+ * is a hash indicating a comment.
+ */
+ if (*str != '\0' && *str != '#')
+ {
+ /*
+ * First we expect sequence of two keywords, {include|exclude}
+ * followed by the object type to operate on.
+ */
+ keyword = filter_get_keyword(&str, &size);
+ if (!keyword)
+ {
+ log_invalid_filter_format(fstate,
+ _("no filter command found (expected \"include\" or \"exclude\")"));
+ return false;
+ }
+
+ if (is_keyword_str("include", keyword, size))
+ *is_include = true;
+ else if (is_keyword_str("exclude", keyword, size))
+ *is_include = false;
+ else
+ {
+ log_invalid_filter_format(fstate,
+ _("invalid filter command (expected \"include\" or \"exclude\")"));
+ return false;
+ }
+
+ keyword = filter_get_keyword(&str, &size);
+ if (!keyword)
+ {
+ log_invalid_filter_format(fstate, _("missing filter object type"));
+ return false;
+ }
+
+ if (is_keyword_str("table_data", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_TABLE_DATA;
+ else if (is_keyword_str("table_data_and_children", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN;
+ else if (is_keyword_str("database", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_DATABASE;
+ else if (is_keyword_str("foreign_data",keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_FOREIGN_DATA;
+ else if (is_keyword_str("function", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_FUNCTION;
+ else if (is_keyword_str("index", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_INDEX;
+ else if (is_keyword_str("schema", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_SCHEMA;
+ else if (is_keyword_str("table", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_TABLE;
+ else if (is_keyword_str("table_and_children", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN;
+ else if (is_keyword_str("trigger", keyword, size))
+ *objtype = FILTER_OBJECT_TYPE_TRIGGER;
+ else
+ {
+ PQExpBuffer str = createPQExpBuffer();
+
+ printfPQExpBuffer(str, _("unsupported filter object type: \"%.*s\""), size, keyword);
+ log_invalid_filter_format(fstate, str->data);
+ return false;
+ }
+
+ initPQExpBuffer(&pattern);
+
+ str = read_pattern(fstate, str, &pattern);
+ if (!str)
+ return false;
+
+ *objname = pattern.data;
+ }
+ else
+ {
+ *objname = NULL;
+ *objtype = FILTER_OBJECT_TYPE_NONE;
+ }
+
+ return true;
+ }
+
+ if (ferror(fstate->fp))
+ {
+ pg_log_error("could not read from filter file \"%s\": %m", fstate->filename);
+ fstate->is_error = true;
+ }
+
+ return false;
+}
diff --git a/src/bin/pg_dump/filter.h b/src/bin/pg_dump/filter.h
new file mode 100644
index 0000000000..9da8fd4afa
--- /dev/null
+++ b/src/bin/pg_dump/filter.h
@@ -0,0 +1,57 @@
+/*-------------------------------------------------------------------------
+ *
+ * filter.h
+ * Common header file for the parser of filter file
+ *
+ * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/bin/pg_dump/filter.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef FILTER_H
+#define FILTER_H
+
+#include "lib/stringinfo.h"
+
+/*
+ * State data for reading filter items from stream
+ */
+typedef struct
+{
+ FILE *fp;
+ const char *filename;
+ int lineno;
+ StringInfoData linebuff;
+ bool is_error;
+} FilterStateData;
+
+/*
+ * List of objects that can be specified in filter file
+ */
+typedef enum
+{
+ FILTER_OBJECT_TYPE_NONE,
+ FILTER_OBJECT_TYPE_TABLE_DATA,
+ FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN,
+ FILTER_OBJECT_TYPE_DATABASE,
+ FILTER_OBJECT_TYPE_FOREIGN_DATA,
+ FILTER_OBJECT_TYPE_FUNCTION,
+ FILTER_OBJECT_TYPE_INDEX,
+ FILTER_OBJECT_TYPE_SCHEMA,
+ FILTER_OBJECT_TYPE_TABLE,
+ FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN,
+ FILTER_OBJECT_TYPE_TRIGGER
+} FilterObjectType;
+
+extern bool filter_init(FilterStateData *fstate, const char *filename);
+extern void filter_free(FilterStateData *fstate);
+extern void log_unsupported_filter_object_type(FilterStateData *fstate,
+ const char *appname, FilterObjectType fot);
+extern void log_unallowed_filter_type(FilterStateData *fstate,
+ FilterObjectType fot, bool is_include);
+extern bool filter_read_item(FilterStateData *fstate, bool *is_include,
+ char **objname, FilterObjectType *objtype);
+
+#endif
diff --git a/src/bin/pg_dump/meson.build b/src/bin/pg_dump/meson.build
index b2fb7ac77f..0a626e6cc6 100644
--- a/src/bin/pg_dump/meson.build
+++ b/src/bin/pg_dump/meson.build
@@ -6,6 +6,7 @@ pg_dump_common_sources = files(
'compress_lz4.c',
'compress_none.c',
'dumputils.c',
+ 'filter.c',
'parallel.c',
'pg_backup_archiver.c',
'pg_backup_custom.c',
@@ -97,6 +98,7 @@ tests += {
't/002_pg_dump.pl',
't/003_pg_dump_with_server.pl',
't/004_pg_dump_parallel.pl',
+ 't/005_pg_dump_filterfile.pl',
't/010_dump_connstr.pl',
],
},
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d62780a088..5aaa5b0b35 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -59,6 +59,7 @@
#include "dumputils.h"
#include "fe_utils/option_utils.h"
#include "fe_utils/string_utils.h"
+#include "filter.h"
#include "getopt_long.h"
#include "libpq/libpq-fs.h"
#include "parallel.h"
@@ -326,6 +327,7 @@ static char *get_synchronized_snapshot(Archive *fout);
static void setupDumpWorker(Archive *AH);
static TableInfo *getRootTableInfo(const TableInfo *tbinfo);
static bool forcePartitionRootLoad(const TableInfo *tbinfo);
+static void read_dump_filters(const char *filename, DumpOptions *dopt);
int
@@ -404,6 +406,7 @@ main(int argc, char **argv)
{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
{"exclude-table-data", required_argument, NULL, 4},
{"extra-float-digits", required_argument, NULL, 8},
+ {"filter", required_argument, NULL, 15},
{"if-exists", no_argument, &dopt.if_exists, 1},
{"inserts", no_argument, NULL, 9},
{"lock-wait-timeout", required_argument, NULL, 2},
@@ -656,6 +659,10 @@ main(int argc, char **argv)
optarg);
break;
+ case 15: /* object filters from file */
+ read_dump_filters(optarg, &dopt);
+ break;
+
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -1102,6 +1109,8 @@ help(const char *progname)
" do NOT dump data for the specified table(s),\n"
" including child and partition tables\n"));
printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n"));
+ printf(_(" --filter=FILENAME dump objects and data based on the filter expressions\n"
+ " in specified file\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"
@@ -18475,3 +18484,108 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
if (!res)
pg_log_warning("could not parse %s array", "reloptions");
}
+
+/*
+ * read_dump_filters - retrieve object identifier patterns from file
+ *
+ * Parse the specified filter file for include and exclude patterns, and add
+ * them to the relevant lists. If the filename is "-" then filters will be
+ * read from STDIN rather than a file.
+ */
+static void
+read_dump_filters(const char *filename, DumpOptions *dopt)
+{
+ FilterStateData fstate;
+ bool is_include;
+ char *objname;
+ FilterObjectType objtype;
+
+ if (!filter_init(&fstate, filename))
+ exit_nicely(1);
+
+ while (filter_read_item(&fstate, &is_include, &objname, &objtype))
+ {
+ /* ignore comments and empty lines */
+ if (objtype == FILTER_OBJECT_TYPE_NONE)
+ continue;
+
+ if (objtype == FILTER_OBJECT_TYPE_TABLE_DATA)
+ {
+ if (is_include)
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ else
+ simple_string_list_append(&tabledata_exclude_patterns,
+ objname);
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN)
+ {
+ if (is_include)
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ else
+ simple_string_list_append(&tabledata_exclude_patterns_and_children,
+ objname);
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_FOREIGN_DATA)
+ {
+ if (is_include)
+ simple_string_list_append(&foreign_servers_include_patterns,
+ objname);
+ else
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_SCHEMA)
+ {
+ if (is_include)
+ {
+ simple_string_list_append(&schema_include_patterns,
+ objname);
+ dopt->include_everything = false;
+ }
+ else
+ simple_string_list_append(&schema_exclude_patterns,
+ objname);
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_TABLE)
+ {
+ if (is_include)
+ {
+ simple_string_list_append(&table_include_patterns, objname);
+ dopt->include_everything = false;
+ }
+ else
+ simple_string_list_append(&table_exclude_patterns, objname);
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN)
+ {
+ if (is_include)
+ {
+ simple_string_list_append(&table_include_patterns_and_children, objname);
+ dopt->include_everything = false;
+ }
+ else
+ simple_string_list_append(&table_exclude_patterns_and_children, objname);
+ }
+ else
+ {
+ log_unsupported_filter_object_type(&fstate, "pg_dump", objtype);
+ break;
+ }
+
+ if (objname)
+ free(objname);
+ }
+
+ filter_free(&fstate);
+
+ if (fstate.is_error)
+ exit_nicely(1);
+}
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index cd421c5944..c060d96236 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -26,6 +26,7 @@
#include "common/string.h"
#include "dumputils.h"
#include "fe_utils/string_utils.h"
+#include "filter.h"
#include "getopt_long.h"
#include "pg_backup.h"
@@ -81,6 +82,7 @@ static PGresult *executeQuery(PGconn *conn, const char *query);
static void executeCommand(PGconn *conn, const char *query);
static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns,
SimpleStringList *names);
+static void read_dumpall_filters(const char *filename, SimpleStringList *patterns);
static char pg_dump_bin[MAXPGPATH];
static const char *progname;
@@ -158,6 +160,7 @@ main(int argc, char *argv[])
{"disable-triggers", no_argument, &disable_triggers, 1},
{"exclude-database", required_argument, NULL, 6},
{"extra-float-digits", required_argument, NULL, 5},
+ {"filter", required_argument, NULL, 8},
{"if-exists", no_argument, &if_exists, 1},
{"inserts", no_argument, &inserts, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
@@ -360,6 +363,10 @@ main(int argc, char *argv[])
appendShellString(pgdumpopts, optarg);
break;
+ case 8:
+ read_dumpall_filters(optarg, &database_exclude_patterns);
+ break;
+
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -653,6 +660,7 @@ help(void)
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --exclude-database=PATTERN exclude databases whose name matches PATTERN\n"));
printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n"));
+ printf(_(" --filter=FILENAME exclude databases specified in filter file\n"));
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
@@ -1908,7 +1916,6 @@ executeCommand(PGconn *conn, const char *query)
PQclear(res);
}
-
/*
* dumpTimestamp
*/
@@ -1932,3 +1939,54 @@ hash_string_pointer(char *s)
return hash_bytes(ss, strlen(s));
}
+
+/*
+ * read_dumpall_filters - retrieve database identifier patterns from file
+ *
+ * Parse the specified filter file for include and exclude patterns, and add
+ * them to the relevant lists. If the filename is "-" then filters will be
+ * read from STDIN rather than a file.
+ *
+ * At the moment, the only allowed filter is for database exclusion.
+ */
+static void
+read_dumpall_filters(const char *filename, SimpleStringList *pattern)
+{
+ FilterStateData fstate;
+ bool is_include;
+ char *objname;
+ FilterObjectType objtype;
+
+ if (!filter_init(&fstate, filename))
+ exit_nicely(1);
+
+ while (filter_read_item(&fstate, &is_include, &objname, &objtype))
+ {
+ if (objtype == FILTER_OBJECT_TYPE_NONE)
+ continue;
+
+ if (objtype == FILTER_OBJECT_TYPE_DATABASE)
+ {
+ if (!is_include)
+ simple_string_list_append(pattern, objname);
+ else
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ }
+ else
+ {
+ log_unsupported_filter_object_type(&fstate, "pg_dumpall", objtype);
+ break;
+ }
+
+ if (objname)
+ free(objname);
+ }
+
+ filter_free(&fstate);
+
+ if (fstate.is_error)
+ exit_nicely(1);
+}
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 049a100634..71414b27a1 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -47,11 +47,13 @@
#include "dumputils.h"
#include "fe_utils/option_utils.h"
+#include "filter.h"
#include "getopt_long.h"
#include "parallel.h"
#include "pg_backup_utils.h"
static void usage(const char *progname);
+static void read_restore_filters(const char *filename, RestoreOptions *dopt);
int
main(int argc, char **argv)
@@ -123,6 +125,7 @@ main(int argc, char **argv)
{"no-publications", no_argument, &no_publications, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
{"no-subscriptions", no_argument, &no_subscriptions, 1},
+ {"filter", required_argument, NULL, 4},
{NULL, 0, NULL, 0}
};
@@ -286,6 +289,10 @@ main(int argc, char **argv)
set_dump_section(optarg, &(opts->dumpSections));
break;
+ case 4:
+ read_restore_filters(optarg, opts);
+ break;
+
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -463,6 +470,7 @@ usage(const char *progname)
printf(_(" -1, --single-transaction restore as a single transaction\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
printf(_(" --enable-row-security enable row security\n"));
+ printf(_(" --filter=FILE restore objects based on filter expressions\n"));
printf(_(" --if-exists use IF EXISTS when dropping objects\n"));
printf(_(" --no-comments do not restore comments\n"));
printf(_(" --no-data-for-failed-tables do not restore data of tables that could not be\n"
@@ -494,3 +502,105 @@ usage(const char *progname)
printf(_("Report bugs to <%s>.\n"), PACKAGE_BUGREPORT);
printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
}
+
+/*
+ * read_restore_filters - retrieve object identifier patterns from file
+ *
+ * Parse the specified filter file for include and exclude patterns, and add
+ * them to the relevant lists. If the filename is "-" then filters will be
+ * read from STDIN rather than a file.
+ */
+static void
+read_restore_filters(const char *filename, RestoreOptions *opts)
+{
+ FilterStateData fstate;
+ bool is_include;
+ char *objname;
+ FilterObjectType objtype;
+
+ if (!filter_init(&fstate, filename))
+ exit_nicely(1);
+
+ while (filter_read_item(&fstate, &is_include, &objname, &objtype))
+ {
+ /* ignore comments or empty lines */
+ if (objtype == FILTER_OBJECT_TYPE_NONE)
+ continue;
+
+ if (objtype == FILTER_OBJECT_TYPE_FUNCTION)
+ {
+ if (is_include)
+ {
+ opts->selTypes = 1;
+ opts->selFunction = 1;
+ simple_string_list_append(&opts->functionNames, objname);
+ }
+ else
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_INDEX)
+ {
+ if (is_include)
+ {
+ opts->selTypes = 1;
+ opts->selIndex = 1;
+ simple_string_list_append(&opts->indexNames, objname);
+ }
+ else
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_SCHEMA)
+ {
+ if (is_include)
+ simple_string_list_append(&opts->schemaNames, objname);
+ else
+ simple_string_list_append(&opts->schemaExcludeNames, objname);
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_TABLE)
+ {
+ if (is_include)
+ {
+ opts->selTypes = 1;
+ opts->selTable = 1;
+ simple_string_list_append(&opts->tableNames, objname);
+ }
+ else
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ }
+ else if (objtype == FILTER_OBJECT_TYPE_TRIGGER)
+ {
+ if (is_include)
+ {
+ opts->selTypes = 1;
+ opts->selTrigger = 1;
+ simple_string_list_append(&opts->triggerNames, objname);
+ }
+ else
+ {
+ log_unallowed_filter_type(&fstate, objtype, is_include);
+ break;
+ }
+ }
+ else
+ {
+ log_unsupported_filter_object_type(&fstate, "pg_restore", objtype);
+ break;
+ }
+
+ if (objname)
+ free(objname);
+ }
+
+ filter_free(&fstate);
+ if (fstate.is_error)
+ exit_nicely(1);
+}
diff --git a/src/bin/pg_dump/t/005_pg_dump_filterfile.pl b/src/bin/pg_dump/t/005_pg_dump_filterfile.pl
new file mode 100644
index 0000000000..5cb26919b8
--- /dev/null
+++ b/src/bin/pg_dump/t/005_pg_dump_filterfile.pl
@@ -0,0 +1,701 @@
+
+# Copyright (c) 2021, PostgreSQL Global Development Group
+
+use strict;
+use warnings;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More tests => 96;
+
+my $tempdir = PostgreSQL::Test::Utils::tempdir;;
+my $inputfile;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+my $port = $node->port;
+my $backupdir = $node->backup_dir;
+my $plainfile = "$backupdir/plain.sql";
+
+$node->init;
+$node->start;
+
+# Generate test objects
+$node->safe_psql('postgres', 'CREATE FOREIGN DATA WRAPPER dummy;');
+$node->safe_psql('postgres',
+ 'CREATE SERVER dummyserver FOREIGN DATA WRAPPER dummy;');
+
+$node->safe_psql('postgres', "CREATE TABLE table_one(a varchar)");
+$node->safe_psql('postgres', "CREATE TABLE table_two(a varchar)");
+$node->safe_psql('postgres', "CREATE TABLE table_three(a varchar)");
+$node->safe_psql('postgres', "CREATE TABLE table_three_one(a varchar)");
+$node->safe_psql('postgres', "CREATE TABLE footab(a varchar)");
+$node->safe_psql('postgres', "CREATE TABLE bootab() inherits (footab)");
+$node->safe_psql(
+ 'postgres', "CREATE TABLE \"strange aaa
+name\"(a varchar)");
+$node->safe_psql(
+ 'postgres', "CREATE TABLE \"
+t
+t
+\"(a int)");
+
+$node->safe_psql('postgres',
+ "INSERT INTO table_one VALUES('*** TABLE ONE ***')");
+$node->safe_psql('postgres',
+ "INSERT INTO table_two VALUES('*** TABLE TWO ***')");
+$node->safe_psql('postgres',
+ "INSERT INTO table_three VALUES('*** TABLE THREE ***')");
+$node->safe_psql('postgres',
+ "INSERT INTO table_three_one VALUES('*** TABLE THREE_ONE ***')");
+$node->safe_psql('postgres', "INSERT INTO bootab VALUES(10)");
+
+$node->safe_psql('postgres', "CREATE DATABASE sourcedb");
+$node->safe_psql('postgres', "CREATE DATABASE targetdb");
+
+$node->safe_psql('sourcedb', 'CREATE FUNCTION foo1(a int) RETURNS int AS $$ select $1 $$ LANGUAGE sql');
+$node->safe_psql('sourcedb', 'CREATE FUNCTION foo2(a int) RETURNS int AS $$ select $1 $$ LANGUAGE sql');
+$node->safe_psql('sourcedb', 'CREATE FUNCTION foo3(a double precision, b int) RETURNS double precision AS $$ select $1 + $2 $$ LANGUAGE sql');
+$node->safe_psql('sourcedb', 'CREATE FUNCTION foo_trg() RETURNS trigger AS $$ BEGIN RETURN NEW; END $$ LANGUAGE plpgsql');
+$node->safe_psql('sourcedb', 'CREATE SCHEMA s1');
+$node->safe_psql('sourcedb', 'CREATE SCHEMA s2');
+$node->safe_psql('sourcedb', 'CREATE TABLE s1.t1(a int)');
+$node->safe_psql('sourcedb', 'CREATE SEQUENCE s1.s1');
+$node->safe_psql('sourcedb', 'CREATE TABLE s2.t2(a int)');
+$node->safe_psql('sourcedb', 'CREATE TABLE t1(a int, b int)');
+$node->safe_psql('sourcedb', 'CREATE TABLE t2(a int, b int)');
+$node->safe_psql('sourcedb', 'CREATE INDEX t1_idx1 ON t1(a)');
+$node->safe_psql('sourcedb', 'CREATE INDEX t1_idx2 ON t1(b)');
+$node->safe_psql('sourcedb', 'CREATE TRIGGER trg1 BEFORE INSERT ON t1 EXECUTE FUNCTION foo_trg()');
+$node->safe_psql('sourcedb', 'CREATE TRIGGER trg2 BEFORE INSERT ON t1 EXECUTE FUNCTION foo_trg()');
+
+#
+# Test interaction of correctly specified filter file
+#
+my ($cmd, $stdout, $stderr, $result);
+
+# Empty filterfile
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "\n # a comment and nothing more\n\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "filter file without patterns");
+
+my $dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "table one dumped");
+ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "table two dumped");
+ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "table three dumped");
+ok($dump =~ qr/^CREATE TABLE public\.table_three_one/m, "table three one dumped");
+
+# Test various combinations of whitespace, comments and correct filters
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile " include table table_one #comment\n";
+print $inputfile "include table table_two\n";
+print $inputfile "# skip this line\n";
+print $inputfile "\n";
+print $inputfile "\t\n";
+print $inputfile " \t# another comment\n";
+print $inputfile "exclude table_data table_one\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with filter patterns as well as comments and whitespace");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "dumped table one");
+ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "dumped table two");
+ok($dump !~ qr/^CREATE TABLE public\.table_three/m, "table three not dumped");
+ok($dump !~ qr/^CREATE TABLE public\.table_three_one/m,
+ "table three_one not dumped");
+ok( $dump !~ qr/^COPY public\.table_one/m,
+ "content of table one is not included");
+ok($dump =~ qr/^COPY public\.table_two/m, "content of table two is included");
+
+# Test dumping tables specified by qualified names
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table public.table_one\n";
+print $inputfile "include table \"public\".\"table_two\"\n";
+print $inputfile "include table \"public\". table_three\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "filter file without patterns");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "dumped table one");
+ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "dumped table two");
+ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "dumped table three");
+
+# Test dumping all tables except one
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude table table_one\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with exclusion of a single table");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE public\.table_one/m, "table one not dumped");
+ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "dumped table two");
+ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "dumped table three");
+ok($dump =~ qr/^CREATE TABLE public\.table_three_one/m,
+ "dumped table three_one");
+
+# Test dumping tables with a wildcard pattern
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table table_thre*\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with wildcard in pattern");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE public\.table_one/m, "table one not dumped");
+ok($dump !~ qr/^CREATE TABLE public\.table_two/m, "table two not dumped");
+ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "dumped table three");
+ok($dump =~ qr/^CREATE TABLE public\.table_three_one/m,
+ "dumped table three_one");
+
+# Test dumping table with multiline quoted tablename
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table \"strange aaa
+name\"";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with multiline names requiring quoting");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public.\"strange aaa/m,
+ "dump table with new line in name");
+
+# Test excluding multiline quoted tablename from dump
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude table \"strange aaa\\nname\"";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE public.\"strange aaa/m,
+ "dump table with new line in name");
+
+# Test excluding an entire schema
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude schema public\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "exclude the public schema");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE/m, "no table dumped");
+
+# Test including and excluding an entire schema by multiple filterfiles
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include schema public\n";
+close $inputfile;
+
+open my $alt_inputfile, '>', "$tempdir/inputfile2.txt"
+ or die "unable to open filterfile for writing";
+print $alt_inputfile "exclude schema public\n";
+close $alt_inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "--filter=$tempdir/inputfile2.txt", 'postgres'
+ ],
+ "exclude the public schema with multiple filters");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE/m, "no table dumped");
+
+# Test dumping a table with a single leading newline on a row
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table \"
+t
+t
+\"";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public.\"\nt\nt\n\" \($/ms,
+ "dump table with multiline strange name");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table \"\\nt\\nt\\n\"";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump tables with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public.\"\nt\nt\n\" \($/ms,
+ "dump table with multiline strange name");
+
+#########################################
+# Test foreign_data
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include foreign_data doesnt_exists\n";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ qr/pg_dump: error: no matching foreign servers were found for pattern/,
+ "dump nonexisting foreign server");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile, "include foreign_data dummyserver\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "dump foreign_data with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE SERVER dummyserver/m, "dump foreign server");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude foreign_data dummy*\n";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ qr/exclude filter for "foreign data" is not allowed/,
+ "erroneously exclude foreign server");
+
+#########################################
+# Test broken input format
+
+# Test invalid filter command
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "k";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ qr/invalid filter command/,
+ "invalid syntax: incorrect filter command");
+
+# Test invalid object type
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include xxx";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ qr/unsupported filter object type: "xxx"/,
+ "invalid syntax: invalid object type specified, should be table, schema, foreign_data or data"
+);
+
+# Test missing object identifier pattern
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ qr/missing object name/,
+ "invalid syntax: missing object identifier pattern");
+
+# Test adding extra content after the object identifier pattern
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table table one";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ qr/no matching tables were found/,
+ "invalid syntax: extra content after object identifier pattern");
+
+#########################################
+# Combined with --strict-names
+
+# First ensure that a matching filter works
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table table_one\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ '--strict-names', 'postgres'
+ ],
+ "strict names with matching mattern");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "no table dumped");
+
+# Now append a pattern to the filter file which doesn't resolve
+open $inputfile, '>>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table table_nonexisting_name";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ '--strict-names', 'postgres'
+ ],
+ qr/no matching tables were found/,
+ "inclusion of non-existing objects with --strict names");
+
+#########################################
+# pg_dumpall tests
+
+###########################
+# Test dumping all tables except one
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude database postgres\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dumpall', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt"
+ ],
+ "dump tables with exclusion of a database");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^\\connect postgres/m, "database postgres is not dumped");
+ok($dump =~ qr/^\\connect template1/m, "database template1 is dumped");
+
+# Test invalid filter command
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "k";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dumpall', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt"
+ ],
+ qr/invalid filter command/,
+ "invalid syntax: incorrect filter command");
+
+# Test invalid object type
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude xxx";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dumpall', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt"
+ ],
+ qr/unsupported filter object type: "xxx"/,
+ "invalid syntax: exclusion of non-existing object type"
+);
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude table foo";
+close $inputfile;
+
+command_fails_like(
+ [
+ 'pg_dumpall', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt"
+ ],
+ qr/"pg_dumpall" doesn't support filter for object type "table"/,
+ "invalid syntax: exclusion of unsupported object type"
+);
+
+#########################################
+# pg_restore tests
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', "$tempdir/filter_test.dump",
+ "-Fc", 'postgres'
+ ],
+ "dump all tables");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include table table_two";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_restore', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "-Fc", "$tempdir/filter_test.dump"
+ ],
+ "restore tables with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "wanted table restored");
+ok($dump !~ qr/^CREATE TABLE public\.table_one/m, "unwanted table is not restored");
+
+#########################################
+# test restore of other objects
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', "$tempdir/filter_test.dump",
+ "-Fc", 'sourcedb'
+ ],
+ "dump all objects from sourcedb");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include function foo1(integer)";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_restore', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "-Fc", "$tempdir/filter_test.dump"
+ ],
+ "restore function with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE FUNCTION public\.foo1/m, "wanted function restored");
+ok($dump !~ qr/^CREATE TABLE public\.foo2/m, "unwanted function is not restored");
+
+# this should be white space tolerant (against the -P argument)
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include function foo3 ( double precision , integer) ";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_restore', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "-Fc", "$tempdir/filter_test.dump"
+ ],
+ "restore function with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE FUNCTION public\.foo3/m, "wanted function restored");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include index t1_idx1\n";
+
+# attention! this hit pg_restore bug - correct name of trigger is "trg1"
+# not "t1 trg1". Should be fixed when pg_restore will be fixed
+print $inputfile "include trigger t1 trg1\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_restore', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "-Fc", "$tempdir/filter_test.dump"
+ ],
+ "restore function with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE INDEX t1_idx1/m, "wanted index restored");
+ok($dump !~ qr/^CREATE INDEX t2_idx2/m, "unwanted index are not restored");
+ok($dump =~ qr/^CREATE TRIGGER trg1/m, "wanted trigger restored");
+ok($dump !~ qr/^CREATE TRIGGER trg2/m, "unwanted trigger is not restored");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "include schema s1\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_restore', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "-Fc", "$tempdir/filter_test.dump"
+ ],
+ "restore function with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE s1\.t1/m, "wanted table from schema restored");
+ok($dump =~ qr/^CREATE SEQUENCE s1\.s1/m, "wanted sequence from schema restored");
+ok($dump !~ qr/^CREATE TABLE s2\t2/m, "unwanted table is not restored");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+print $inputfile "exclude schema s1\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_restore', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt",
+ "-Fc", "$tempdir/filter_test.dump"
+ ],
+ "restore function with filter");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE s1\.t1/m, "unwanted table from schema is not restored");
+ok($dump !~ qr/^CREATE SEQUENCE s1\.s1/m, "unwanted sequence from schema is not restored");
+ok($dump =~ qr/^CREATE TABLE s2\.t2/m, "wanted table restored");
+ok($dump =~ qr/^CREATE TABLE public\.t1/m, "wanted table restored");
+
+#########################################
+# test of supported syntax
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+
+print $inputfile "include table_and_children footab\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "filter file without patterns");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.bootab/m, "dumped children table");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+
+print $inputfile "exclude table_and_children footab\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "filter file without patterns");
+
+$dump = slurp_file($plainfile);
+
+ok($dump !~ qr/^CREATE TABLE public\.bootab/m, "exclude dumped children table");
+
+open $inputfile, '>', "$tempdir/inputfile.txt"
+ or die "unable to open filterfile for writing";
+
+print $inputfile "exclude table_data_and_children footab\n";
+close $inputfile;
+
+command_ok(
+ [
+ 'pg_dump', '-p', $port, '-f', $plainfile,
+ "--filter=$tempdir/inputfile.txt", 'postgres'
+ ],
+ "filter file without patterns");
+
+$dump = slurp_file($plainfile);
+
+ok($dump =~ qr/^CREATE TABLE public\.bootab/m, "dumped children table");
+ok($dump !~ qr/^COPY public\.bootab/m, "exclude dumped children table");
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index e3ffc653e5..fca7b7d0de 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -451,6 +451,7 @@ sub mkvcbuild
$pgdumpall->AddIncludeDir('src/backend');
$pgdumpall->AddFile('src/bin/pg_dump/pg_dumpall.c');
$pgdumpall->AddFile('src/bin/pg_dump/dumputils.c');
+ $pgdumpall->AddFile('src/bin/pg_dump/filter.c');
$pgdumpall->AddLibrary('ws2_32.lib');
my $pgrestore = AddSimpleFrontend('pg_dump', 1);
--
2.40.0
