Why does my db/structure.sql file contain a CREATE SCHEMA statement after running db:structure:dump?

Question

After migrating my development database, I'm unable to run rails db:test:prepare.

My application uses the db/structure.sql file to update the test database:

# config/application.rb

config.active_record.schema_format = :sql

When I run rails db:migrate (which also runs db:structure:dump), my db/structure.sql is updated. Unfortunately, it now contains this line near the top after updating:

CREATE SCHEMA public;

This will bomb when loaded into a database that already contains the public schema.

I can manually update that line and the SQL dump loads successfully:

CREATE SCHEMA IF NOT EXISTS public;

...but I don't want to do that every time.

I expect the test database to be built successfully from the SQL dump in db/structure.sql when I run rails db:test:prepare because the SQL dump should not try to create the public schema.

Answer 1

My development environment config explicitly specified the "public" schema. I removed that specification, which allowed db:test:prepare to complete successfully.

# config/environments/development.rb

Rails.application.configure do
  ...
  config.active_record.dump_schemas = "public" # <<-- DELETED!
end

You can configure which database schemas will be dumped when calling db:structure:dump by setting config.active_record.dump_schemas to one of the following options:

• :schema_search_path: This looks for the schema names in the schema_search_path setting in config/database.yml.
• "<some string>": It will dump the schema names in the string. Names here are comma separated values.
• :all: No schema names are specified.
• Or just don't set it at all, which is what I did.

If config.active_record.dump_schemas.blank? == true, ActiveRecord's Postgres adaptor will set the --schema=<schema name> flag on pg_dump, which in turn adds the line CREATE SCHEMA <schema name> to its output in db/structure.sql.

An interesting side effect is that now db:test:prepare inserts this instead:

CREATE SCHEMA _template;