[postgis-users] how to backup/restore

[Ben Madin]

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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users