Rails + PostGIS errors migrating database

Question

I was following Daniel Azuma's talk on geospatial analysis with rails but I am having difficulty whenever I run rake db:migrate in the second project.

The details of my setup are as follows: I am running Postgresql using Postgres.app which gives me version 9.1.3 of Postgres and 2.0.0 of PostGIS. I run into a few issues with the database.yml file, and running migrations. ( I have added the relevant gems and required their info in application.rb)

My database.yml file looks like this:

 development:
   adapter: postgis
   postgis_extension: true
   host: localhost
   encoding: unicode
   database: my_app_development
   pool: 5
   username: my_app
   password:

If I add the following line schema_search_path: "public,postgis" I get:

 rake aborted!
 PG::Error: ERROR:  schema "postgis" does not exist
 : SET search_path TO public,postgis

If I remove that line I receive the following error when I try to migrate my database:

rake aborted!
PG::Error: ERROR:  relation "geometry_columns" does not exist
LINE 1: SELECT * FROM geometry_columns WHERE f_table_name='schema_mi...                       ^
: SELECT * FROM geometry_columns WHERE f_table_name='schema_migrations'

Does anyone have an idea on how to fix these issues?

There is a migration included that references a polygon as a data type . However I don't believe there is an additional migration needed to be created for a geometry columns table I think it should reference the postgis adapter — tomciopp, Aug 30 '12 at 05:18

score 18 · Answer 1 · answered Sep 28 '12 at 19:45

18

Drop PostGIS extenion in public schema and recreate it in postgis schema.

DROP EXTENSION PostGIS;

CREATE SCHEMA postgis;
CREATE EXTENSION PostGIS WITH SCHEMA postgis;
GRANT ALL ON postgis.geometry_columns TO PUBLIC;
GRANT ALL ON postgis.spatial_ref_sys TO PUBLIC

answered Sep 28 '12 at 19:45

Raido

571
5
11

database.yml should also be amended to `schema_search_path: public, postgis` – Jerome Aug 18 '15 at 05:13

score 10 · Accepted Answer · answered Apr 15 '13 at 00:49

10

Here is how I solved the issue. I first created a new migration to add postgis to the database. (I have already installed both postgis and postgresql through homebrew on a mac.)

rails g migration add_postgis_to_database

In the migration file I removed the change method and used the execute method to add POSTGIS.

execute("CREATE EXTENSION postgis;")

After that you can check the database to make sure that postgis is available.

psql your_database_name
SELECT PostGIS_full_version();

answered Apr 15 '13 at 00:49

tomciopp

2,602
2
31
60

1

Just to be clear, the migration file is supposed to look like: class AddPostgisToDatabase < ActiveRecord::Migration execute("CREATE EXTENSION postgis;") end – dysbulic Apr 28 '13 at 16:59
@will `rails g migration` will give you a template to add this code to, so put it inside the `change` block or write your own `up` method. – tadman Nov 18 '13 at 19:31
Chiming in late on this... @tomciopp I had used your solution on an application on my mac, and it solved the issue. However on a newer application this CREATE EXTENSION migration is leading to the same error. I conclude it must be some task upstream during installation that's missing.. – Jerome Aug 16 '15 at 18:18

score 3 · Answer 3 · answered Nov 15 '14 at 09:04

Actually, the install command needs to invoke the postgis version

sudo apt-get install -y postgis postgresql-9.3-postgis-2.1

The simplest way forward is then to declare

sudo -u postgres psql -c "CREATE EXTENSION postgis" your-pg-database-name

avoiding migration hiccups.

score 2 · Answer 4 · answered Aug 31 '12 at 01:41

2

What version of PostgreSQL are you using? The EXTENSION thing appeared in 9.1. Extensions are a handy way of loading several objects in one package.

If you're on less than 9.1 you'll probably be able to load PostGIS following those instructions (all the -f commands). It may also be a good idea to upgrade, but that's up to you.

answered Aug 31 '12 at 01:41

Arthur

1,974
2
21
28

I am currently running 9.1.4 which I believe is the most current version of postgres. – tomciopp Aug 31 '12 at 03:49
Yes I found I needed to do it "the old way" like this even on pg 9.4. Not sure why the EXTENSION commands didn't fix the issue for me. I hit upon these instruction on the OpenStreetMap wiki which amount to the same thing (running these SQL files): http://wiki.openstreetmap.org/wiki/PostGIS/Installation#Ubuntu_14.10.2B_2 – Harry Wood Jul 23 '15 at 08:41

score 1 · Answer 5 · answered Jun 25 '17 at 21:11

I had the same issue, except while @Raido's solution fixed the issue for db:migrate, I was still having issues with the Apartment gem when a tenant was created (e.g. during db:seed).

I discovered that Rails was automagically adding enable_extension "postgis" to schema.rb, which Apartment uses to create the tenant schema. I'm not sure exactly why Apartment doesn't use the existing postgis extension (perhaps an issue with the search_path at the time of tenant creation), but this results in the same error.

The solution (if you can call it that) was to simply remove the enable_extension "postgis" line from schema.rb. The only problem with this approach is that any subsequent migrations which trigger a schema.rb refresh result in the line being re-added.

Also, I used the Apartment approach of adding the postgis extension to the shared_extensions schema instead of its own. My lib/tasks/db_extensions.rake looks like:

namespace :db do
  desc 'Also create shared_extensions Schema'
  task :extensions => :environment  do
    # Create Schema
    ActiveRecord::Base.connection.execute 'CREATE SCHEMA IF NOT EXISTS shared_extensions;'
    # Enable Hstore
    ActiveRecord::Base.connection.execute 'CREATE EXTENSION IF NOT EXISTS HSTORE SCHEMA shared_extensions;'
    # Enable uuid-ossp for uuid_generate_v1mc()
    ActiveRecord::Base.connection.execute 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA shared_extensions;'
    # Enable postgis extension for geographic data types
    ActiveRecord::Base.connection.execute 'DROP EXTENSION IF EXISTS postgis;'
    ActiveRecord::Base.connection.execute 'CREATE EXTENSION postgis WITH SCHEMA shared_extensions;'
    ActiveRecord::Base.connection.execute 'GRANT USAGE ON SCHEMA shared_extensions to PUBLIC;'
    puts 'Created extensions'
  end
end

Rake::Task["db:create"].enhance do
  Rake::Task["db:extensions"].invoke
end

Rake::Task["db:test:purge"].enhance do
  Rake::Task["db:extensions"].invoke
end

And my database.yml looks like:

postgis_options: &postgis_options
  adapter: postgis
  postgis_extension: postgis # default is postgis
  postgis_schema: shared_extensions # default is public

default: &default
  schema_search_path: 'public,shared_extensions'
  encoding: utf8
  <<: *postgis_options

...

production:
  <<: *default
  url: <%= ENV['DATABASE_URL'].try(:sub, /^postgres/, 'postgis') %>

Not ideal, but it's working. Perhaps this will save someone an hour or two with PostGIS and Apartment. I'd be interested to know if anyone has a better solution than removing the enable_extension call from schema.rb :)

score 0 · Answer 6 · answered Sep 24 '14 at 12:32

0

Make sure that you have installed this

sudo apt-get install postgresql-9.3-postgis

I was facing same issue due to missing this package.

answered Sep 24 '14 at 12:32

vitthal-gaikwad

1,184
11
13

Rails + PostGIS errors migrating database

6 Answers6

Linked