[postgis-users] Problems reloading data during hard upgrade

[Mark Cave-Ayland]

On Fri, 2008-01-04 at 09:33 +0200, Toomas Aas wrote:
> Hello!
> 
> Following the instructions in PostGIS online documentation, I performed a 
> hard upgrade from PostGIS 1.1.6 to 1.3.1, at the same time also upgrading 
> PostgreSQL from 8.1.5 to 8.2.5. During reloading of databases using 
> postgis_restore.pl there were a number of errors recorded in PostgreSQL 
> log, which looked like this:
> 
> [2-1] ERROR:  current transaction is aborted, commands ignored until end of 
> transaction block
> [2-2] STATEMENT:  CREATE OR REPLACE FUNCTION spheroid_in(cstring)
> [2-3]             RETURNS spheroid
> [2-4]             AS '$libdir/liblwgeom.so.1','ellipsoid_in'
> [2-5]             LANGUAGE 'C' IMMUTABLE STRICT;
> 
> All these errors regard CREATE OR REPLACE FUNCTION statements. As there are 
> a lot of them, I haven't verified all functions in all databases, but so 
> far all the functions that I have checked actually seem to exist in the 
> database. Does this indicate some kind of problem?
> 
> Also, the restore log for each database contains two lines beginning with 
> KEEPING:
> KEEPING FUNCTION: [last_pos()]
> KEEPING CAST bool,text (see CAST)
> 
> The online documentation recommends to check the log for such lines, but 
> what I don't understand is - what, if anything, should I do about it?


Hi Toomas,

Unfortunately one of the areas in which PostGIS suffers is that the
upgrade script does not handle jumping large versions of PostgreSQL
*and* PostGIS at the same time :(

Generally for such a version change, I would recommend the following as
the "cleanest" way to upgrade:

- Create a new PostgreSQL 8.2.5 installation
- Install the latest PostGIS, and create a new spatial database
- Dump the tables/sequences from your PostgreSQL 8.1.5 installation
using pg_dump, and restore to your PostgreSQL 8.2.5 installation
  - If there are a lot of tables, pg_dump the entire database and then
    use pg_restore -L/-l options to restore only the tables/sequences
    in your database rather than any other internal objects
  - If there are a few objects, you may find it easier to use
    pg_dump -t for the individual tables/sequences

Also: you really want to install the latest 1.3.2 release rather than
1.3.1 - this version has several known bugs which are likely to cause
problems for you.


HTH,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk