rake db:migrate - PG:: InvalidSchamaName ERROR: no schema has been selected to create in

Question

I am attempting to run a rake:db migrate on a postgres database for my rails application. rake:db drop (the db already existed I am fixing a bug that requires I recreate the db) runs fine rake:db create runs fine but when I run rake:db migrate I get the following error

ERROR --: PG::InvalidSchemaName: ERROR: no schema has been selected to create in.

My database.yml file looks fine (it was working so I'm not sure what happened?)

development: adapter: postgressql encoding: utf8 username: [myusername] password: [mypassword] host: [myhost] port: 5432 database: myapp_dev schema_search_path: "myapp_dev" pool: 5 timeout: 5000

Any idea what is going on?

alternatively, the last answer in this question pertains to your situation (stackoverflow.com/questions/14285854/…) — kparekh01
– kparekh01, Commented Nov 15, 2017 at 18:15

Neil Anderson · Accepted Answer · 2017-11-15 21:13:34Z

4

Rails does not automatically create schemas AFAIK, if you stick to the public schema that fact can be ignored and things just work.

To use a different schema you will have to include a migration to do so, a raw SQL one like this will do:

class CreateMyAppSchema < ActiveRecord::Migration def up execute "CREATE SCHEMA myapp_dev;" end def down execute "DROP SCHEMA myapp_dev;" end end

In addition to the schema in the schema search path existing, the user needs to have the USAGE privilege on the myapp_dev schema

GRANT USAGE on schema myapp_dev to the_user;

edited Nov 15, 2017 at 21:13

answered Nov 15, 2017 at 18:10

Neil Anderson

1,39512 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jamie Over a year ago

I have no issue sticking to the public schema, how then would I do the GRANT USAGE on schema myapp_dev to the_user (assuming the_user is the DB owner?) sorry I'm fairly new to postgres. In Rails, do I have to create a migration for that? And if I have a _dev, _test and prod how would I go about that?

Neil Anderson Over a year ago

If you stick to the public schema you just leave things as they are, no need to grant usage, and potentially remove the 'schema_search_path'. By default any SQL will be referring to public then. If you have different envs you could check an environment string to determine what the name of the schema should be, however if you have different databases for each environment you can name them all the same schema.

Jamie Over a year ago

So I removed the schema search path in my database.yml (I have a dev, test and prod for it so I can and that seems to have fixed the issue. I guess I got my wires crossed on something because I had read a different S/O where it said it was required. I'm thinking I may have had a DB that I was using that required it but no longer since I stood up my own DB for it. Problem solved you get the upvote thank you.

Jamie · Accepted Answer · 2017-11-15 18:17:43Z

1

For some reason there was no "myapp_dev" schema in my database, when I created it using the PGAdmin tool it fixed the issue.

answered Nov 15, 2017 at 18:17

Jamie

4568 silver badges28 bronze badges

2 Comments

Jamie Over a year ago

If anyone has any idea why rails wouldn't create the "myapp_dev" schema in the database though that would be helpful.

Neil Anderson Over a year ago

I've edited my answer to include creating the schema in your migrations

Collectives™ on Stack Overflow

rake db:migrate - PG:: InvalidSchamaName ERROR: no schema has been selected to create in

2 Answers 2

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Linked

Related