How can I dump multiple postgres schemas using rake db:dump:schema

Question

Our postgres database has two schemas: a public schema and a metadata schema. I need both schemas in my test database, but rake db:schema:dump only dumps the public schema. If I add schema_search_path: "public, metadata" to my database.yml file, it dumps both schemas, but the schema information is not there.

How can I dump both schemas to db/schema.rb so I can load them with rake db:test:prepare?

Answer 1

It looks to me like the answer is to use a structure file instead of a schema file.

Add this to application.rb

# use a .sql structure instead of a schema.rb for the schema
config.active_record.schema_format = :sql

remove your schema.rb file

This will now dump your database using structure (sql) intead of schema (rb) and it can be more expressive. However, it is now tied to your database vendor (not a big deal for us).

bundle exec rake db:test:prepare