Hi,
Attached is version 4. Version 3 no longer
built against head.
On 10/16/2012 09:48:06 PM, Karl O. Pinc wrote:
>
> On 09/23/2012 08:52:07 PM, Karl O. Pinc wrote:
>
> > On 09/23/2012 12:24:27 AM, Karl O. Pinc wrote:
>
> > > On 09/23/2012 12:19:07 AM, Karl O. Pinc wrote:
> > > > On 09/21/2012 10:54:05 AM, Karl O. Pinc wrote:
> > > > > On 09/20/2012 12:24:49 PM, Karl O. Pinc wrote:
> > > > >
> > > > > > I've had problems using pg_restore --data-only when
> > > > > > restoring individual schemas (which contain data which
> > > > > > has had bad things done to it). --clean does not work
> > > > > > well because of dependent objects in other schemas.
> > > >
> > > > Since there wasn't much more to do I've gone ahead
> > > > and written the patch. Works for me.
> > > >
> > > > Against git master.
> > > > Passes regression tests, but there's no regression
> > > > tests for pg_restore so this does not say much.
> > > > Since there's no regression tests I've not written one.
> > > >
> > > > Since this is a real patch for application I've given
> > > > it a new name (it's not a v2).
> > > >
> > > > Truncate done right before COPY, since that's what
> > > > the parallel restores do.
>
>
> Karl <[email protected]>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>
>
------quoted attachment------
>
> --
> Sent via pgsql-hackers mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
Karl <[email protected]>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 1b9db6b..23b0d16 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -546,6 +546,26 @@
</varlistentry>
<varlistentry>
+ <term><option>--truncate-tables</></term>
+ <listitem>
+ <para>
+ This option is only relevant when performing a data-only
+ restore. It instructs <application>pg_restore</application>
+ to execute commands to truncate the target tables while the
+ data is reloaded. Use this when restoring tables or schemas
+ and <option>--clean</option> cannot be used because dependent
+ objects would be destroyed.
+ </para>
+
+ <para>
+ The <option>--disable-triggers</option> will almost always
+ always need to be used in conjunction with this option to
+ disable check constraints on foreign keys.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--use-set-session-authorization</option></term>
<listitem>
<para>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3b49395..0aaf1d3 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -101,6 +101,8 @@ typedef struct _restoreOptions
int noTablespace; /* Don't issue tablespace-related commands */
int disable_triggers; /* disable triggers during data-only
* restore */
+ int truncate_tables; /* truncate tables during data-only
+ * restore */
int use_setsessauth;/* Use SET SESSION AUTHORIZATION commands
* instead of OWNER TO */
int no_security_labels; /* Skip security label entries */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 1fead28..c7fdc79 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -309,6 +309,11 @@ RestoreArchive(Archive *AHX)
if (ropt->createDB && ropt->single_txn)
exit_horribly(modulename, "-C and -1 are incompatible options\n");
+ /* When the schema is dropped and re-created then no point
+ * truncating tables. */
+ if (ropt->dropSchema && ropt->truncate_tables)
+ exit_horribly(modulename, "-c and --truncate-tables are incompatible options\n");
+
/*
* If we're going to do parallel restore, there are some restrictions.
*/
@@ -403,6 +408,10 @@ RestoreArchive(Archive *AHX)
}
}
+ /* Truncate tables only when restoring data. */
+ if (!ropt->dataOnly && ropt->truncate_tables)
+ exit_horribly(modulename, "--truncate-tables requires the --data-only option\n");
+
/*
* Setup the output file if necessary.
*/
@@ -562,6 +571,7 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te,
int retval = 0;
teReqs reqs;
bool defnDumped;
+ bool truncate;
AH->currentTE = te;
@@ -696,14 +706,21 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te,
* server, so no need to see if we should issue BEGIN.
*/
StartTransaction(AH);
+ truncate = 1;
+ } else
+ /* Truncate the table when asked to. */
+ truncate = ropt->truncate_tables;
+ if (truncate) {
/*
* If the server version is >= 8.4, make sure we issue
* TRUNCATE with ONLY so that child tables are not
- * wiped.
+ * wiped. If we don't know the server version
+ * then err on the side of safety.
*/
ahprintf(AH, "TRUNCATE TABLE %s%s;\n\n",
- (PQserverVersion(AH->connection) >= 80400 ?
+ ((!AH->connection ||
+ PQserverVersion(AH->connection) >= 80400) ?
"ONLY " : ""),
fmtId(te->tag));
}
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 49d799b..316fe63 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -77,6 +77,7 @@ main(int argc, char **argv)
static int disable_triggers = 0;
static int no_data_for_failed_tables = 0;
static int outputNoTablespaces = 0;
+ static int truncate_tables = 0;
static int use_setsessauth = 0;
static int no_security_labels = 0;
@@ -119,6 +120,7 @@ main(int argc, char **argv)
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"role", required_argument, NULL, 2},
{"section", required_argument, NULL, 3},
+ {"truncate-tables", no_argument, &truncate_tables, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
@@ -324,6 +326,7 @@ main(int argc, char **argv)
opts->disable_triggers = disable_triggers;
opts->noDataForFailedTables = no_data_for_failed_tables;
opts->noTablespace = outputNoTablespaces;
+ opts->truncate_tables = truncate_tables;
opts->use_setsessauth = use_setsessauth;
opts->no_security_labels = no_security_labels;
@@ -434,6 +437,7 @@ usage(const char *progname)
printf(_(" --no-security-labels do not restore security labels\n"));
printf(_(" --no-tablespaces do not restore tablespace assignments\n"));
printf(_(" --section=SECTION restore named section (pre-data, data, or post-data)\n"));
+ printf(_(" --truncate-tables truncate tables before restore\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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
