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

[TECHER Jean David]

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