Migration from postgresql 8.4 to postgresql 9.0, postgis 1.5 involved

Question

Im migrating from 8.4 to 9.0, in pg8.4 i have postgis 1.5.1 and in 9.0 im just installed 1.5.2.

I decided to dump the postgis databases before the pg_upgrade command, and delete them, planning to create my new postgis_template and then create the databases:

pg_dump -C -h host -U postgres -f databasegis.dump databasegis
psql -U postgres < databasegis.dump

But I have errors :

ERROR:  could not access file "$libdir/liblwgeom": No such file or directory
ERROR:  function public.st_text(boolean) does not exist

What can i do?

Thanks a lot.

Answer 1

This method worked.

1. Create a new database.
2. Install PostGIS 1.5 (the install files should be in share/contrib/postgis-1.5).
3. Then restore your data on top.
4. You should then get errors that the functions already exist and they will be skipped. You may get some errors too about the liblwgeom, but those you can ignore since they represent functions long ago deprecated that got taken out of postgis-1.5.

Answer 2

I've just find the best method, I used a script in postgis package called /usr/share/postgresql-9.0-postgis/utils/new_postgis_restore.pl This path my be different in your systems but only got a few duplicate primary key errors.

Answer 3

liblwgeom was renamed to postgis in version 1.4. Are you sure you are updating from 1.5? If you are migrating a major version you should use a 'hard upgrade': http://www.postgis.org/documentation/manual-1.5/ch02.html#upgrading

Answer 4

PostGIS wiki helped me to do a clean hard upgrade from postgresql 8.3 with postgis 1.3.3 to postgresql 9.2 with postgis 2.0 http://trac.osgeo.org/postgis/wiki/UsersWikiWinUpgrade