Attached is an undocumented patch that allows a user to have pg_dump exclude data but not DDL for a table. One use case for this is a very large table that changes infrequently, and for which dumping data frequently would be wasteful and unnecessary. This is especially useful in conjunction with another patch (see next email) to do post-data items only or omit post-data items in pg_restore.

For those who are (like my clients :-) ) anxious to get their hands on this immediately, a backport patch is also attached which applies to 9.0 sources, and applies with offsets to 8.4 sources.

cheers

andrew
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8721e65..aad6b00 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,8 @@ static SimpleStringList table_include_patterns = {NULL, 
NULL};
 static SimpleOidList table_include_oids = {NULL, NULL};
 static SimpleStringList table_exclude_patterns = {NULL, NULL};
 static SimpleOidList table_exclude_oids = {NULL, NULL};
+static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
+static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
 
 /* default, if no "inclusion" switches appear, is to dump everything */
 static bool include_everything = true;
@@ -261,6 +263,7 @@ main(int argc, char **argv)
                {"blobs", no_argument, NULL, 'b'},
                {"clean", no_argument, NULL, 'c'},
                {"create", no_argument, NULL, 'C'},
+               {"exclude-table-data", required_argument, NULL, 'D'},
                {"file", required_argument, NULL, 'f'},
                {"format", required_argument, NULL, 'F'},
                {"host", required_argument, NULL, 'h'},
@@ -334,7 +337,7 @@ main(int argc, char **argv)
                }
        }
 
