Hi PostgreSQL developers!
On [1], Stephen and I are currently discussing how to provide seamless
automatic version upgrades of PostgreSQL databases with third party
modules like PostGIS.
The core problem is that we want to not restore objects (mainly
tables) in the destination database which already exist. pg_restore
currently offers the -L option to selectively restore only particular
objects, so our original idea was to automatically create this list
based on the output of
pg_dump -Fc --schema-only $db | pg_restore -l
for the original and target databases.
However, there is a fundamental problem with this approach. When using
--schema-only, the generated list naturally does not contain TABLE
DATA entries. This means that we cannot figure out the catalog id of
the DATA object. Of course there are workarounds [2], but both of them
are inefficient.
So my current idea is to add a new option --ignore-list to pg_restore
which specifies a file with objects that should not be restored. This
file should not contain catalog IDs, but human readable data:
type schema tag
e. g. if we know that we don't want to restore the public.foo table,
then this file would contain
TABLE public foo TABLE DATA public foo
I would be willing to provide an implementation, but before I wanted
to ask if this feature is something you would generally accept, or
regard as totally crackful?
Another way would be to add an option which specifically ignores
objects that are already present in the target database. This would be
more robust and probably easier to implement, but less general than
the ignore list.
Thank you in advance for any comment,
Martin
[1] http://bugs.debian.org/351571
[2] (1) run pg_dump without --schema-only twice (once for pg_restore -l, second time for actual restoration), or
(2)save pg_dump output into a temporary file
--
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?