[postgis-users] Question Upgrading to PostgreSQL8.2 PG1.2.1

[Randy George]

Hi,

 

            I have a question on upgrading to newer version of
PostgreSQL/PostGIS on Windows

 

Using:

Windows 2003

 

Old:

postgis_full_version

"POSTGIS=""1.1.1"" GEOS=""2.2.1-CAPI-1.0.1"" PROJ=""Rel. 4.4.9, 29 Oct
2004"" USE_STATS DBPROC=""0.3.0"" RELPROC=""1.1.1"" (needs proc upgrade)"

 

New:

PostgreSQL 8.2.3 - postgresql-8.2.3-1

PostGIS 1.2.1 - postgis-pg82-setup-1.2.1-1.exe 

postgis_full_version

"POSTGIS=""1.2.1"" GEOS=""2.2.3-CAPI-1.1.1"" PROJ=""Rel. 4.5.0, 22 Oct
2006"" USE_STATS"

 

I assume that I need to do a hard upgrade. I am using the pgAdmin III
backup/restore for transferring dbs to the upgraded version.

In order to make this work I manually create each of the DBs in the new
version using the default template_postgis and then do a Restore from the
previously exported 8.1 .backup. This is a bit time consuming with several
large DBs like TIGER2005, NHD, DCW etc, but appears to work.

 

Looking over the restore log there are a number of warning/errors:

WARNING: errors ignored on restore: 364

Process returned exit code 1.

 

Most of the errors seem to be with the PostGIS functions which already exist
from create with template_postgis. For example:

pg_restore: creating FUNCTION geometry_analyze(internal)

pg_restore: [archiver (db)] Error from TOC entry 30; 1255 16421 FUNCTION
geometry_analyze(internal) postgres

pg_restore: [archiver (db)] could not execute query: ERROR:  function
"geometry_analyze" already exists with same argument types

    Command was: CREATE FUNCTION geometry_analyze(internal) RETURNS boolean

    AS '$libdir/liblwgeom.dll', 'LWGEOM_analyze'

    LANGUAGE c S...

 

In spite of the "exit code 1" the tables seem to be restored correctly
allowing my usual queries.

 

Note: I do need to add my custom spatial_ref_sys entries with sql insert,
since:

pg_restore: [archiver (db)] could not execute query: ERROR:  relation
"spatial_ref_sys" already exists

 

However, I am wondering if some issue will surface down the road due to the
ignored warning/errors? 

 

If there is a more efficient approach for transferring large DBs that I'm
missing, I'd like to know about it.

 

Also thanks for adding the asKML() function! These export functions asGML,
asSVG, asKML are really a help!

 

Thanks

randy

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070307/d3b4d3d5/attachment.html>