Nicholas, 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:
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) 2. when dumping, ignore the gis schema = pg-dump -N gis database > database.dump 3. when recreating, create new database, import postgis (as in 1) then restore database.dump (psql newdatabase < database.dump) Or some variant of the above - and I'd love to know / be shown a better way! 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. cheers Ben On 09/04/2010, at 21:20 , Nicholas Bower wrote: > 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 > > [Solaris Postgis 1.3.4] pg_dump -scFc database > schema.sql > > [Windows Postgis 1.5.1] pgrestore -d database schema.sql > > End result - lots of errors and missing any tables containing postgis > objects. Note the hard coded c language function library paths below; > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 6; 2615 18201 SCHEMA wastac > wastacad > pg_restore: [archiver (db)] could not execute query: ERROR: schema "wastac" > already exists > Command was: > CREATE SCHEMA wastac; > pg_restore: [archiver (db)] Error from TOC entry 1223; 2612 17398 PROCEDURAL > LANGUAGE plpgsql postgres > pg_restore: [archiver (db)] could not execute query: ERROR: language > "plpgsql" > already exists > Command was: CREATE PROCEDURAL LANGUAGE plpgsql; > pg_restore: [archiver (db)] Error from TOC entry 1115; 0 0 SHELL TYPE box2d > postgres > pg_restore: [archiver (db)] could not execute query: ERROR: type "box2d" > already exists > Command was: CREATE TYPE box2d; > pg_restore: [archiver (db)] Error from TOC entry 216; 1255 17477 FUNCTION > st_box2d_in(cstring) postgres > pg_restore: [archiver (db)] could not execute query: ERROR: permission > denied for language c > Command was: CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d > AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_in' > LAN... > pg_restore: [archiver (db)] could not execute query: ERROR: function > public.st_box2d_in(cstring) does not exist > Command was: ALTER FUNCTION public.st_box2d_in(cstring) OWNER TO postgres; > pg_restore: [archiver (db)] Error from TOC entry 218; 1255 17479 FUNCTION > st_box2d_out(box2d) postgres > pg_restore: [archiver (db)] could not execute query: ERROR: permission > denied for language c > Command was: CREATE FUNCTION st_box2d_out(box2d) RETURNS cstring > AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_out' > L... > pg_restore: [archiver (db)] could not execute query: ERROR: function > public.st_box2d_out(box2d) does not exist > Command was: ALTER FUNCTION public.st_box2d_out(box2d) OWNER TO postgres; > pg_restore: [archiver (db)] Error from TOC entry 1114; 1247 17475 TYPE box2d > postgres > > ... > > pg_restore: [archiver (db)] could not execute query: ERROR: relation > "t_tile_geometry" does not exist > Command was: GRANT SELECT ON TABLE t_tile_geometry TO wastacportal; > pg_restore: [archiver (db)] could not execute query: ERROR: relation > "t_tile_geometry" does not exist > Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE t_tile_geometry > TO wastac; > WARNING: errors ignored on restore: 1586 > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
