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.

Freeboard

My structure.sql doesn't try to create the public schema. Which version of PostgreSQL are you using? – Xanthus

@muistooshort psql -V gives me psql (PostgreSQL) 11.3. – Freeboard

Does it say CREATE SCHEMA public; if you manually pg_dump your database? I'm wondering if the pg_dump output change slightly in PostgreSQL 11 (which I don't have installed). – Xanthus

@muistooshort I should have stated earlier that I tried migrating databases in versions 9.6 and 11, and it still tries to create the public schema in structure.sql. – Freeboard

But which version is creating the db/structure.sql file? – Xanthus

@muistooshort From my db/structure.sql file: -- Dumped from database version 11.3 -- Dumped by pg_dump version 11.3 – Freeboard

A manual pg_dump does not include the CREATE SCHEMA public line. – Freeboard

@Freeboard I'm running into this problem as well. Did you find a solution? – Hulahula

@GregW I couldn't find a simple solution. ActiveRecord's postgres adaptor usually calls pg_dump with a --schema=<schema_name> argument, which creates the line CREATE SCHEMA <schema_name>. pg_dump gives no option to change this to ...IF EXISTS.... – Freeboard

Source: activerecord-5.0.7.2/lib/active_record/tasks/postgresql_database_tasks.rb:45 – Freeboard

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;

Freeboard