Re: Adding an ignore list to pg_restore, prototype patch #1

Hi again,

Tom Lane [2006-02-18 14:34 -0500]:
> >>> The core problem is that we want to not restore objects (mainly
> >>> tables) in the destination database which already exist.
> >>
> >> Why is this a problem?  It's already the default behavior --- the
> >> creation commands fail but pg_restore keeps going.
>
> > The problem is that pg_restore would restore the TABLE DATA object,
> > although we don't want that (the postgis specific tables are
> > pre-populated by PostGIS itself, and should not be altered by the
> > upgrade.
>
> Hm.  Rather than a variant of the -L facility (which is hard to use,
> and I don't see your proposal being much easier), maybe what's wanted
> is just a flag saying "don't try to restore data into any table whose
> creation command fails".  Maybe that should even be the default ...
> and you could extend it to indexes and constraints on such tables too,
> as those would likely end up being duplicated as well.

My first stab at this is a patch which only does the minimal changes,
just to get me going. If the restoration of a TABLE object fails, it
marks the corresponding TABLE DATA object as to be ignored. Do you
think the current patch is a valid approach?

Since this changes the behaviour of pg_restore, this should probably
become an option, e. g. -D / --ignore-existing-table-data. I'll do
this if you agree to the principle of the current patch.

For convenience, I wrote a small test script which demonstrates the
behaviour. The table 'userdata' should be restored, while the table
'auxdata' is already present in the destination db, and its contents
should not be modified.

Output with pg_restore from 8.1.3:
------------------- snip ------------------------
$ LC_ALL=C sudo -u postgres ./test-pg_restore-existing.sh
=== create empty databases ===
=== populating old database ===
=== pre-creating auxdata in new database ===
=== restoring old to new ===
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating TABLE auxdata
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1183; 1259 17184 TABLE auxdata postgres
pg_restore: [archiver (db)] could not execute query: FEHLER:  Relation »auxdata« existiert bereits
    Command was: CREATE TABLE auxdata (
    x integer
);
pg_restore: creating TABLE userdata
pg_restore: restoring data for table "auxdata"
pg_restore: restoring data for table "userdata"
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for TABLE auxdata
pg_restore: setting owner and privileges for TABLE userdata
WARNING: errors ignored on restore: 1
pg_restore failed with 1
=== new/userdata: ===
42
256
=== new/auxdata: ===
-1
-2
1
2
------------------- snip ------------------------

Output with patched pg_restore:
------------------- snip ------------------------
$ LC_ALL=C sudo -u postgres ./test-pg_restore-existing.sh
=== create empty databases ===
=== populating old database ===
=== pre-creating auxdata in new database ===
=== restoring old to new ===
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating TABLE auxdata
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1183; 1259 17194 TABLE auxdata postgres
pg_restore: [archiver (db)] could not execute query: FEHLER:  Relation »auxdata« existiert bereits
    Command was: CREATE TABLE auxdata (
    x integer
);
pg_restore: table auxdata could not be created, will not restore its data
pg_restore: creating TABLE userdata
pg_restore: restoring data for table "userdata"
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for TABLE auxdata
pg_restore: setting owner and privileges for TABLE userdata
WARNING: errors ignored on restore: 1
pg_restore failed with 1
=== new/userdata: ===
42
256
=== new/auxdata: ===
-1
-2
------------------- snip ------------------------

Thus, with the patch, auxdata is not restored (which produced the
additional entries '1' and '2').

Thanks,

Martin

--
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?