[pgrouting-users] Problem with CREATE EXTENSION pgrouting;

[René Fournier]

So, I already had postgis extension installed in the postgres database. 

postgres=# SELECT postgis_full_version();
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
                                                                postgis_full_version                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.9.1" RASTER
(1 row)

Plus, I installed pgrouting:

postgres=# create extension postgis from unpackaged;
ERROR:  extension "postgis" already exists
postgres=# select * from pgr_version();
 version |       tag       | build |  hash   | branch | boost  
---------+-----------------+-------+---------+--------+--------
 2.0.0   | pgrouting-2.0.0 | 0     | f26831f | master | 1.55.0
(1 row)

The problem seems to be, that when I change to mydb, postgis is available, but pgrouting is not—and when I try to create the extension in mydb, pgrouting complains that postgis is not there… but it is clearly accessible.

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# SELECT postgis_full_version();
NOTICE:  Function postgis_gdal_version() not found.  Is raster support enabled and rtpostgis.sql installed?
NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
NOTICE:  Function postgis_raster_scripts_installed() not found. Is raster support enabled and rtpostgis.sql installed?
NOTICE:  Function postgis_raster_lib_version() not found. Is raster support enabled and rtpostgis.sql installed?
                                                               postgis_full_version                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.9.1" (core procs from "2.1.1 r12113" need upgrade)
(1 row)

mydb=# select * from pgr_version();
ERROR:  function pgr_version() does not exist
LINE 1: select * from pgr_version();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
mydb=# create extension postgis from unpackaged;
ERROR:  type "raster" does not exist
mydb=# 

Now, I’m pretty sure the problems I’m having are simply the result of not understanding how/where extensions are created and accessible by individual databases. (So, sorry for the silly questions.)

…Rene

On Jul 15, 2014, at 6:46 PM, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:

> On 7/15/2014 8:31 PM, René Fournier wrote:
>> Hi there,
>> 
>> I’m having some trouble installing the pgRouting extension. Specifically, I want to add it to an existing database, but all the tutorials only seem to show creating a database with pgRouting, then loading data.
>> 
>> postgres=# SELECT postgis_full_version();
>> NOTICE:  Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?
>>                                                                postgis_full_version
>> ----------------------------------------------------------------------------------------------------------------------------------------------------
>> POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.9.1" RASTER
>> (1 row)
>> 
>> postgres=# select * from pgr_version();
>> version |       tag       | build |  hash   | branch | boost
>> ---------+-----------------+-------+---------+--------+--------
>> 2.0.0   | pgrouting-2.0.0 | 0     | f26831f | master | 1.55.0
>> (1 row)
>> 
>> postgres=# \c mydb
>> 
>> mydb=# create extension postgis;
>> ERROR:  PostGIS is already installed in schema 'public', uninstall it first
> 
> This is ok and can be ignored.
> 
>> alba3=# select * from pgr_version();
>> mydb:  function pgr_version() does not exist
>> LINE 1: select * from pgr_version();
>>                      ^
>> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> 
> This fails correctly because you have not installed pgrouting!
> 
> create extension pgrouting;
> select * from pgr_version();
> 
> It is possible that the pgrouting extension is dependent on have postgis installed as an extension rather then from the sql script. You can fix this with:
> 
> create extension postgis from unpackaged;
> 
> Then run the commands above.
> 
> -Steve
> 
>> mydb=#
>> 
>> 
>> Can anyone point me in the right direction? Thanks!
>> 
>> …Rene
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>> 
> 
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20140716/e0c591c8/attachment.html>