Thread: pg_dump patch: Allow -X'exclude table from dump by pattern'

pg_dump patch: Allow -X'exclude table from dump by pattern'

From
Giuseppe Tanzilli - CSF
Date:
Ciao,
I had the need to exclude tables from the dump so I made this patch,
I do something like

pg_dump -X \"Test_*\" -X \"Devel*\" test

I'm not a C guru,  but it work, the only thing I was unable to get rid
of is the dump of sequences for that table,

so I have to add -X tablename_id_seq

If you can suggest a way to work around it, I will try to fix it

hope it can be useful to the project

bye

--
-------------------------------------------------------
Giuseppe Tanzilli        g.tanzilli@gruppocsf.com
CSF Sistemi srl            phone ++39 0775 7771
Via del Ciavattino
Anagni FR
Italy



diff -u pg_dump.orig/pg_dump.c pg_dump.exclude/pg_dump.c
--- pg_dump.orig/pg_dump.c    Thu Aug 16 20:36:43 2001
+++ pg_dump.exclude/pg_dump.c    Wed Aug 22 11:20:56 2001
@@ -145,6 +145,7 @@

 #include <unistd.h>                /* for getopt() */
 #include <ctype.h>
+#include <fnmatch.h>

 #include "pg_backup.h"

@@ -204,6 +205,8 @@
 static PQExpBuffer getPKconstraint(TableInfo *tblInfo, IndInfo *indInfo);
 static const char *getAttrName(int attrnum, TableInfo *tblInfo);

+int isTableExcluded(const char *table);
+
 extern char *optarg;
 extern int    optind,
             opterr;
@@ -228,6 +231,10 @@
 char        g_comment_start[10];
 char        g_comment_end[10];

