Re: BUG #6738: pg_dump does not handle extensions properly/invalid pg_dump output

On Sat, Jul 14, 2012 at 1:44 PM, <dsavolainen(at)visi(dot)com> wrote:
> 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.

I agree.

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

But this sounds like a problem with the PostGIS extension doing things
improperly, rather than a problem with the extension mechanism itself.
More specifics would be helpful.

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

The extension mechanism is designed in such a way that extensions can
declare which tables should be dumped in their entirety or just
partially. Sounds like something isn't right here.

> 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

That sounds like a bug, assuming someone else can reproduce it.

> 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 only handles custom and tar format backups. Plain format
backups can just be fed to psql. Maybe we could give a nicer error
message, but this isn't a bug.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company