Thanks - I ended up with the opposite solution actually and not dumping postgis at all.<div><br></div><div>1. All my data tables in separate schema (doesn't include postgis - that's in default)</div><div>2. Dump just this separate data schema using pg_dump -Fc -N <schema></div>
<div>3. Build new DB, install postgis manually according to docs (public schema by default).</div><div>4. Restore the data dump</div><div><br></div><div>Now free of errors, but yet to see it work/finish. Tends to thrash disk after a few hours, no CPU activity and no subsequent DB increase after a couple of gigs (my DB is 40G, 2G dump size). Looking at dropping constraints - indexes were already mostly absent. Probably postgresql territory not postgis though.</div>
<div><br></div><div>My conclusion - postgis internals are not capable of a standard portable dump due to observation of hard coded paths and who knows what else in there. Sort of makes sense if you consider them to be system tables. However I'm still unsure what I lose by not dumping them along with my own schema.</div>
<div><br></div><div>I note your solution of the separate schema using default path hack - interesting that this works (assume you change the search path for all db updater roles).</div><div><br></div><div><br></div><div><br>
<div class="gmail_quote">On Mon, Apr 12, 2010 at 11:47 AM, 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="margin:0 0 0 .8ex;border-left:1px #ccc solid;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></div>