CODEWITHSUNDEEP
ruby-on-railspostgresqlpostgisdatabase-migration
bitmaybewise
bitmaybewise

Reputation: 41

Postgresql extensions removed on db/structure.sql when migrations runs

I have a rails app that uses postgresql + some extensions, but every time I run rails db:migrate it removes the lines that enable the extensions. I have to copy and paste it manually every time.

Lines being removed on db/structure.sql:

-- Name: EXTENSION "postgis"; Type: COMMENT; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "postgis" WITH SCHEMA public;


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


--
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

My database.yml:

default: &default
  adapter: postgis
  encoding: unicode
  host: <%= ENV.fetch('DB_HOST', 'localhost') %>
  username: <%= ENV.fetch('DB_USERNAME') %>
  password: <%= ENV.fetch('DB_PASSWORD') %>
  schema_search_path: public

test:
  <<: *default
  database: db_test

development:
  <<: *default
  database: db_development

production:
  <<: *default
  database: db_production

Any ideas how can I fix it?

I'm using the following versions:

postgresql: 9.6

postgis: 2.3

rails: 5.0

macOS: 10.12

UPDATE:

I managed to find a workaround to the problem. As I'm using schema_search_path as public, if not defined the default option is public. Just removed this line from database.yml and it works now. Still no clues why it's happening when defining the schema_search_path explicitly.

Upvotes: 4

Views: 2319

Answers (4)

Shawn Mathews
Shawn Mathews

Reputation: 11

After digging thru the code for the schema dumper, I found an option that seems to help.

ActiveRecord::Base.dump_schemas = :all

I added this to a config/initializers/schema_dumper.rb

Upvotes: 1

Madis N&#245;mme
Madis N&#245;mme

Reputation: 1303

I experienced a similar issue where the database.yml specified multiple schemas for like schema_search_path: public, third_party. I found the answer why the CREATE EXTENSION statements didn't end up in structure.sql.

The reason is explained in https://github.com/rails/rails/issues/17157 but here is it for reference

If the user has specified a schema_search_path and also has extensions, the resulting pg_dump call generated by rake db:structure:dump includes the --schema flag which means that no CREATE EXTENSION statements will be created in the resulting structure.sql. According to the pg_dump documentation:

Upvotes: 3

skyporter
skyporter

Reputation: 897

You can also move all your extension to another schema like shared_extension and controls which database schemas will be dumped by set config.active_record.dump_schemas = "public".

Upvotes: 0

s3tjan
s3tjan

Reputation: 1168

You could generate a migration and use enable_extension method:

class AddExtensions < ActiveRecord::Migration[5.1]
   def change
     enable_extension "postgis"
     enable_extension "plpgsql"
     enable_extension "pg_trgm"
     enable_extension "uuid-ossp"
     # ...
   end
end

Upvotes: 3

Related Questions