[postgis-users] postgresql8.0 and postgis1.0.0

[strk at refractions.net]

This "way" suggest that installing all postgis objects in a specific
schema will help future upgrades.

This decision is currently left to the user, also for non-availability
of schema in all supported postgresql versions.

When installing postgis on a schema-aware postgresql you might want
to create a schema for it:

	# create schema pgis;
	# SET SEARCH_PATH to pgis,public;
	# \i lwpostgis.sql
	# spatial_ref_sys.sql

--strk;

On Wed, Feb 16, 2005 at 03:32:52PM +0100, TECHER Jean David wrote:
> On December (2004), we'received an similar mail...U could try to do this:
> 
> 
> One way
> 
> In a SQL file (example toto.sql), writes this...That will help U to
> enumerate only your tables
> not postgis function
> 
> select 'pg_dump -h your_old_server_adress -p 5432 -U
> your_old_user_name -c -O -t ' || tablename || '  your_database >> dump .sql
> '
> from pg_tables  where
>  (tablename not like 'pg_%')
> and (tablename not like 'sql_%')
> (tablename not like 'spatial_ref_sys_%')
> 
> then do
> 
> psql -d your_database -f  foo.sql  > dump.sh
> ./dump.sh
> dropdb your_database
> createdb your_database
> createlang plpgsql your_database
> psql -d your_database  -f  $PGHOME/share/lwpostgis.sql
> psql -d your_database  -f  $PGHOME/share/spatial_ref_sys.sql
> psql -d your_database  -f dump.sql
> 
> ----------------------------------------------------------------
> TECHER Jean David
> Responsable Informatique 01MAP
> e-mail: davidtecher at yahoo.fr
> Bureau: 04 67 45 60 27
> Portable: 06 85 37 36 75
> site perso : http://techer.pascal.free.fr/postgis/
> site pro: http://www.01map.com/download/
> K-S:"The greatest trick the devil pulled off was convincing people he didn't
> exist"
> ------------------------------------------------------------
> ----- Original Message -----
> From: "Obe, Regina DND\MIS" <robe.dnd at cityofboston.gov>
> To: "'Pritesh Shah'" <pritesh.krish at gmail.com>; "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>; <pgsql-general at postgresql.org>
> Sent: Wednesday, February 16, 2005 3:08 PM
> Subject: RE: [postgis-users] postgresql8.0 and postgis1.0.0
> 
> 
> > The short answer is that you leave out all the postgis function calls in
> > your dump, create a new database and load the postgis functions using the
> > new scripts. Then you just load the data.
> >
> > I didn't find an easy way to selectively load tables and views using the
> > .sql format (and I actually didn't want to bring over some old junky
> tables
> > I had) so I opted for the dumping using .tar format.  Not sure if this is
> > the easiest way, but this is what I did.
> >
> > 1) Make sure you have the new postgis installed and have run the lwgeom,
> > spatial_ref.sql files in your new db
> >
> > 2) I used the new pg_dump util (from 8.0 to dump the old db) - I'm not
> sure
> > what happens if you use the old one.  Anyrate the general command was
> >
> > pg_dump -h myoldpgserver -n public -F t myolddb -f mydata.tar
> >
> > (you might need to change the above a bit if you are backing up from a
> > non-local pc that does not have a trust relationship with your old server)
> >
> > 3) pg_restore --list mydata.tar > restoreitems.txt
> > (this gives you a list of the items backuped up - which you can then edit
> to
> > only restore the stuff you want)
> >
> > 4)Edit the restoreitems.txt - cutting out all references to postgis
> > functions and any other stuff you don't want to restore
> >
> > 5) pg_restore --use-list=restoreitems.txt --dbname=mynewdb
> > --username=postgres mydata.tar
> > (here I was on the real server so had no need for the -h flag)
> >
> > -----Original Message-----
> > From: Pritesh Shah [mailto:pritesh.krish at gmail.com]
> > Sent: Wednesday, February 16, 2005 4:09 AM
> > To: pgsql-general at postgresql.org; postgis-users at postgis.refractions.net
> > Subject: [postgis-users] postgresql8.0 and postgis1.0.0
> >
> >
> > hi,
> >
> > I'm trying to backup a server and restore it on a different machine with
> > newer versions of postgresql and postgis. Dumps have been created for the
> > following versions from the old database server:
> >
> > Postgresql  7.4.6
> > Postgis       0.8.2
> >
> > Now since both the packages have released newer versions i've installed
> the
> > following on my newer machine where i would like to restore the dumped
> > databases.
> >
> > Postgresql  8.0.1 and
> > Postgis       1.0.0
> >
> > For restoring the information i'm using
> >
> > psql -e -f abc.sql template1
> >
> > While restoring the dumps i've collected the following information where
> the
> > problem occurs:
> >
> > ..
> > ..
> > ..
> > CREATE FUNCTION histogram2d_in(cstring) RETURNS histogram2d
> >     AS '$libdir/libpostgis.so.0.8', 'histogram2d_in'
> >     LANGUAGE c STRICT;
> > psql:abc.sql:3947: ERROR:  could not access file
> > "$libdir/libpostgis.so.0.8": No such file or directory
> > ..
> > ..
> > ..
> >
> > I understand that this is due to the following:
> >
> > libpostgis.so.8.0 is now liblwgeom.so.1.0 and also
> >
> > histogram2d_in  is now  lwhistogram2d_in
> > histogram2d_out is now lwhistogram2d_out and so on.
> >
> > Now my problem is there are a lot of databases that use the postgis stuff
> > (like the histogram2d_in) which has changed from the older version to the
> > newer version. What do i do to overcome this problem?? Can somebody help
> me
> > out with this??
> >
> > Cheers,
> > Pritesh
> > _______________________________________________
> > 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users