The following bug has been logged on the website: Bug reference: 6738 Logged by: Dave Savolainen Email address: dsavolai...@visi.com PostgreSQL version: 9.1.4 Operating system: Windows XP\server 2003 Description:
Hello, Here is a feature that pg_dump needs: An option needs to be added to explicitly suppress or allow backing up all or selected extensions. Reason and associated bug: The current behavior may by fine with extensions that do not add or change tables, schemas, functions, etc when installed, however some extensions such as postgis does. Restoring to a spatially enabled database will cause errors when the postgis provided rules and schema "topology" is encountered and probably the postgis provided public.spatial_ref_sys table (see ancilliary bug #3). Restoring to a non-spatially enabled database does work since pg_restore does install the postgis extension and spatially enable the database. However pg_restore still throws errors over already existing rules created by postgis as earlier initiated by pg_restore. Ancilliary bug#1 : pg_dump dumps the postgis table data public.spatial_ref_sys. This cannot be suppressed with the --schema-only flag. This may be a problem if the restored database has an upgraded postgis and the public.spatial_ref_sys table has been updated in some way. The newer table may be overwritten with old data. Ancilliary bug#2: I was unable to test the above since pg_restore made no apparant attempt to restore public.spatial_ref_sys, at least there was no mention at all listed in the pg_restore output of any attempt to restore that table even though that table had been dumped in violation of the --schema-only flag. The backup was in tar format created as follows: pg_dump.exe --host localhost --port 5432 --username "postgres" --format tar --verbose --schema-only --file "C:\other\postgres-bak\transfer\data.backup" oec Ancilliary bug#3: pg_restore was unable to restore an otherwise identical plain text version of the backup file generated by: pg_dump.exe --host localhost --port 5432 --username "postgres" --format plain --verbose --schema-only --file "C:\other\postgres-bak\transfer\data.backup" oec pg_restore output is: pg_restore: [archiver] input file does not appear to be a valid archive If this particular set of options normally does create an invalid backup file, the manual is not clear about it. System specs are as follows: Source database cluster hosted on Windows server 2003 accessed via ssh tunnel Database version: 9.1.4 Postgis version 2.0.0 Backup files restored to database cluster hosted on Windows XP Database version 9.1.4 Postgis version 2.0.1 pg_dump and pg_restore are run on the Windows XP machine and belong to the database installed on that machine. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs