3

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?

Jamie
  • 428
  • 6
  • 24
  • alternatively, the last answer in this question pertains to your situation (https://stackoverflow.com/questions/14285854/cannot-create-a-new-table-after-drop-schema-public) – kparekh01 Nov 15 '17 at 18:15

2 Answers2

3

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;
Neil Anderson
  • 1,265
  • 12
  • 19
  • 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? – Jamie Nov 15 '17 at 21:42
  • 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. – Neil Anderson Nov 15 '17 at 23:04
  • 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 Nov 16 '17 at 15:48
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.

Jamie
  • 428
  • 6
  • 24