Rails + Postgis: error cannot drop table spatial_ref_sys

Question

Rails 5
Postgresql 9.4.10
Postgis 2.1.8

when I try rake db:reset, console show an error

rake aborted! ActiveRecord::StatementInvalid:
PG::DependentObjectsStillExist: ERROR:  cannot drop table
spatial_ref_sys because extension postgis requires it HINT:  You can
drop extension postgis instead. : DROP TABLE "spatial_ref_sys" CASCADE

I am new to psql and postgis, any help will be appreciated.

Answer 1

I ran into this same issue when I created a migration with enable_extension :postgis. This had the intended effect of enabling the extension, but it also updated my db/schema.rb file to include this spatial_ref_sys table, which is required by postgis.

However, we don't want the db/schema.rb file to include this table, because then a db:drop or db:reset will try and remove the table, and we'll get this error.

To tell rails to ignore this table, we can add the following to the end of config/environment.rb:

ActiveRecord::SchemaDumper.ignore_tables = ["spatial_ref_sys"]

Also, don't forget to remove the create_table statement from db/schema.rb.

Answer 2

This special table comes with PostGIS extension. It was added automatically. Your schema.rb probably contains line: create_table "spatial_ref_sys", primary_key: "srid", id: :integer, force: :cascade do |t|

Then you try to recreate your database with rake db:reset or maybe create a test database with rake. Because force option is set it first drop existing table but it is required by postgis.

The quick solution is to ignore this table in some initializer:

::ActiveRecord::SchemaDumper.ignore_tables |= %w(spatial_ref_sys)

Then run rake db:migrate to refresh your schema.rb and it's done.

But more likely you will want to work with some PostGIS features using gem activerecord-postgis-adapter. It will also fix schema.rb for you.