[postgis-users] how to backup/restore

[Robin Chauhan]

I have a database I've been maintaining for a few years, in which postgis is
in the public schema.

Is there any sane way for me to strip out the public-schema postgis
elements?

I have been upgrading postgis on this database from postgres 7.4 days, and
it works well though its crufty.  I would love to have it installed over a
fresh, clean postgis.

Thanks,
=Robin


On Sun, Apr 11, 2010 at 6:47 PM, 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/20100411/2416df62/attachment.html>