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?
signature.asc
Description: Digital signature