+/* exclude table from dump by patterns */
+#define MAX_EXCLUDE_TABLE_PATTERNS    32
+char       *excludetable[MAX_EXCLUDE_TABLE_PATTERNS];
+

 typedef struct _dumpContext
 {
@@ -268,6 +275,7 @@
          "  -S, --superuser=NAME     specify the superuser user name to use in plain\n"
          "                           text format\n"
       "  -t, --table=TABLE        dump for this table only (* for all)\n"
+      "  -X, --exclude-table=TABLE        exclude  this table from dump (* allowed)\n"
          "  -u, --password           use password authentication\n"
          "  -v, --verbose            verbose\n"
          "  -x, --no-acl             do not dump ACL's (grant/revoke)\n"
@@ -297,6 +305,7 @@
          "  -S NAME                  specify the superuser user name to use in plain\n"
          "                           text format\n"
       "  -t TABLE                 dump for this table only (* for all)\n"
+      "  -X TABLE                exclude  this table from dump (* allowed)\n"
          "  -u                       use password authentication\n"
          "  -v                       verbose\n"
          "  -x                       do not dump ACL's (grant/revoke)\n"
@@ -666,7 +675,7 @@
         if (tblinfo[i].sequence)/* already dumped */
             continue;

-        if (!onlytable || (strcmp(classname, onlytable) == 0) || (strlen(onlytable) == 0))
+        if ( (! isTableExcluded(classname)) && (!onlytable || (strcmp(classname, onlytable) == 0) ||
(strlen(onlytable)== 0))) 
         {
             if (g_verbose)
                 fprintf(stderr, "%s preparing to dump out the contents of Table '%s' %s\n",
@@ -750,6 +759,7 @@
         {"schema-only", no_argument, NULL, 's'},
         {"superuser", required_argument, NULL, 'S'},
         {"table", required_argument, NULL, 't'},
+        {"exclude-table", required_argument, NULL, 'X'},
         {"password", no_argument, NULL, 'u'},
         {"verbose", no_argument, NULL, 'v'},
         {"no-acl", no_argument, NULL, 'x'},
@@ -770,6 +780,13 @@

     dataOnly = schemaOnly = dumpData = attrNames = false;

+
+    {
+        int i=0;
+        for(i=0; i< MAX_EXCLUDE_TABLE_PATTERNS; i++)
+            excludetable[i] = NULL;
+    }
+
     if (!strrchr(argv[0], SEP_CHAR))
         progname = argv[0];
     else
@@ -797,9 +814,9 @@
     }

 #ifdef HAVE_GETOPT_LONG
-    while ((c = getopt_long(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uvxzZ:V?", long_options, &optindex)) != -1)
+    while ((c = getopt_long(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uvxzZ:X:V?", long_options, &optindex)) != -1)
 #else
-    while ((c = getopt(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uvxzZ:V?-")) != -1)
+    while ((c = getopt(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uvxzZ:X:V?-")) != -1)
 #endif

     {
@@ -918,6 +935,45 @@
                 }
                 break;

+            case 'X':            /* Exclude this table from dump*/
+                {
+                    int            i;
+                    char             *s;
+
+                    s = strdup(optarg);
+
+    printf("-- Exclude table:%s\n", s);
+                    /*
+                     * quoted string? Then strip quotes and preserve
+                     * case...
+                     */
+                    if (s[0] == '"')
+                    {
+                        strcpy(s, &s[1]);
+                        if (*(s + strlen(s) - 1) == '"')
+                            *(s + strlen(s) - 1) = '\0';
+                    }
+                    /* otherwise, convert table name to lowercase... */
+                    else
+                    {
+                        for (i = 0; s[i]; i++)
+                            if (isupper((unsigned char) s[i]))
+                                s[i] = tolower((unsigned char) s[i]);
+
+                    }
+
+                    // Save the exclude pattern to the exclude array
+                    i=0;
+                    while(i < MAX_EXCLUDE_TABLE_PATTERNS) {
+                        if (!excludetable[i]){
+                            excludetable[i] = s;
+                            break;
+                        } else
+                            i++;
+                    }
+                }
+                break;
+
             case 'u':
                 use_password = true;
                 break;
@@ -4057,8 +4113,8 @@
     {
         if (!(tblinfo[i].sequence))
             continue;
-        if (!tablename || (!strcmp(tblinfo[i].relname, tablename))
-            || (serialSeq && !strcmp(tblinfo[i].relname, serialSeq)))
+        if ((!isTableExcluded(tblinfo[i].relname)) && (!tablename || (!strcmp(tblinfo[i].relname, tablename))
+            || (serialSeq && !strcmp(tblinfo[i].relname, serialSeq))))
         {
             /* becomeUser(fout, tblinfo[i].usename); */
             dumpSequence(fout, tblinfo[i], schemaOnly, dataOnly);
@@ -4074,7 +4130,7 @@
         if (tblinfo[i].sequence)/* already dumped */
             continue;

-        if (!tablename || (!strcmp(tblinfo[i].relname, tablename)) || (strlen(tablename) == 0))
+        if ( (!isTableExcluded(tblinfo[i].relname)) && (!tablename || (!strcmp(tblinfo[i].relname, tablename)) ||
(strlen(tablename)== 0))) 
         {

             resetPQExpBuffer(delq);
@@ -4472,7 +4528,7 @@
             }
         }

-        if (!tablename || (strcmp(indinfo[i].indrelname, tablename) == 0) || (strlen(tablename) == 0))
+        if ( (! isTableExcluded(indinfo[i].indrelname)) && (!tablename || (strcmp(indinfo[i].indrelname, tablename) ==
0)|| (strlen(tablename) == 0))) 
         {
             resetPQExpBuffer(id1);
             resetPQExpBuffer(id2);
@@ -4844,7 +4900,7 @@

     for (i = 0; i < numTables; i++)
     {
-        if (tablename && (strcmp(tblinfo[i].relname, tablename) != 0) && (strlen(tablename) > 0))
+        if ( isTableExcluded(tblinfo[i].relname) ||  (tablename && (strcmp(tblinfo[i].relname, tablename) != 0) &&
(strlen(tablename)> 0))) 
             continue;

         for (j = 0; j < tblinfo[i].ntrig; j++)
@@ -4882,7 +4938,7 @@
      */
     for (t = 0; t < numTables; t++)
     {
-        if (tablename && (strcmp(tblinfo[t].relname, tablename) != 0) && (strlen(tablename) > 0))
+        if ( isTableExcluded(tblinfo[t].relname) ||  (tablename && (strcmp(tblinfo[t].relname, tablename) != 0) &&
(strlen(tablename)> 0))) 
             continue;

         /*
@@ -4938,4 +4994,20 @@

         PQclear(res);
     }
+}
+
+int isTableExcluded(const char *table) {
+    int check=false;
+    int i=0;
+
+    while (i < MAX_EXCLUDE_TABLE_PATTERNS && excludetable[i] ) {
+        if (! fnmatch(excludetable[i], table, FNM_PATHNAME)) {
+            check=1;
+            break;
+        }
+        i++;
+    }
+
+    return(check);
+
 }


Re: pg_dump patch: Allow -X'exclude table from dump by

From
Peter Eisentraut
Date:
Giuseppe Tanzilli - CSF writes:

> Ciao,
> I had the need to exclude tables from the dump so I made this patch,
> I do something like
>
> pg_dump -X \"Test_*\" -X \"Devel*\" test

We already have an option -t to select the table name to dump.  This could
be expanded to interpret the name as a pattern of some kind (RE or LIKE
pattern).  If you want to work on that I think no one would object.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter