[postgis-tickets] [PostGIS] #4782: postgis_restore.pl does not handle recent pg_dump public schema change

[PostGIS]

#4782: postgis_restore.pl does not handle recent pg_dump public schema change
---------------------+---------------------------
 Reporter:  mbanck   |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 3.0.3
Component:  postgis  |    Version:  3.0.x
 Keywords:           |
---------------------+---------------------------
 If I create an empty postgis database, dump it and try to restore it again
 with postgis_restore.pl, this fails:


 {{{
 postgres at localhost:~$ createdb postgis
 postgres at localhost:~$ echo "CREATE EXTENSION postgis;" | psql postgis
 CREATE EXTENSION
 postgres at localhost:~$ pg_dump --version
 pg_dump (PostgreSQL) 10.14 (Debian 10.14-1.pgdg100+1)
 postgres at localhost:~$ pg_dump -Fc postgis > postgis.dmp
 postgres at localhost:~$
 /usr/share/postgresql/10/contrib/postgis-3.0/postgis_restore.pl
 postgis.dmp | psql postgis
 Converting postgis.dmp to ASCII on stdout...
   Reading list of functions to ignore...
   Writing manifest of things to read from dump file...
   Writing ASCII to stdout...
 Done.
 ALTER TABLE
 ALTER TABLE
 SELECT 8500
 DELETE 8500
 SET
 SET
 SET
 SET
 SET
  set_config
 ------------

 (1 row)

 SET
 SET
 SET
 SET
 CREATE EXTENSION
 COMMENT
 CREATE EXTENSION
 COMMENT
 COPY 0
 ERROR:  relation "spatial_ref_sys" does not exist
 LINE 1: UPDATE spatial_ref_sys o set auth_name = n.auth_name, auth_s...
                ^
 ERROR:  relation "spatial_ref_sys" does not exist
 LINE 1: INSERT INTO spatial_ref_sys SELECT * FROM _pgis_restore_spat...
                     ^
 DROP TABLE
 ERROR:  relation "spatial_ref_sys" does not exist
 ERROR:  relation "spatial_ref_sys" does not exist
 ERROR:  relation "spatial_ref_sys" does not exist

 postgres at localhost:~$
 }}}

 If I downgrade pg_dump to 10.2 (the last minor release that did not have
 the public schema handling change), I don't get an error:


 {{{
 postgres at localhost:~$ dropdb postgis
 postgres at localhost:~$ createdb postgis
 postgres at localhost:~$ echo "CREATE EXTENSION postgis;" | psql postgis
 CREATE EXTENSION
 postgres at localhost:~$ pg_dump --version
 pg_dump (PostgreSQL) 10.2 (Debian 10.2-1.pgdg100+1)
 postgres at localhost:~$ pg_dump -Fc postgis > postgis.dmp.2
 postgres at localhost:~$
 /usr/share/postgresql/10/contrib/postgis-3.0/postgis_restore.pl
 postgis.dmp.2 | psql postgis
 Converting postgis.dmp.2 to ASCII on stdout...
   Reading list of functions to ignore...
   Writing manifest of things to read from dump file...
   Writing ASCII to stdout...
 ALTER TABLE
 ALTER TABLE
 Done.
 SELECT 8500
 DELETE 8500
 UPDATE 0
 INSERT 0 8500
 DROP TABLE
 ALTER TABLE
 ALTER TABLE
 ALTER TABLE
 postgres at localhost:~$
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4782>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.