Rails + Postgis: error cannot be disabled table_face_ref_sys

Question

Rails 5
Postgresql 9.4.10
Postgis 2.1.8

when I try to use rake db: reset, the console shows 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 would be appreciated.

Answer 1

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

However, we do not want the db/schema.rb include this table, because then db:drop or db:reset will try to delete this table, and we will get this error.

To show the 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, be sure to remove the create_table statement from db/schema.rb .

Answer 2

This custom table comes with the PostGIS extension. It has been added automatically. Your schema.rb probably contains the 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 create a test database with rake . Since the force parameter is set, it first discards the existing table, but postgis is required.

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

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

Then run rake db:migrate to update the schema.rb file and do this.

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