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

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.

On Sat, Jul 14, 2012 at 1:44 PM,  <dsavolainen@visi.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