Thread: [PATCH] add option to pg_dumpall to exclude tables from the dump

A new option -T --exlude-table for pg_dumpall. This option is then
passed through to the pg_dump which really does the work.
This feature can be used to exclude large tables that are known not
to change from a database backup dump so that only the changing parts
of the database are dumped.

Signed-off-by: Juergen Hannappel <juergen@juergen-hannappel.de>
---doc/src/sgml/ref/pg_dumpall.sgml | 14 ++++++++++++++src/bin/pg_dump/pg_dumpall.c     |  9 ++++++++-2 files changed,
22insertions(+), 1 deletion(-)
 

diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 6c34c25..24408b9 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -198,6 +198,20 @@ PostgreSQL documentation     </varlistentry>     <varlistentry>
+      <term><option>-T <replaceable class="parameter">table</replaceable></option></term>
+      <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term>
+      <listitem>
+       <para>
+        Do not dump any tables matching the <replaceable
+        class="parameter">table</replaceable> pattern.  The pattern is
+        interpreted according to the same rules as for <option>-t</>.
+        <option>-T</> can be given more than once to exclude tables
+        matching any of several patterns.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>      <term><option>-v</></term>      <term><option>--verbose</></term>      <listitem>
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index a7dc41c..979a964 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -111,6 +111,7 @@ main(int argc, char *argv[])        {"password", no_argument, NULL, 'W'},        {"no-privileges",
no_argument,NULL, 'x'},        {"no-acl", no_argument, NULL, 'x'},
 
+        {"exclude-table", required_argument, NULL, 'T'},        /*         * the following options don't have an
equivalentshort option letter
 
@@ -195,7 +196,7 @@ main(int argc, char *argv[])    pgdumpopts = createPQExpBuffer();
-    while ((c = getopt_long(argc, argv, "acd:f:gh:l:oOp:rsS:tU:vwWx", long_options, &optindex)) != -1)
+    while ((c = getopt_long(argc, argv, "acd:f:gh:l:oOp:rsS:tU:vwWxT:", long_options, &optindex)) != -1)    {
switch(c)        {
 
@@ -283,6 +284,11 @@ main(int argc, char *argv[])                appendPQExpBufferStr(pgdumpopts, " -x");
break;
 
+            case 'T':
+                appendPQExpBufferStr(pgdumpopts, " -T");
+                doShellQuoting(pgdumpopts,optarg);
+                break;
+            case 0:                break;
@@ -564,6 +570,7 @@ help(void)    printf(_("  -s, --schema-only            dump only the schema, no data\n"));
printf(_(" -S, --superuser=NAME         superuser user name to use in the dump\n"));    printf(_("  -t,
--tablespaces-only      dump only tablespaces, no databases or roles\n"));
 
+    printf(_("  -T, --exclude-table          exclude some tables\n"));    printf(_("  -x, --no-privileges          do
notdump privileges (grant/revoke)\n"));    printf(_("  --binary-upgrade             for use by upgrade utilities
only\n"));   printf(_("  --column-inserts             dump data as INSERT commands with column names\n"));
 
-- 
1.8.4.5

On Fri, Apr 22, 2016 at 6:42 AM, Juergen Hannappel
<juergen@juergen-hannappel.de> wrote:
> A new option -T --exlude-table for pg_dumpall. This option is then
> passed through to the pg_dump which really does the work.
> This feature can be used to exclude large tables that are known not
> to change from a database backup dump so that only the changing parts
> of the database are dumped.
>
> Signed-off-by: Juergen Hannappel <juergen@juergen-hannappel.de>

This seems like it could be useful.  Please add it to the
currently-open CommitFest so it gets reviewed at some point:

https://commitfest.postgresql.org/action/commitfest_view/open

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Juergen Hannappel <juergen@juergen-hannappel.de> writes:
> A new option -T --exlude-table for pg_dumpall. This option is then
> passed through to the pg_dump which really does the work.
> This feature can be used to exclude large tables that are known not
> to change from a database backup dump so that only the changing parts
> of the database are dumped.

This seems pretty dubious to me, in particular that the identical -T
option will be passed willy-nilly into the pg_dump runs for every
database.  That seems more likely to be a foot-gun than something useful.

Also, if we believe that this has a safe use-case, why only -T, and
not pg_dump's other object selectivity options?
        regards, tom lane

On 8/18/16 2:40 PM, Tom Lane wrote:
> This seems pretty dubious to me, in particular that the identical -T
> option will be passed willy-nilly into the pg_dump runs for every
> database.  That seems more likely to be a foot-gun than something useful.

I agree, but I think mandating a database name (which I suppose could be 
*) with the specifiers would solve that issue.

> Also, if we believe that this has a safe use-case, why only -T, and
> not pg_dump's other object selectivity options?

+1.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 8/18/16 2:40 PM, Tom Lane wrote:
>> This seems pretty dubious to me, in particular that the identical -T
>> option will be passed willy-nilly into the pg_dump runs for every
>> database.  That seems more likely to be a foot-gun than something useful.

> I agree, but I think mandating a database name (which I suppose could be 
> *) with the specifiers would solve that issue.

Hmm, something like "-T dbname1:pattern1 -T dbname2:pattern2" ?
        regards, tom lane

On 8/18/16 5:01 PM, Tom Lane wrote:
>> I agree, but I think mandating a database name (which I suppose could be
>> > *) with the specifiers would solve that issue.
> Hmm, something like "-T dbname1:pattern1 -T dbname2:pattern2" ?

Bingo. Hopefully there'd be some way to consolidate the code between the 
two as well...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Tue, Sep 6, 2016 at 9:37 PM, Gerdan Rezende dos Santos
<gerdan@gmail.com> wrote:
> After review, I realized that there is a call to the function:
> doShellQuoting (pgdumpopts, OPTARG), which no longer seems to exist ...
> After understand the code, I saw that the call is appendShellString
> (pgdumpopts, OPTARG).
>
> Follow the patches already with the necessary corrections.

This doesn't seem to take into account the discussion between Tom Lane
and Jim Nasby about how this feature should work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Sep 29, 2016 at 2:16 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Sep 6, 2016 at 9:37 PM, Gerdan Rezende dos Santos
> <gerdan@gmail.com> wrote:
>> After review, I realized that there is a call to the function:
>> doShellQuoting (pgdumpopts, OPTARG), which no longer seems to exist ...
>> After understand the code, I saw that the call is appendShellString
>> (pgdumpopts, OPTARG).
>>
>> Follow the patches already with the necessary corrections.
>
> This doesn't seem to take into account the discussion between Tom Lane
> and Jim Nasby about how this feature should work.

So, Juergen, it would be nice if you could participate in the
discussion and get a consensus on the patch. Until then, I am marking
this patch as returned with feedback.
-- 
Michael