Skip site navigation (1) Skip section navigation (2)

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

From: dsavolainen(at)visi(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6738: pg_dump does not handle extensions properly/invalidpg_dump output
Date: 2012-07-14 17:44:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6738
Logged by:          Dave Savolainen
Email address:      dsavolainen(at)visi(dot)com
PostgreSQL version: 9.1.4
Operating system:   Windows XP\server 2003

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"

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


pgsql-bugs by date

Next:From: Mike WilsonDate: 2012-07-15 21:15:35
Subject: Re: BUG #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
Previous:From: Tom LaneDate: 2012-07-14 05:20:53
Subject: Re: BUG #6735: PANIC: 42501: could not open control file "global/pg_control": Permission denied

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group