[postgis-users] how to backup/restore

[Nicholas Bower]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100409/0446133d/attachment.html>