[postgis-users] Problem moving database

[pcreso at pcreso.com]

The issue is that you are trying to install an old version of postgis into the new database.

My normal approach (yet to fail - for me at least):

create the new database
install the appropriate/current version of Postgis in that db (this may require plpgsql as well as running postgis.sql & spatial_ref_sys.sql)

dump the old db without the "create new db option" & copy the dump file to the new system

run psql -d <newdb> -f <old db dump file>
(optionally create any users you want in the new db to be compatible with the old db)

you will still get errors as it fails to install the old postgis on top of the one that is already there, but that is fine - you don't want or need the old postgis.

That should give you a working new db.

Brent Wood

--- On Sat, 9/10/11, Michael A. Peters <mpeters at shastaherps.org> wrote:

From: Michael A. Peters <mpeters at shastaherps.org>
Subject: [postgis-users] Problem moving database
To: "PostGIS Users" <postgis-users at postgis.refractions.net>
Date: Saturday, September 10, 2011, 9:00 AM

Old server:
- 32 bit CentOS 5.6
- postgresql-8.4.4-2PGDG.el5
- postgresql-server-8.4.4-2PGDG.el5
- postgis-1.3.6-1.rhel5

New server:
- 64 bit CentOS 6
- postgresql-8.4.7-1.el6_0.1.x86_64
- postgresql-server-8.4.7-1.el6_0.1.x86_64
- postgis-1.5.1-1.el6.x86_64

Dump Method:

pg_dump -Fc shastaherps > shastaherps.dump

Restore Method: (as postgres user)

sh /usr/share/postgis/postgis_restore.pl postgis-64.sql shastaherps shastaherps.dump -E=UNICODE > restore.log

Problems:

Console full of error - IE

ERROR:  language "plpgsql" already exists
ERROR:  function st_histogram2d_in(cstring) does not exist
ERROR:  could not access file "/usr/lib/pgsql/liblwgeom": No such file or directory
ERROR:  function public.asukml(geometry, integer, integer) does not exist
ERROR:  could not access file "/usr/lib/pgsql/liblwgeom": No such file or directory
ERROR:  function public.asukml(geometry, integer) does not exist
ERROR:  could not access file "/usr/lib/pgsql/liblwgeom": No such file or directory
ERROR:  function public.asukml(geometry) does not exist
ERROR:  could not access file "/usr/lib/pgsql/liblwgeom": No such file or directory
ERROR:  function public.box2d_contain(box2d, box2d) does not exist
ERROR:  could not access file "/usr/lib/pgsql/liblwgeom": No such file or directory
ERROR:  function public.box2d_contained(box2d, box2d) does not exist
ERROR:  could not access file "/usr/lib/pgsql/liblwgeom": No such file or directory

etc.

ERROR:  function public.transform_geometry(geometry, text, text, integer) does not exist
ERROR:  function geom_accum(geometry[], geometry) does not exist
ERROR:  aggregate public.geomunion(geometry) does not exist

-bash-4.1$ yum provides '/usr/lib/pgsql/liblwgeom'
Loaded plugins: fastestmirror
No Matches found

-=-=-

Any suggestions?
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110909/afd901dc/attachment.html>