The following bug has been logged on the website:
Bug reference: 6738
Logged by: Dave Savolainen
Email address: dsavolainen@visi.com
PostgreSQL version: 9.1.4
Operating system: Windows XP\server 2003
Description:=20=20=20=20=20=20=20=20
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=
=20
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.
