[postgis-users] Schema migration

[Sandro Santilli]

On Wed, Jun 11, 2014 at 04:36:11PM +1000, James Sewell wrote:
> Hi All,
> 
> I'm migrating from a legacy PostGIS system to a new one, and I want to
> locate PostGIS in the postgis schema at the same time.
> 
> Most of the time this works fine, but when people have defined things like:
> 
> CREATE TABLE metro (
>     ogc_fid integer DEFAULT nextval('metro_ogc_fid_seq'::regclass) NOT NULL,
>     wkb_geometry public.geometry,
>     id numeric(6,0),
>     CONSTRAINT enforce_dims_wkb_geometry CHECK
> ((public.st_ndims(wkb_geometry) = 2)),
>     CONSTRAINT enforce_srid_wkb_geometry CHECK
> ((public.st_srid(wkb_geometry) = 4283))
> );
> 
> the whole thing falls apart, as it looks specifically for public.geometry etc...
> 
> Can anyone think of a smart way of fixing this on the fly? I'm using
> postgis_restore.pl, which means I'm using pg_restore, which means that
> I can't edit the SQL as I have a binary dump.
>
> I know I can fix manually on the source database (ALTER), or I can
> import these tabels manually - but I am hoping there is a way to
> automate this.

I think adding a switch to postgis_restore.pl could be a good idea.
The perl script parses the whole sql anyway, so it could be a way
to replace "public.<any_postgis_object>" with "<target_schema>.\1"

--migrate-postgis-schema ?

Looking forward for your patch ! :)

--strk;