Обсуждение: 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
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
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, Feb 16, 2005 at 02:09:11AM -0700, Pritesh Shah wrote:
> 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 02:09:11AM -0700, Pritesh Shah wrote:
> 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
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