I have a database I've been maintaining for a few years, in which postgis is in the public schema. <br><br>Is there any sane way for me to strip out the public-schema postgis elements?<br><br>I have been upgrading postgis on this database from postgres 7.4 days, and it works well though its crufty. I would love to have it installed over a fresh, clean postgis.<br>
<br>Thanks,<br>=Robin<br><br><br><div class="gmail_quote">On Sun, Apr 11, 2010 at 6:47 PM, Ben Madin <span dir="ltr"><<a href="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Nicholas,<br>
<br>
I can't answer 'What's the right way?', as I'm sure I don't have it yet, but I have found the biggest issue is in getting postgis to transfer, so I do it in steps:<br>
<br>
1. always install postgis into it's own schema (either create the schema - gis - and set the search-path before \i postgis.sql command, or edit the postgis.sql file)<br>
<br>
2. when dumping, ignore the gis schema = pg-dump -N gis database > database.dump<br>
<br>
3. when recreating, create new database, import postgis (as in 1) then restore database.dump (psql newdatabase < database.dump)<br>
<br>
Or some variant of the above - and I'd love to know / be shown a better way!<br>
<br>
As a side benefit (If it helps,) I also use the gis schema for reasonably static GIS data - background maps etc. Then my backups don't include it, and are often much smaller for it, which helps going between machines.<br>
<br>
cheers<br>
<br>
Ben<br>
<div><div></div><div class="h5"><br>
<br>
<br>
<br>
On 09/04/2010, at 21:20 , Nicholas Bower wrote:<br>
<br>
> I'm trying to create a new instance on a different platform of a postgis-enabled database, starting with just the schema definition. What's the right way? This doesn't work below - do I have to partition into separate schemas to have this work perhaps? Thanks, Nick<br>
><br>
> [Solaris Postgis 1.3.4] pg_dump -scFc database > schema.sql<br>
><br>
> [Windows Postgis 1.5.1] pgrestore -d database schema.sql<br>
><br>
> End result - lots of errors and missing any tables containing postgis objects. Note the hard coded c language function library paths below;<br>
><br>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:<br>
> pg_restore: [archiver (db)] Error from TOC entry 6; 2615 18201 SCHEMA wastac wastacad<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: schema "wastac" already exists<br>
> Command was:<br>
> CREATE SCHEMA wastac;<br>
> pg_restore: [archiver (db)] Error from TOC entry 1223; 2612 17398 PROCEDURAL LANGUAGE plpgsql postgres<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql"<br>
> already exists<br>
> Command was: CREATE PROCEDURAL LANGUAGE plpgsql;<br>
> pg_restore: [archiver (db)] Error from TOC entry 1115; 0 0 SHELL TYPE box2d postgres<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: type "box2d" already exists<br>
> Command was: CREATE TYPE box2d;<br>
> pg_restore: [archiver (db)] Error from TOC entry 216; 1255 17477 FUNCTION st_box2d_in(cstring) postgres<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c<br>
> Command was: CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d<br>
> AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_in'<br>
> LAN...<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_in(cstring) does not exist<br>
> Command was: ALTER FUNCTION public.st_box2d_in(cstring) OWNER TO postgres;<br>
> pg_restore: [archiver (db)] Error from TOC entry 218; 1255 17479 FUNCTION st_box2d_out(box2d) postgres<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c<br>
> Command was: CREATE FUNCTION st_box2d_out(box2d) RETURNS cstring<br>
> AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_out'<br>
> L...<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_out(box2d) does not exist<br>
> Command was: ALTER FUNCTION public.st_box2d_out(box2d) OWNER TO postgres;<br>
> pg_restore: [archiver (db)] Error from TOC entry 1114; 1247 17475 TYPE box2d postgres<br>
><br>
> ...<br>
><br>
> pg_restore: [archiver (db)] could not execute query: ERROR: relation "t_tile_geometry" does not exist<br>
> Command was: GRANT SELECT ON TABLE t_tile_geometry TO wastacportal;<br>
> pg_restore: [archiver (db)] could not execute query: ERROR: relation "t_tile_geometry" does not exist<br>
> Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE t_tile_geometry TO wastac;<br>
> WARNING: errors ignored on restore: 1586<br>
</div></div>> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br><br>