postgresql8.0 and postgis1.0.0

You can't simply restore a dump to upgrade postgis
for two reasons:

1) postgis library name might have changed (this is the case).
2) postgis procedural language function might be changed
and you'd get the old ones.

You can find an utility script in the utils/ directory (postgis_restore.pl)
which would extract from a custom format dump (pg_dump -Fc) all
but postgis objects and restore them after having enabled a database
with new postgis objects. This is currently the preferred method
for upgrade, but has not been widely tested.

The alternative is first installing/enabling the new postgis code
and then restore the dump. This second choice will show you lots
of errors due to attempts at restoring objects that will already be
present in the database (added by new postgis enabling). Note that
doing so will also leave in dthe resulting database functions that
might have been obsoleted by newer postgis.

I suggest you try the postgis_upgrade.pl and report any problem
with it.

--strk;

On Wed, 16 Feb 2005, Pritesh Shah wrote:

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??

if these functions are internal POSGIS api, you need to remove all defintions
come from posgis.sql (match version !) from you dump, then
1. createdb dbname
2. psql dbname < posgis.sql (new version)
3. psql dbname < your_dump.sql

if these functions are part of external api, you're out of luck, you should
do search/replace in your dump, probably checking arguments.

Cheers,
Pritesh

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

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@gmail.com]
Sent: Wednesday, February 16, 2005 4:09 AM
To: pgsql-general@postgresql.org; postgis-users@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

hi,

I would like to thank everybody for their responses to my question. I
tried a couple of things and the best thing for me was to use
utils/postgis_restore.pl script.

I ran this script on some sample databases and they all seemed to
work, but partially, as there are still some errors.

These errors are because some postgis content is not being stripped
out while writing into the $dump.list file. A sample of where it fails
is:

....
....
....
CREATE FUNCTION wkb_recv(internal) RETURNS wkb
AS '$libdir/libpostgis.so.0.8', 'WKB_recv'
LANGUAGE c STRICT;
ERROR: could not access file "$libdir/libpostgis.so.0.8": No such file or direc
tory
ALTER FUNCTION public.wkb_recv(internal) OWNER TO brook;
ERROR: function public.wkb_recv(internal) does not exist
...
...
...

This line should be stripped out as in the new postgis (1.0.0) there
is nothing called "WKB_recv" which existed in the older version
(0.8.2)

There are many such cases where the postgis_restore.pl script fails in
removing the postgis stuff. I'm currently working on getting this
fixed.

Cheers,
Pritesh

On Wed, Feb 16, 2005 at 06:16:36PM -0700, Pritesh Shah wrote:

hi,

I would like to thank everybody for their responses to my question. I
tried a couple of things and the best thing for me was to use
utils/postgis_restore.pl script.

I ran this script on some sample databases and they all seemed to
work, but partially, as there are still some errors.

These errors are because some postgis content is not being stripped
out while writing into the $dump.list file. A sample of where it fails
is:

....
....
....
CREATE FUNCTION wkb_recv(internal) RETURNS wkb
AS '$libdir/libpostgis.so.0.8', 'WKB_recv'
LANGUAGE c STRICT;
ERROR: could not access file "$libdir/libpostgis.so.0.8": No such file or direc
tory
ALTER FUNCTION public.wkb_recv(internal) OWNER TO brook;
ERROR: function public.wkb_recv(internal) does not exist
...
...
...

This line should be stripped out as in the new postgis (1.0.0) there
is nothing called "WKB_recv" which existed in the older version
(0.8.2)

There are many such cases where the postgis_restore.pl script fails in
removing the postgis stuff. I'm currently working on getting this
fixed.

I've added wkb_recv and wkb_send in the list of obsoleted functions
handled by postgis_restore.pl. Could you try current (CVS) version ?
Would you send a list of other uncought obsoleted functions ?

Be aware (if you're going to patch postgis_restore.pl yourself) that
some failures are due to different output from different versions
of pg_restore.

Note finally that the errors you're getting won't hurt unless the
restore call is interrupted by the error (ie. transactional).

--strk;

Hi, Pitesh,

strk@refractions.net schrieb:

You can't simply restore a dump to upgrade postgis
for two reasons:

1) postgis library name might have changed (this is the case).
2) postgis procedural language function might be changed
and you'd get the old ones.

You can find an utility script in the utils/ directory (postgis_restore.pl)
which would extract from a custom format dump (pg_dump -Fc) all
but postgis objects and restore them after having enabled a database
with new postgis objects. This is currently the preferred method
for upgrade, but has not been widely tested.

For large databases, the attached Script (or a variation of it) may be
helpful for it. It separates schema and data dumps. If you have large
databases, only the relatively small schema dump has to be ran through
the converter, all other (potentially very large) data dumps can simply
be restored via psql. The spatial_ref_sys data dump should be completely
replaced by the spatial_ref_sys.sql coming with PostGIS.

It may also help to split of all the index creations of the schema dump
into a separate file.

The whole restore procedure should following the lines of:
- create new database
- install postgis into database, including spatial_ref_sys.sql
- install the schema dump via postgis_restore.pl
- install the geometry_columns dump (and manually drop the obsolete
columns afterwards).
- install all other dumps via psql.

HTH,
Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 z�rich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com