[postgis-users] how to backup/restore

[Nicholas Bower]

Thanks - I ended up with the opposite solution actually and not dumping
postgis at all.

1. All my data tables in separate schema (doesn't include postgis - that's
in default)
2. Dump just this separate data schema using pg_dump -Fc -N <schema>
3. Build new DB, install postgis manually according to docs (public schema
by default).
4. Restore the data dump

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.

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.

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).



On Mon, Apr 12, 2010 at 11:47 AM, Ben Madin
<lists at remoteinformation.com.au>wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100412/98e7c615/attachment.html>