pg_dump: add option to ignore TABLE DATA for failed TABLE object creation
Hi PostgreSQL developers,
some time ago I started a discussion [1]http://archives.postgresql.org/pgsql-hackers/2006-02/msg00694.php here about modifying pg_dump
to not restore TABLE DATA objects if the corresponding TABLE oject
failed to be created (usually because it already exists, but it might
fail due to a different error like a nonexisting data type). We need
this to provide automatic major version upgrades for databases with
extensions like PostGIS. Tom's reply [3]http://archives.postgresql.org/pgsql-hackers/2006-02/msg00716.php seemed to indicate that this
was not entirely crackful, so I implemented his approach, and after
some feedback I now have a fairly clean patch that works very well.
The patch was scheduled for review and inclusion [4]http://archives.postgresql.org/pgsql-hackers/2006-02/msg01253.php, and indeed the
page had the patch for a while, but after some time it vanished.
Can you please reconsider this? If there is still a problem with the
patch, I'd like to work on it until it meets your standards.
For your convenience I attach the current patch version; a test script
[5]: http://people.debian.org/~mpitt/test-pg_restore-existing.sh
it on a Debian server). It does not alter the default behaviour, it
just adds a new option -X no-data-for-failed-tables. If you think this
mode should be the default, I'm happy to change it that way.
Thank you a lot!
Martin
[1]: http://archives.postgresql.org/pgsql-hackers/2006-02/msg00694.php
[2]: http://bugs.debian.org/351571
[3]: http://archives.postgresql.org/pgsql-hackers/2006-02/msg00716.php
[4]: http://archives.postgresql.org/pgsql-hackers/2006-02/msg01253.php
[5]: http://people.debian.org/~mpitt/test-pg_restore-existing.sh
--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
In a world without walls and fences, who needs Windows and Gates?
Attachments:
13-pg_restore-ignore-failing-tables.patchtext/plain; charset=us-asciiDownload
diff -ruN postgresql-8.1.3-old/doc/src/sgml/ref/pg_restore.sgml postgresql-8.1.3/doc/src/sgml/ref/pg_restore.sgml
--- postgresql-8.1.3-old/doc/src/sgml/ref/pg_restore.sgml 2005-11-01 22:09:50.000000000 +0100
+++ postgresql-8.1.3/doc/src/sgml/ref/pg_restore.sgml 2006-03-03 19:13:50.000000000 +0100
@@ -395,6 +395,20 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-X no-data-for-failed-tables</></term>
+ <listitem>
+ <para>
+ By default, table data objects are restored even if the
+ associated table could not be successfully created (e. g.
+ because it already exists). With this option, such table
+ data is silently ignored. This is useful for dumping and
+ restoring databases with tables which contain auxiliary data
+ for PostgreSQL extensions (e. g. PostGIS).
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
diff -ruN postgresql-8.1.3-old/src/bin/pg_dump/pg_backup_archiver.c postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c
--- postgresql-8.1.3-old/src/bin/pg_dump/pg_backup_archiver.c 2006-02-05 21:58:57.000000000 +0100
+++ postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c 2006-03-03 19:14:03.000000000 +0100
@@ -268,6 +268,23 @@
_printTocEntry(AH, te, ropt, false, false);
defnDumped = true;
+ /* If we could not create a table, ignore the respective TABLE DATA if
+ * -X no-data-for-failed-tables is given */
+ if (ropt->noDataForFailedTables && AH->lastErrorTE == te && strcmp (te->desc, "TABLE") == 0) {
+ TocEntry *tes, *last;
+
+ ahlog (AH, 1, "table %s could not be created, will not restore its data\n", te->tag);
+
+ for (last = te, tes = te->next; tes != AH->toc; last = tes, tes = tes->next) {
+ if (strcmp (tes->desc, "TABLE DATA") == 0 && strcmp (tes->tag, te->tag) == 0 &&
+ strcmp (tes->namespace ? tes->namespace : "", te->namespace ? te->namespace : "") == 0) {
+ /* remove this node */
+ last->next = tes->next;
+ break;
+ }
+ }
+ }
+
/* If we created a DB, connect to it... */
if (strcmp(te->desc, "DATABASE") == 0)
{
diff -ruN postgresql-8.1.3-old/src/bin/pg_dump/pg_backup.h postgresql-8.1.3/src/bin/pg_dump/pg_backup.h
--- postgresql-8.1.3-old/src/bin/pg_dump/pg_backup.h 2005-10-15 04:49:38.000000000 +0200
+++ postgresql-8.1.3/src/bin/pg_dump/pg_backup.h 2006-03-03 19:13:50.000000000 +0100
@@ -106,6 +106,7 @@
char *pghost;
char *username;
int ignoreVersion;
+ int noDataForFailedTables;
int requirePassword;
int exit_on_error;
diff -ruN postgresql-8.1.3-old/src/bin/pg_dump/pg_restore.c postgresql-8.1.3/src/bin/pg_dump/pg_restore.c
--- postgresql-8.1.3-old/src/bin/pg_dump/pg_restore.c 2006-03-03 19:13:48.000000000 +0100
+++ postgresql-8.1.3/src/bin/pg_dump/pg_restore.c 2006-03-03 19:13:50.000000000 +0100
@@ -254,6 +254,8 @@
use_setsessauth = 1;
else if (strcmp(optarg, "disable-triggers") == 0)
disable_triggers = 1;
+ else if (strcmp(optarg, "no-data-for-failed-tables") == 0)
+ opts->noDataForFailedTables = 1;
else
{
fprintf(stderr,
@@ -394,6 +396,8 @@
printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" -X no-data-for-failed-tables\n"
+ " do not restore data of tables which could not be created\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
You should submit to -patches; at least that it should either get
into the queue or you should get a reason why it didn't.
On Jul 17, 2006, at 4:27 PM, Martin Pitt wrote:
some time ago I started a discussion [1] here about modifying pg_dump
to not restore TABLE DATA objects if the corresponding TABLE oject
failed to be created (usually because it already exists, but it might
fail due to a different error like a nonexisting data type). We need
this to provide automatic major version upgrades for databases with
extensions like PostGIS. Tom's reply [3] seemed to indicate that this
was not entirely crackful, so I implemented his approach, and after
some feedback I now have a fairly clean patch that works very well.The patch was scheduled for review and inclusion [4], and indeed the
page had the patch for a while, but after some time it vanished.Can you please reconsider this? If there is still a problem with the
patch, I'd like to work on it until it meets your standards.For your convenience I attach the current patch version; a test script
[5] is also available (the ML kills shell script attachments, so I put
it on a Debian server). It does not alter the default behaviour, it
just adds a new option -X no-data-for-failed-tables. If you think this
mode should be the default, I'm happy to change it that way.Thank you a lot!
Martin
[1] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00694.php
[2] http://bugs.debian.org/351571
[3] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00716.php
[4] http://archives.postgresql.org/pgsql-hackers/2006-02/msg01253.php
[5] http://people.debian.org/~mpitt/test-pg_restore-existing.sh--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.orgIn a world without walls and fences, who needs Windows and Gates?
<13-pg_restore-ignore-failing-tables.patch>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Martin Pitt wrote:
-- Start of PGP signed section.
Hi PostgreSQL developers,
some time ago I started a discussion [1] here about modifying pg_dump
to not restore TABLE DATA objects if the corresponding TABLE oject
failed to be created (usually because it already exists, but it might
fail due to a different error like a nonexisting data type). We need
this to provide automatic major version upgrades for databases with
extensions like PostGIS. Tom's reply [3] seemed to indicate that this
was not entirely crackful, so I implemented his approach, and after
some feedback I now have a fairly clean patch that works very well.The patch was scheduled for review and inclusion [4], and indeed the
page had the patch for a while, but after some time it vanished.Can you please reconsider this? If there is still a problem with the
patch, I'd like to work on it until it meets your standards.For your convenience I attach the current patch version; a test script
[5] is also available (the ML kills shell script attachments, so I put
it on a Debian server). It does not alter the default behaviour, it
just adds a new option -X no-data-for-failed-tables. If you think this
mode should be the default, I'm happy to change it that way.Thank you a lot!
Martin
[1] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00694.php
[2] http://bugs.debian.org/351571
[3] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00716.php
[4] http://archives.postgresql.org/pgsql-hackers/2006-02/msg01253.php
[5] http://people.debian.org/~mpitt/test-pg_restore-existing.sh--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.orgIn a world without walls and fences, who needs Windows and Gates?
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Patch applied. Thanks.
---------------------------------------------------------------------------
Martin Pitt wrote:
-- Start of PGP signed section.
Hi PostgreSQL developers,
some time ago I started a discussion [1] here about modifying pg_dump
to not restore TABLE DATA objects if the corresponding TABLE oject
failed to be created (usually because it already exists, but it might
fail due to a different error like a nonexisting data type). We need
this to provide automatic major version upgrades for databases with
extensions like PostGIS. Tom's reply [3] seemed to indicate that this
was not entirely crackful, so I implemented his approach, and after
some feedback I now have a fairly clean patch that works very well.The patch was scheduled for review and inclusion [4], and indeed the
page had the patch for a while, but after some time it vanished.Can you please reconsider this? If there is still a problem with the
patch, I'd like to work on it until it meets your standards.For your convenience I attach the current patch version; a test script
[5] is also available (the ML kills shell script attachments, so I put
it on a Debian server). It does not alter the default behaviour, it
just adds a new option -X no-data-for-failed-tables. If you think this
mode should be the default, I'm happy to change it that way.Thank you a lot!
Martin
[1] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00694.php
[2] http://bugs.debian.org/351571
[3] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00716.php
[4] http://archives.postgresql.org/pgsql-hackers/2006-02/msg01253.php
[5] http://people.debian.org/~mpitt/test-pg_restore-existing.sh--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.orgIn a world without walls and fences, who needs Windows and Gates?
[ Attachment, skipping... ]
-- End of PGP section, PGP failed!
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +