Hello Jason,
Do you use EnterpriseDB setup to install PostgreSQL and PostGIS? Are
you on Windows, Like-Nix or Mac platform? Which version of PostGIS WAS
installed in your previous backup? And which one is installed now on
your new setup?
It sounds like you try to restore some PostGIS functions from one
version to another one installed on your new setup. For example if
your backup contains PostGIS functions who work with version 1.5 and
your new installation is set on 2.1, it will not work. Type "SELECT
postgis_version()" in both databases (if you can for the old one) and
if you have different version numbers, you could have to do an hard
upgrade of PostGIS. See postgis_restore.pl script in PosgreSQL
library. You will need to install Perl Langage if you're not on Linux.
If you have to do an hard upgrade, please use manual restoring and
don't use ALTER EXTENSION postgis UPGRADE/UPDATE. What I heard about
it is that doesn't work fine for hard upgrade.
http://www.postgis.org/docs/postgis_installation.html#hard_upgrade
Another think to check is that POSTGIS functions must be in 'public'
schema. Do you have them in it?
Hope it will be useful
Sylvain Racine
iGreffe Géomatique
Granby
[email protected]
http://www.igreffe.net
Jason Fleetwood-Boldt <[email protected]> a écrit :
I've narrowed down my problem (I think) but I really stuck on
something here, any advice to point me in the right direction would
be greatly appreciated.
Whenever I run the restore of my database, I get these kinds of
errors (hundreds and hundreds of them... this just one example)
pg_restore: creating FUNCTION geography_out(geography)
pg_restore: [archiver (db)] Error from TOC entry 894; 1255 17188
FUNCTION geography_out(geography) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: could
not access file "$libdir/postgis-1.5": No such file or directory
Command was: CREATE FUNCTION geography_out(geography) RETURNS cstring
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/postgis-1.5', 'geog...
pg_restore: [archiver (db)] could not execute query: ERROR:
function public.geography_out(geography) does not exist
Command was: ALTER FUNCTION public.geography_out(geography)
OWNER TO postgres;
So, it seems like something is not set up correctly with Postgis.
I've tried droping & recreating my database (both from pgAdmin and
also from rails rake tasks) multiple times, and followed the
instructions here http://postgis.net/install/ but not matter what I
do I always see these errors when I run restore.
It seems like these functions aren't being created correctly, which
makes sense to me, because then after I load up the database I am
unable to use them in pgAdmin (except my rails app continues to
work, completely counter-intuitively).
Can anyone point me in the right direction? I've spent several hours
googling for answers here and am completely stuck at this point.
I am running Postgres 9.3.4.0
-Jason
On Jun 10, 2014, at 11:39 AM, Jason Fleetwood-Boldt
<[email protected]> wrote:
After dropping & recreating my Postgres database, I am seeing some
very strange behavior in pgAdmin while debugging my Rails app.
I'm getting this error:
function st_distance(postgis.geography, unknown) does not exist
Here's how I'm setting up my database
rake db:create
--> I now have 2 extensions plpgsql and postgis; also have two
schemas postgis and public
rake db:migrate
--> I now have 3 extensions plpgsql, postgis, hstore
--> Also I now have 50 tables (what I am expecting)
Then I restore my production dump to my local dev machine. At this
point, everything looks ok when I examine it in pgAdmin, but I am
unable to use any Postgis functions.
Now here's where it gets weird. When my query runs in Rails, it
works fine (as expected). However, when i run it in pgAdmin, it
fails, giving me this error:
ERROR: function st_distance(postgis.geography, unknown) does not exist
LINE 1: SELECT DISTINCT places.*, ST_Distance("places"."coords", '00...
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
When this query runs inside of my Rails app it executes as expected
-- which is behavior I've never seen from Rails before, so I must
be missing something basic.
Can anyone point in the right direction here, I'm really stuck
trying to figure why Rails behaves differently than pgAdmin on the
exact same database.
My full query is below, as you can see it is rather large:
SELECT DISTINCT places.*, ST_Distance("places"."coords",
'0020000001000010e6c0527e94b7b289544044645492ff4ba5') AS distance,
CASE WHEN (premium_listings.id IS NOT NULL AND
(ST_Distance(places.coords,
'0020000001000010e6c0527e94b7b289544044645492ff4ba5')*0.621371) <
premium_listings.reach_radius_mi) THEN 1 ELSE 0 END as
premium_listing, ur.rating AS user_rating, CASE WHEN fp.id IS NULL
THEN 'false' ELSE 'true' END AS user_favorite, CASE WHEN
featured_places.count > 0 THEN 'true' ELSE 'false' END AS featured
FROM "places" LEFT OUTER JOIN premium_listing_places ON
premium_listing_places.place_id = places.id LEFT OUTER JOIN
premium_listings ON (premium_listings.id =
premium_listing_places.premium_listing_id AND
premium_listings.starts_at < NOW() AND premium_listings.ends_at >
NOW()) LEFT OUTER JOIN ratings AS ur ON ur.rateable_id = places.id
AND ur.rateable_type = 'Place' AND ur.user_id = -1 LEFT OUTER JOIN
user_favorite_places AS fp ON fp.place_id = places.id AND
fp.user_id = -1 LEFT OUTER JOIN (SELECT places.id AS id, COUNT(*)
AS count FROM "promotions" INNER JOIN "place_promotions" ON
"place_promotions"."promotion_id" = "promotions"."id" INNER JOIN
"places" ON "places"."id" = "place_promotions"."place_id" WHERE
"promotions"."featured" = 't' AND ((("promotions"."starts_at" <=
'2014-06-10' AND "promotions"."ends_at" >= '2014-06-10') OR
("promotions"."starts_at" <= '2014-06-10' AND
"promotions"."ends_at" IS NULL))) GROUP BY places.id) AS
featured_places ON featured_places.id = places.id JOIN taggings
place_taggings_e280fa7 ON place_taggings_e280fa7.taggable_id =
places.id AND place_taggings_e280fa7.taggable_type = 'Place' WHERE
"places"."status" = 'approved' AND (ST_DWithin("places"."coords",
'0020000001000010e6c0527e94b7b289544044645492ff4ba5', 50000)) AND
(place_taggings_e280fa7.tag_id = 80 OR
place_taggings_e280fa7.tag_id = 102 OR
place_taggings_e280fa7.tag_id = 8 OR place_taggings_e280fa7.tag_id
= 76 OR place_taggings_e280fa7.tag_id = 260 OR
place_taggings_e280fa7.tag_id = 261 OR
place_taggings_e280fa7.tag_id = 217 OR
place_taggings_e280fa7.tag_id = 226 OR
place_taggings_e280fa7.tag_id = 258 OR
place_taggings_e280fa7.tag_id = 286 OR
place_taggings_e280fa7.tag_id = 155 OR
place_taggings_e280fa7.tag_id = 262) GROUP BY places.id,
premium_listings.id, ur.rating, fp.id, featured_places.count ORDER
BY premium_listing DESC, distance LIMIT 50
For your reference, my database.yml file looks like this:
common: &common
adapter: postgis
host: localhost
username: <%= ENV['USER'] %>
encoding: unicode
pool: 5
timeout: 5000
postgis_extension: postgis
schema_search_path: public,postgis
development:
<<: *common
database: mn_development
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--
Sylvain Racine
iGreffe Géomatique
50, rue Saint-Hubert, no. 5
Granby (Québec) J2G 5L9
T: 450.770.9974
C: [email protected]
Site: www.igreffe.net
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
