[postgis-users] how to backup/restore
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
Re: [postgis-users] how to backup/restore
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
Re: [postgis-users] how to backup/restore
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 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@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 > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to backup/restore
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 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 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: ERR
Re: [postgis-users] how to backup/restore
OK On 12/04/2010, at 10:03 , Nicholas Bower wrote: > 2. Dump just this separate data schema using pg_dump -Fc -N I think here you mean -n?, but it's six of one and half a dozen of the other. I routinely use different schema's for different aspects of the database, hence easier to just exclude one. > 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). I'm not sure what you mean by this question, sorry. I do change the search_path for the database - ALTER database SET search_path TO data, reference, users, gis; if that is what you are referring to? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to backup/restore
On Mon, Apr 12, 2010 at 12:21 PM, Ben Madin wrote: > OK > > On 12/04/2010, at 10:03 , Nicholas Bower wrote: > > > 2. Dump just this separate data schema using pg_dump -Fc -N > > I think here you mean -n?, but it's six of one and half a dozen of the > other. I routinely use different schema's for different aspects of the > database, hence easier to just exclude one. > Yes -n you're right. > > > 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). > > I'm not sure what you mean by this question, sorry. I do change the > search_path for the database - > >ALTER database SET search_path TO data, reference, users, gis; > > if that is what you are referring to? > Yep that's it - you're changing the search path not just of the restore, but all roles using that database ongoing so they can find the postgis functions. When I started experimenting with Postgis back in 2003, I couldn't get it to work so ever since I've used public schema for postgis. I should have tried harder ;) Btw have you restored your backups from scratch before and found them to work? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to backup/restore
Nicholas, On 12/04/2010, at 12:33 , Nicholas Bower wrote: > On Mon, Apr 12, 2010 at 12:21 PM, Ben Madin > wrote: > OK > > On 12/04/2010, at 10:03 , Nicholas Bower wrote: > > > 2. Dump just this separate data schema using pg_dump -Fc -N > > I think here you mean -n?, but it's six of one and half a dozen of the other. > I routinely use different schema's for different aspects of the database, > hence easier to just exclude one. > > Yes -n you're right. > > > > 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). > > I'm not sure what you mean by this question, sorry. I do change the > search_path for the database - > >ALTER database SET search_path TO data, reference, users, gis; > > if that is what you are referring to? > > Yep that's it - you're changing the search path not just of the restore, but > all roles using that database ongoing so they can find the postgis functions. > When I started experimenting with Postgis back in 2003, I couldn't get it to > work so ever since I've used public schema for postgis. I should have tried > harder ;) Yeah, I'm a relative newcomer to this, which may have saved me some grief, although I have once deleted the geometry type from a big database. > Btw have you restored your backups from scratch before and found them to work? Yes - pretty routinely when I dump out the database to make a local copy, but also for upgrades to PostgreSQL (most recently to 8.4). I'm actually a bit of a tech luddite, and haven't ever mastered the more technical aspects of pg_dump and pg_restore, I just dump as a script (also using the -O flag normally), tar, rsync and then run the file into psql - since much of it is on remote servers I never really learnt how to use pgadmin. I've also done it when I have deleted the Geometry type from a big database... cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
