Read include/exclude commands for dump/restore from file

When there is a need to filter multiple tables with include and/or exclude
options it's quite possible to run into the limitations of the commandline.
This adds a --filter=FILENAME feature to pg_dump, pg_dumpall and pg_restore
which is used to supply a file containing object exclude/include commands
which work just like their commandline counterparts. The format of the file
is one command per row like:

    <command> <object> <objectpattern>

<command> can be "include" or "exclude", <object> can be table_data, index
table_data_and_children, database, extension, foreign_data, function, table
schema, table_and_children or trigger.

This patch has gone through many revisions and design changes over a long
period of time, the list of reviewers reflect reviewers of some version of
the patch, not necessarily the final version.

Patch by Pavel Stehule with some additional hacking by me.

Author: Pavel Stehule <pavel.stehule@gmail.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: vignesh C <vignesh21@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Tomas Vondra <tomas.vondra@enterprisedb.com>
Reviewed-by: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by: Erik Rijkers <er@xs4all.nl>
Discussion: https://postgr.es/m/CAFj8pRB10wvW0CC9Xq=1XDs=zCQxer3cbLcNZa+qiX4cUH-G_A@mail.gmail.com

3 files changed, 228 insertions, 0 deletions

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 8695571045b..0e5ba4f7125 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -837,6 +837,109 @@ PostgreSQL documentation
      </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,
+        <option>-e</option>/<option>--extension</option> for extensions.
+        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 } { extension | foreign_data | table | table_and_children | table_data | table_data_and_children | schema } <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>extension</literal>: extensions, works like the
+           <option>--extension</option> option. This keyword can only be
+           used with the <literal>include</literal> keyword.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>foreign_data</literal>: data on foreign servers, works like
+           the <option>--include-foreign-data</option> option. This keyword can
+           only be used with the <literal>include</literal> keyword.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>table</literal>: tables, works like the
+           <option>-t</option>/<option>--table</option> option.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>table_and_children</literal>: tables including any partitions
+           or inheritance child tables, works like the
+           <option>--table-and-children</option> option.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>table_data</literal>: table data of any tables matching
+           <replaceable>pattern</replaceable>, works like the
+           <option>--exclude-table-data</option> 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 tables
+           matching <replaceable>pattern</replaceable> as well as any partitions
+           or inheritance children of the table(s), works like the
+           <option>--exclude-table-data-and-children</option> option. This
+           keyword can only be used with the <literal>exclude</literal> keyword.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>schema</literal>: schemas, works like the
+           <option>-n</option>/<option>--schema</option> option.
+          </para>
+         </listitem>
+        </itemizedlist>
+       </para>
+
+       <para>
+        Lines starting with <literal>#</literal> are considered comments and
+        ignored. Comments can be placed after an object pattern row 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>
        <para>
@@ -1168,6 +1271,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>.
@@ -1613,6 +1717,19 @@ CREATE DATABASE foo WITH TEMPLATE template0;
 <prompt>$</prompt> <userinput>pg_dump -t "\"MixedCaseName\"" mydb &gt; mytab.sql</userinput>
 </screen></para>
 
+  <para>
+   To dump all tables whose names start with <literal>mytable</literal>, 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 &gt; db.sql</userinput>
+</screen></para>
+
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index d31585216c6..4d7c0464687 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -126,6 +126,37 @@ PostgreSQL documentation
      </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 interpreted 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 <option>--exclude-database</option> 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>
+
+       <para>
+        Lines starting with <literal>#</literal> are considered comments and
+        ignored. Comments can be placed after an object pattern row as well.
+        Blank lines are also ignored. See <xref linkend="app-psql-patterns"/>
+        for how to perform quoting in patterns.
+       </para>
+
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><option>-g</option></term>
       <term><option>--globals-only</option></term>
       <listitem>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 374d8d8715c..1a23874da68 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -191,6 +191,86 @@ PostgreSQL documentation
      </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 interpreted according to the
+        same rules as
+        <option>-n</option>/<option>--schema</option> for including objects in schemas,
+        <option>-N</option>/<option>--exclude-schema</option>for excluding objects in schemas,
+        <option>-P</option>/<option>--function</option> for restoring named functions,
+        <option>-I</option>/<option>--index</option> for restoring named indexes,
+        <option>-t</option>/<option>--table</option> for restoring named tables
+        or <option>-T</option>/<option>--trigger</option> for restoring triggers.
+        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>
+
+       <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>function</literal>: functions, works like the
+           <option>-P</option>/<option>--function</option> option. This keyword
+           can only be used with the <literal>include</literal> keyword.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>index</literal>: indexes, works like the
+           <option>-I</option>/<option>--indexes</option> option. This keyword
+           can only be used with the <literal>include</literal> keyword.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>schema</literal>: schemas, works like the
+           <option>-n</option>/<option>--schema</option> and
+           <option>-N</option>/<option>--exclude-schema</option> options.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>table</literal>: tables, works like the
+           <option>-t</option>/<option>--table</option> option. This keyword
+           can only be used with the <literal>include</literal> keyword.
+          </para>
+         </listitem>
+         <listitem>
+          <para>
+           <literal>trigger</literal>: triggers, works like the
+           <option>-T</option>/<option>--trigger</option> option. This keyword
+           can only be used with the <literal>include</literal> keyword.
+          </para>
+         </listitem>
+        </itemizedlist>
+       </para>
+
+       <para>
+        Lines starting with <literal>#</literal> are considered comments and
+        ignored. Comments can be placed after an object pattern row as well.
+        Blank lines are also ignored. See <xref linkend="app-psql-patterns"/>
+        for how to perform quoting in patterns.
+       </para>
+
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><option>-F <replaceable class="parameter">format</replaceable></option></term>
       <term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
       <listitem>