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
