Attached is an undocumented patch that allows pg_restore to omit
post-data items or omit all but post-data items. This has been discussed
before, and Simon sent in a patch back on 2008, which has bitrotted
some. I'm not sure why it was dropped at the time, but I think it's time
to do this. This patch relies on some infrastructure that was added
since Simon's patch, so it works a bit differently (and more simply).
So with this patch, the following three sequences should be equivalent:
pg_restore --no-post-data
pg_restore --post-data-only
pg_restore -s --no-post-data
pg_restore -a
pg_restore --post-data-only
pg_restore
This is useful and worth doing on its own, and will also add to the
usefulness of the pg_dump --exclude-table-data patch in my previous email.
As with that patch, a version that applies to version 9.0 and 8.4
sources is also attached, for the very eager.
cheers
andrew
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 5a73779..8bd45c1 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -106,6 +106,8 @@ typedef struct _restoreOptions
char *superuser; /* Username to use as superuser */
char *use_role; /* Issue SET ROLE to this */
int dataOnly;
+ int postDataOnly; /* skip all but post-data section */
+ int noPostData; /* skip post-data section */
int dropSchema;
char *filename;
int schemaOnly;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c
b/src/bin/pg_dump/pg_backup_archiver.c
index 26ee9d9..d113435 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2086,6 +2086,7 @@ ReadToc(ArchiveHandle *AH)
int depIdx;
int depSize;
TocEntry *te;
+ bool in_post_data = false;
AH->tocCount = ReadInt(AH);
AH->maxDumpId = 0;
@@ -2151,6 +2152,12 @@ ReadToc(ArchiveHandle *AH)
te->section = SECTION_PRE_DATA;
}
+ /* will stay true even for SECTION_NONE items */
+ if (te->section == SECTION_POST_DATA)
+ in_post_data = true;
+
+ te->inPostData = in_post_data;
+
te->defn = ReadStr(AH);
te->dropStmt = ReadStr(AH);
@@ -2306,6 +2313,12 @@ _tocEntryRequired(TocEntry *te, RestoreOptions *ropt,
bool include_acls)
return 0;
}
+ /* skip (all but) post data section as required */
+ if (ropt->noPostData && te->inPostData)
+ return 0;
+ if (ropt->postDataOnly && ! te->inPostData)
+ return 0;
+
if (ropt->selTypes)
{
if (strcmp(te->desc, "TABLE") == 0 ||
diff --git a/src/bin/pg_dump/pg_backup_archiver.h
b/src/bin/pg_dump/pg_backup_archiver.h
index a3a87dc..8557481 100644
--- a/src/bin/pg_dump/pg_backup_archiver.h
+++ b/src/bin/pg_dump/pg_backup_archiver.h
@@ -289,6 +289,9 @@ typedef struct _tocEntry
void *dataDumperArg; /* Arg for above routine */
void *formatData; /* TOC Entry data specific to file
format */
+ /* in post data? not quite the same as section, might be SECTION_NONE */
+ bool inPostData;
+
/* working state (needed only for parallel restore) */
struct _tocEntry *par_prev; /* list links for pending/ready items; */
struct _tocEntry *par_next; /* these are NULL if not in either list */
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index dbdf7ac..e205d6e 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -76,6 +76,8 @@ main(int argc, char **argv)
static int no_data_for_failed_tables = 0;
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ static int post_data_only = 0;
+ static int no_post_data = 0;
struct option cmdopts[] = {
{"clean", 0, NULL, 'c'},
@@ -116,7 +118,9 @@ main(int argc, char **argv)
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"role", required_argument, NULL, 2},
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
-
+ {"post-data-only", no_argument, &post_data_only, 1},
+ {"no-post-data", no_argument, &no_post_data, 1},
+
{NULL, 0, NULL, 0}
};
@@ -337,6 +341,8 @@ main(int argc, char **argv)
opts->noDataForFailedTables = no_data_for_failed_tables;
opts->noTablespace = outputNoTablespaces;
opts->use_setsessauth = use_setsessauth;
+ opts->postDataOnly = post_data_only;
+ opts->noPostData = no_post_data;
if (opts->formatName)
{
@@ -443,6 +449,9 @@ usage(const char *progname)
" created\n"));
printf(_(" --no-tablespaces do not restore tablespace
assignments\n"));
printf(_(" --role=ROLENAME do SET ROLE before restore\n"));
+ printf(_(" --no-post-data do not restore constraints,
indexes, rules, triggers\n"));
+ printf(_(" --post-data-only only restore constraints, indexes,
rules, triggers\n"));
+
printf(_(" --use-set-session-authorization\n"
" use SET SESSION
AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set
ownership\n"));
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index ce12a41..d4e2890 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -108,6 +108,8 @@ typedef struct _restoreOptions
char *superuser; /* Username to use as superuser */
char *use_role; /* Issue SET ROLE to this */
int dataOnly;
+ int postDataOnly; /* skip all but post-data section */
+ int noPostData; /* skip post-data section */
int dropSchema;
char *filename;
int schemaOnly;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index c57799e..8b90ad0 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2141,6 +2141,7 @@ ReadToc(ArchiveHandle *AH)
int depIdx;
int depSize;
TocEntry *te;
+ bool in_post_data = false;
AH->tocCount = ReadInt(AH);
AH->maxDumpId = 0;
@@ -2206,6 +2207,12 @@ ReadToc(ArchiveHandle *AH)
te->section = SECTION_PRE_DATA;
}
+ /* will stay true even for SECTION_NONE items */
+ if (te->section == SECTION_POST_DATA)
+ in_post_data = true;
+
+ te->inPostData = in_post_data;
+
te->defn = ReadStr(AH);
te->dropStmt = ReadStr(AH);
@@ -2365,6 +2372,12 @@ _tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls)
return 0;
}
+ /* skip (all but) post data section as required */
+ if (ropt->noPostData && te->inPostData)
+ return 0;
+ if (ropt->postDataOnly && ! te->inPostData)
+ return 0;
+
if (ropt->selTypes)
{
if (strcmp(te->desc, "TABLE") == 0 ||
diff --git a/src/bin/pg_dump/pg_backup_archiver.h b/src/bin/pg_dump/pg_backup_archiver.h
index 8a3a6f9..7712a42 100644
--- a/src/bin/pg_dump/pg_backup_archiver.h
+++ b/src/bin/pg_dump/pg_backup_archiver.h
@@ -287,6 +287,9 @@ typedef struct _tocEntry
void *dataDumperArg; /* Arg for above routine */
void *formatData; /* TOC Entry data specific to file format */
+ /* in post data? not quite the same as section, might be SECTION_NONE */
+ bool inPostData;
+
/* working state (needed only for parallel restore) */
struct _tocEntry *par_prev; /* list links for pending/ready items; */
struct _tocEntry *par_next; /* these are NULL if not in either list */
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 7731d25..74145fb 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -77,6 +77,8 @@ main(int argc, char **argv)
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
static int no_security_labels = 0;
+ static int post_data_only = 0;
+ static int no_post_data = 0;
struct option cmdopts[] = {
{"clean", 0, NULL, 'c'},
@@ -118,6 +120,8 @@ main(int argc, char **argv)
{"role", required_argument, NULL, 2},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
+ {"post-data-only", no_argument, &post_data_only, 1},
+ {"no-post-data", no_argument, &no_post_data, 1},
{NULL, 0, NULL, 0}
};
@@ -319,6 +323,8 @@ main(int argc, char **argv)
opts->noTablespace = outputNoTablespaces;
opts->use_setsessauth = use_setsessauth;
opts->no_security_labels = no_security_labels;
+ opts->postDataOnly = post_data_only;
+ opts->noPostData = no_post_data;
if (opts->formatName)
{
@@ -431,7 +437,10 @@ usage(const char *progname)
" do not restore data of tables that could not be\n"
" created\n"));
printf(_(" --no-security-labels do not restore security labels\n"));
- printf(_(" --no-tablespaces do not restore tablespace assignments\n"));
+ printf(_(" --no-tablespaces do not restore tablespace assignments\n"));
+ printf(_(" --no-post-data do not restore constraints, indexes, rules, triggers\n"));
+ printf(_(" --post-data-only only restore constraints, indexes, rules, triggers\n"));
+
printf(_(" --use-set-session-authorization\n"
" use SET SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers