[postgis-users] Schema migration

[James Sewell]

OK,

Looks like I will have a go at this.

I'm thinking of the following:

   1. Check all referenced postgis objects (ie: in table creation, index
   creation) for schema qualification
   2. If *--migrate-schema* is specified (no argument) then remove the
   schema
   3. If *--migrate-schema schema* is specified then replace the schema
   qualification with *schema*

This way you can either remove schema qualification (manage with
search_path OR enforce correct schema qualification.

Does anyone have any thoughts on locating PostGIS objects? I can use the
inline list but functions could prove difficult - for example determining
the difference between *function(int) *and a user defined *function(real)*?

How comfortable would people be with assuming users are not creating
non-postgis functions with clashing names?

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
______________________________________


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099



On Wed, Jun 11, 2014 at 5:31 PM, Sandro Santilli <strk at keybit.net> wrote:

> 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;
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>

-- 


------------------------------
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140617/f9f99ba4/attachment.html>