Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For some reason I manage to have 2 versions of postgis installed at the same time due to a wrong update.

I can see this as by issuing a select postgis_full_version() i get back version 2.1.0 installed but if I dump the database i get the following statements in the sql file:

create function _st_asgml(integer, geography, integer, integer, text) returns text LANGUAGE c IMMUTABLE AS '$libdir/postgis-2.0', 'geography_as_gml';

which means the functions are using v2.0

This is only part of the functions as some of the postgis functions read postgis-2.1

I have tried to uninstall postgis-2.0 with the provided uninstall_postgis.sql script but it is failing with current transaction is aborted

My pg_available_extensions table reads:

   name          | default_version | installed_version |                               comment                                
------------------------+-----------------+-------------------+----------------------------------------------------------------------
 pg_freespacemap        | 1.0             |                   | examine the free space map (FSM)
 citext                 | 1.0             |                   | data type for case-insensitive character strings
 postgis_topology       | 2.0.3           | 2.1.0             | PostGIS topology spatial types and functions
 pg_buffercache         | 1.0             |                   | examine the shared buffer cache
 unaccent               | 1.0             |                   | text search dictionary that removes accents
 intagg                 | 1.0             |                   | integer aggregator and enumerator (obsolete)
 plpython2u             | 1.0             |                   | PL/Python2U untrusted procedural language
 hstore                 | 1.1             |                   | data type for storing sets of (key, value) pairs
 pg_trgm                | 1.0             |                   | text similarity measurement and index searching based on trigrams
 cube                   | 1.0             |                   | data type for multidimensional cubes
 tcn                    | 1.0             |                   | Triggered change notifications
 ltree                  | 1.0             |                   | data type for hierarchical tree-like structures
 intarray               | 1.0             |                   | functions, operators, and index support for 1-D arrays of integers
 pltcl                  | 1.0             |                   | PL/Tcl procedural language
 earthdistance          | 1.0             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw               | 1.0             |                   | foreign-data wrapper for flat file access
 pageinspect            | 1.0             |                   | inspect the contents of database pages at a low level
 adminpack              | 1.0             | 1.0               | administrative functions for PostgreSQL
 moddatetime            | 1.0             |                   | functions for tracking last modification time
 pgstattuple            | 1.0             |                   | show tuple-level statistics
 plpythonu              | 1.0             |                   | PL/PythonU untrusted procedural language
 dict_int               | 1.0             |                   | text search dictionary template for integers
 seg                    | 1.0             |                   | data type for representing line segments or floating-point intervals
 dblink                 | 1.0             | 1.0               | connect to other PostgreSQL databases from within a database
 pgrowlocks             | 1.0             |                   | show row-level locking information
 tablefunc              | 1.0             |                   | functions that manipulate whole tables, including crosstab
 pg_stat_statements     | 1.1             |                   | track execution statistics of all SQL statements executed
 plpgsql                | 1.0             | 1.0               | PL/pgSQL procedural language
 refint                 | 1.0             |                   | functions for implementing referential integrity (obsolete)
 chkpass                | 1.0             |                   | data type for auto-encrypted passwords
 tsearch2               | 1.0             |                   | compatibility package for pre-8.3 text search functions
 isn                    | 1.0             |                   | data types for international product numbering standards
 fuzzystrmatch          | 1.0             |                   | determine similarities and distance between strings
 lo                     | 1.0             |                   | Large Object maintenance
 timetravel             | 1.0             |                   | functions for implementing time travel
 test_parser            | 1.0             |                   | example of a custom parser for full-text search
 autoinc                | 1.0             |                   | functions for autoincrementing fields
 pltclu                 | 1.0             |                   | PL/TclU untrusted procedural language
 postgis_tiger_geocoder | 2.1.0           |                   | PostGIS tiger geocoder and reverse geocoder
 dict_xsyn              | 1.0             |                   | text search dictionary template for extended synonym processing
 btree_gin              | 1.0             |                   | support for indexing common datatypes in GIN
 insert_username        | 1.0             |                   | functions for tracking who changed a table
 postgis                | 2.0.3           | 2.1.0             | PostGIS geometry, geography, and raster spatial types and functions
 pgcrypto               | 1.0             |                   | cryptographic functions
 btree_gist             | 1.0             |                   | support for indexing common datatypes in GiST

`

 Schema |    Name     | Result data type |      Argument data types       |  Type  
--------+-------------+------------------+--------------------------------+--------
 public | st_distance | double precision | geography, geography           | normal
 public | st_distance | double precision | geography, geography, boolean  | normal
 public | st_distance | double precision | geom1 geometry, geom2 geometry | normal
 public | st_distance | double precision | text, text                     | normal

Any suggestion on how to remove v2.0?

System is Slackware 13.37 with postgresql and postgis compiled from source.

share|improve this question
    
You shouldn't get CREATE FUNCTION statements in your dumps at all, if you enabled PostGIS with CREATE EXTENSION. Did you directly run the PostGIS setup script instead? –  Craig Ringer Mar 24 at 1:32
    
It was quite some time ago when I did it but I think yes. –  Fabrizio Mazzoni Mar 24 at 2:47
    
In that case you need to CREATE EXTENSION postgis FROM UNPACKAGED, then ALTER EXTENSION postgis UPDATE TO '2.1' (or whatever the version is; if in doubt, check the extension directory or select * from pg_available_extensions) –  Craig Ringer Mar 24 at 2:49
    
Ok Thanks. I'll give it a go –  Fabrizio Mazzoni Mar 24 at 2:50
    
Create extension postgis from unpackaged will raise an error as the extension is already present. alter extension postgis update to '2.1.0' does work but I still get create functions in the dump. Have edited the question to include the pg_available_extensions table . –  Fabrizio Mazzoni Mar 29 at 12:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.