[postgis-users] Proper way to upgrade after using CREATE EXTENSION postgis?

2012-06-25 Thread Charlie Sharpsteen 
Hello all,

I installed PostGIS 2.0 and added it to a database using:

CREATE EXTENSION postgis;


Today, I built and installed 2.0.1 and tried to upgrade the extension using:

ALTER EXTENSION postgis UPDATE;


However, this failed with:

ERROR:  cannot drop cast from geometry to geometry because extension 
postgis requires it
HINT:  You can drop extension postgis instead.


So, I tried the old school way of running the upgrade scripts:

psql -f /usr/local/share/postgis/postgis_upgrade_20_minor.sql 
hum_bay_gis


But this also fails:


BEGIN
CREATE FUNCTION
   postgis_major_version_check
--
 Scripts versions checked for upgrade: ok
(1 row)

DROP FUNCTION
psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:46: ERROR: 
 cannot drop function addgeometrycolumn(character varying,character 
varying,character varying,character varying,integer,character 
varying,integer,boolean) because extension postgis requires it
HINT:  You can drop extension postgis instead.
psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:47: ERROR: 
 current transaction is aborted, commands ignored until end of transaction 
block
...(thousands of similar errors)...
ROLLBACK


So, it appears there is no way to upgrade PostGIS without running `DROP 
EXTENSION postgis` which would cascade to all spatially-enabled tables.

Am I missing a step or is there a circular dependency here that is breaking 
the upgrade process?


-Charlie
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Proper way to upgrade after using CREATE EXTENSION postgis?

2012-06-25 Thread Paragon Corporation 
It worked fine for me upgrading from 2.0.0 to 2.0.1.
I used:
ALTER EXTENSION postgis UPDATE TO 2.0.1;   

Which version of PostGIS are you using.

do a 

SELECT postgis_full_version()l

When all fails, to fix your database.

1) Backup your database
2) Restore it.

It should automatically upgrade itself to the new version.



Hope that helps,
Regina
http://www.postgis.us




 -Original Message-
 From: postgis-users-boun...@postgis.refractions.net 
 [mailto:postgis-users-boun...@postgis.refractions.net] On 
 Behalf Of Sandro Santilli
 Sent: Monday, June 25, 2012 3:09 PM
 To: PostGIS Users Discussion
 Cc: postgis-us...@googlegroups.com
 Subject: Re: [postgis-users] Proper way to upgrade after 
 using CREATE EXTENSION postgis?
 
 On Mon, Jun 25, 2012 at 11:02:44AM -0700, Charlie Sharpsteen wrote:
  Hello all,
  
  I installed PostGIS 2.0 and added it to a database using:
  
  CREATE EXTENSION postgis;
  
  
  Today, I built and installed 2.0.1 and tried to upgrade the 
 extension using:
  
  ALTER EXTENSION postgis UPDATE;
  
  
  However, this failed with:
  
  ERROR:  cannot drop cast from geometry to geometry because 
  extension postgis requires it
  HINT:  You can drop extension postgis instead.
  
  
  So, I tried the old school way of running the upgrade scripts:
  
  psql -f /usr/local/share/postgis/postgis_upgrade_20_minor.sql
  hum_bay_gis
  
  
  But this also fails:
  
  
  BEGIN
  CREATE FUNCTION
 postgis_major_version_check
  --
   Scripts versions checked for upgrade: ok
  (1 row)
  
  DROP FUNCTION
  
 psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:46: ERROR: 
   cannot drop function addgeometrycolumn(character varying,character 
  varying,character varying,character varying,integer,character
  varying,integer,boolean) because extension postgis requires it
  HINT:  You can drop extension postgis instead.
  
 psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:47: ERROR: 
   current transaction is aborted, commands ignored until end of 
  transaction block ...(thousands of similar errors)...
  ROLLBACK
  
  
  So, it appears there is no way to upgrade PostGIS without 
 running `DROP 
  EXTENSION postgis` which would cascade to all 
 spatially-enabled tables.
  
  Am I missing a step or is there a circular dependency here 
 that is breaking 
  the upgrade process?
 
 I had a similar experience with EXTENSION, so much that I 
 decided not to
 use it again. It's a bad step back from safe and well-tested 
 old school way.
 
 I hope you've a way to break out of the EXTENSION dependency 
 jail somehow.
 Please report back when you figure.
 
 --strk;
 
  Sent from our free software
  http://www.gnu.org/philosophy/free-sw.html
 ___
 postgis-users mailing list
 postgis-users@postgis.refractions.net
 http://postgis.refractions.net/mailman/listinfo/postgis-users
 


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Proper way to upgrade after using CREATE EXTENSION postgis?

2012-06-25 Thread Charlie Sharpsteen 
On Monday, June 25, 2012 12:33:06 PM UTC-8, Paragon Corporation wrote:

 It worked fine for me upgrading from 2.0.0 to 2.0.1. 
 I used: 
 ALTER EXTENSION postgis UPDATE TO 2.0.1;   

 Which version of PostGIS are you using. 


 As stated in the first email, I was moving from version 2.0 to 2.0.1.

However, I just re-installed 2.0 to get the output of 
postgis_full_version(). Along the way I updated PostgreSQL from 9.1.3 to 
9.1.4 and then re-tried the 2.0.1 upgrade.

This time ALTER EXTENSION worked without complaint. Perhaps there was a bug 
in Postgres that got fixed by the 9.1.4 upgrade.

-Charlie
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users