-       while ((c = getopt_long(argc, argv, 
"abcCE:f:F:h:in:N:oOp:RsS:t:T:U:vwWxX:Z:",
+       while ((c = getopt_long(argc, argv, 
"abcCD:E:f:F:h:in:N:oOp:RsS:t:T:U:vwWxX:Z:",
                                                        long_options, 
&optindex)) != -1)
        {
                switch (c)
@@ -355,6 +358,10 @@ main(int argc, char **argv)
                                outputCreateDB = 1;
                                break;
 
+                       case 'D':                       /* exclude table(s) 
data */
+                               
simple_string_list_append(&tabledata_exclude_patterns, optarg);
+                               break;
+
                        case 'E':                       /* Dump encoding */
                                dumpencoding = optarg;
                                break;
@@ -689,6 +696,10 @@ main(int argc, char **argv)
        }
        expand_table_name_patterns(&table_exclude_patterns,
                                                           &table_exclude_oids);
+
+       expand_table_name_patterns(&tabledata_exclude_patterns,
+                                                          
&tabledata_exclude_oids);
+
        /* non-matching exclusion patterns aren't an error */
 
        /*
@@ -813,6 +824,8 @@ help(const char *progname)
        printf(_("  -b, --blobs                 include large objects in 
dump\n"));
        printf(_("  -c, --clean                 clean (drop) database objects 
before recreating\n"));
        printf(_("  -C, --create                include commands to create 
database in dump\n"));
+       printf(_("  -D, --exclude-table-data=TABLE\n"
+                        "                              do NOT dump data for 
the named table(s)\n"));
        printf(_("  -E, --encoding=ENCODING     dump the data in encoding 
ENCODING\n"));
        printf(_("  -n, --schema=SCHEMA         dump the named schema(s) 
only\n"));
        printf(_("  -N, --exclude-schema=SCHEMA do NOT dump the named 
schema(s)\n"));
@@ -1012,6 +1025,15 @@ selectDumpableTable(TableInfo *tbinfo)
                simple_oid_list_member(&table_exclude_oids,
                                                           
tbinfo->dobj.catId.oid))
                tbinfo->dobj.dump = false;
+
+       /* If table is to be dumped, check that the data is not excluded */
+       if (tbinfo->dobj.dump && !
+               simple_oid_list_member(&tabledata_exclude_oids,
+                                                          
tbinfo->dobj.catId.oid))
+               tbinfo->dobj.dumpdata = true;
+       else
+               tbinfo->dobj.dumpdata = false;
+               
 }
 
 /*
@@ -1391,6 +1413,10 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
        DataDumperPtr dumpFn;
        char       *copyStmt;
 
+       /* don't do anything if the data isn't wanted */
+       if (!tbinfo->dobj.dumpdata)
+               return;
+
        if (!dump_inserts)
        {
                /* Dump/restore using COPY */
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 1dc7157..b4f5716 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -127,6 +127,7 @@ typedef struct _dumpableObject
        char       *name;                       /* object name (should never be 
NULL) */
        struct _namespaceInfo *namespace;       /* containing namespace, or 
NULL */
        bool            dump;                   /* true if we want to dump this 
object */
+       bool        dumpdata;       /* true if we want data for this object */
        DumpId     *dependencies;       /* dumpIds of objects this one depends 
on */
        int                     nDeps;                  /* number of valid 
dependencies */
        int                     allocDeps;              /* allocated size of 
dependencies[] */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index cf0fc4b..f6cd7eb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,8 @@ static SimpleStringList table_include_patterns = {NULL, NULL};
 static SimpleOidList table_include_oids = {NULL, NULL};
 static SimpleStringList table_exclude_patterns = {NULL, NULL};
 static SimpleOidList table_exclude_oids = {NULL, NULL};
+static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
+static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
 
 /* default, if no "inclusion" switches appear, is to dump everything */
 static bool include_everything = true;
@@ -289,6 +291,7 @@ main(int argc, char **argv)
 		{"blobs", no_argument, NULL, 'b'},
 		{"clean", no_argument, NULL, 'c'},
 		{"create", no_argument, NULL, 'C'},
+		{"exclude-table-data", required_argument, NULL, 'D'},
 		{"file", required_argument, NULL, 'f'},
 		{"format", required_argument, NULL, 'F'},
 		{"host", required_argument, NULL, 'h'},
@@ -366,7 +369,7 @@ main(int argc, char **argv)
 		}
 	}
 
-	while ((c = getopt_long(argc, argv, "abcCE:f:F:h:in:N:oOp:RsS:t:T:U:vwWxZ:",
+	while ((c = getopt_long(argc, argv, "abcCD:E:f:F:h:in:N:oOp:RsS:t:T:U:vwWxZ:",
 							long_options, &optindex)) != -1)
 	{
 		switch (c)
@@ -387,6 +390,10 @@ main(int argc, char **argv)
 				outputCreateDB = 1;
 				break;
 
+			case 'D':			/* exclude table(s) data */
+				simple_string_list_append(&tabledata_exclude_patterns, optarg);
+				break;
+
 			case 'E':			/* Dump encoding */
 				dumpencoding = optarg;
 				break;
@@ -718,6 +725,10 @@ main(int argc, char **argv)
 	}
 	expand_table_name_patterns(&table_exclude_patterns,
 							   &table_exclude_oids);
+
+	expand_table_name_patterns(&tabledata_exclude_patterns,
+							   &tabledata_exclude_oids);
+
 	/* non-matching exclusion patterns aren't an error */
 
 	/*
@@ -842,6 +853,8 @@ help(const char *progname)
 	printf(_("  -b, --blobs                 include large objects in dump\n"));
 	printf(_("  -c, --clean                 clean (drop) database objects before recreating\n"));
 	printf(_("  -C, --create                include commands to create database in dump\n"));
+	printf(_("  -D, --exclude-table-data=TABLE\n"
+			 "                              do NOT dump data for the named table(s)\n"));
 	printf(_("  -E, --encoding=ENCODING     dump the data in encoding ENCODING\n"));
 	printf(_("  -n, --schema=SCHEMA         dump the named schema(s) only\n"));
 	printf(_("  -N, --exclude-schema=SCHEMA do NOT dump the named schema(s)\n"));
@@ -1090,6 +1103,15 @@ selectDumpableTable(TableInfo *tbinfo)
 		simple_oid_list_member(&table_exclude_oids,
 							   tbinfo->dobj.catId.oid))
 		tbinfo->dobj.dump = false;
+
+	/* If table is to be dumped, check that the data is not excluded */
+	if (tbinfo->dobj.dump && !
+		simple_oid_list_member(&tabledata_exclude_oids,
+							   tbinfo->dobj.catId.oid))
+		tbinfo->dobj.dumpdata = true;
+	else
+		tbinfo->dobj.dumpdata = false;
+		
 }
 
 /*
@@ -1507,6 +1529,10 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
 	DataDumperPtr dumpFn;
 	char	   *copyStmt;
 
+	/* don't do anything if the data isn't wanted */
+	if (!tbinfo->dobj.dumpdata)
+		return;
+
 	if (!dump_inserts)
 	{
 		/* Dump/restore using COPY */
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 3d5d534..d6b5dd4 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -129,6 +129,7 @@ typedef struct _dumpableObject
 	char	   *name;			/* object name (should never be NULL) */
 	struct _namespaceInfo *namespace;	/* containing namespace, or NULL */
 	bool		dump;			/* true if we want to dump this object */
+	bool        dumpdata;       /* true if we want data for this object */
 	bool		ext_member;		/* true if object is member of extension */
 	DumpId	   *dependencies;	/* dumpIds of objects this one depends on */
 	int			nDeps;			/* number of valid dependencies */
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to