Rails 4.0.1 on Heroku, can't create database

Question

I can't get rake db:migrate to run on my rails 4.0.1 app on Heroku.

I'm guessing that I don't have postgres configured properly... but reading the docs on heroku hasn't really helped and I'm not sure what to do. I don't know too much about heroku or postgres.

Any help or resources would be greatly appreciated. Let me know if there's anything else I can post.

(Also, I'm using devise, if that matters)

When I run heroku run rake db:migrate I get this:

Running `rake db:migrate` attached to terminal... up, run.5077
PG::UndefinedTable: ERROR:  relation "users" does not exist
LINE 5:                WHERE a.attrelid = '"users"'::regclass
                                      ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                 pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
            FROM pg_attribute a LEFT JOIN pg_attrdef d
              ON a.attrelid = d.adrelid AND a.attnum = d.adnum
           WHERE a.attrelid = '"users"'::regclass
             AND a.attnum > 0 AND NOT a.attisdropped
           ORDER BY a.attnum

rake aborted!
PG::UndefinedTable: ERROR:  relation "users" does not exist
LINE 5:                WHERE a.attrelid = '"users"'::regclass
                                      ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                 pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
            FROM pg_attribute a LEFT JOIN pg_attrdef d
              ON a.attrelid = d.adrelid AND a.attnum = d.adnum
           WHERE a.attrelid = '"users"'::regclass
             AND a.attnum > 0 AND NOT a.attisdropped
           ORDER BY a.attnum

When I run heroku run rake db:setup I get this:

FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.

... stack trace ...

Couldn't create database for {"adapter"=>"postgresql", "username"=>"aqofwrwjifcqkx", "password"=>"7yqDAx1L_4HFhw7WV3PH7ZrKyM", "port"=>5432, "database"=>"d5dvi0pjk7dgr7", "host"=>"ec2-23-21-94-137.compute-1.amazonaws.com"}

followed by the same PG::UndefinedTable ERROR relation 'users' does not exist

database.yml :

development:
  adapter: sqlite3
  encoding: unicode
  database: chore_app_development
  pool: 5

test:
  adapter: sqlite3
  encoding: unicode
  database: chore_app_test
  pool: 5

production:
  adapter: postgresql
  database: chore_app_production
  pool: 5
  timeout: 5000

Gemfile:

source 'https://rubygems.org'
ruby '2.0.0'

gem 'rails', '4.0.1'

# Database
group :production do
  gem 'pg'
end

group :development, :test do
  gem 'sqlite3'
end

# Assets
gem 'sass-rails', '~> 4.0.0'
gem 'uglifier', '>= 1.3.0'
gem 'turbolinks'
gem 'haml-rails'
gem 'jquery-rails'

# Heroku
gem 'rails_12factor', group: :production

# Application
gem 'devise'
gem 'formtastic'

# Testing
gem 'factory_girl_rails'

Do you have the postgresql add-on in you Heroku app resources page? — thank_you, Nov 29 '13 at 22:18
Do I need to put the user and password from here in my database.yml? If so, would I then have to remove the file from version control (private repo, but still) — user2043725, Nov 29 '13 at 22:27
Heroku will recreate the database.yml for you so you it doesn't matter what you put in that file or if it even exists in the app. — thank_you, Nov 29 '13 at 22:31
Your database creds are in your question. Rotate them immediately by running `heroku pg:credentials HEROKU_POSTGRESQL_COLOR --reset --app your-app` https://postgres.heroku.com/blog/past/2012/7/17/rotate_database_credentials_on_heroku_postgres_/ — GregB, Nov 29 '13 at 22:39
Do you perhaps have some logic in an initializer that is attempting to use the users relationship (before it exists in the database)? — nbrew, Nov 29 '13 at 22:47
@NathanHyde I don't think so. I just went through all my initializers and most of them are just empty scaffolds. There is a devise.rb but it just seems to set a bunch of config variables? I don't see it calling users anywhere. Even so, I don't think that would prevent rake db:setup from being able to create the database, no? — user2043725, Nov 29 '13 at 22:57
@fatfrog Migrations were sound, turned out to be FactoryGirl. — user2043725, Dec 01 '13 at 19:41

thank_you · Answer 1 · 2016-11-26T00:04:44.020

52

I would first try

heroku restart

heroku rake db:migrate

If that doesn't work, then try.

heroku pg:reset DATABASE_URL   #Literally type in heroku pg:reset DATABASE_URL

heroku rake db:migrate

edited Nov 26 '16 at 00:04

answered Nov 29 '13 at 22:33

thank_you

11,001
19
101
185

1

Unfortunately that's what I tried already. The output above is from running `heroku run rake db:migrate`, running heroku restart or heroku pg:reset DATABASE_URL doesn't change the error. Doing heroku pg:reset and then migrating also produces the same error. – user2043725 Nov 29 '13 at 22:37
sometimes the simplest explanation is the right one – craastad Oct 11 '14 at 09:14
@user2043725 your case can be different but it helps - as in my case – Mani Dec 16 '15 at 13:28
5

For those who are dumb like me, the command is literally `heroku pg:reset DATABASE_URL`. I was trying to find out what's my "database_url", but you don't need to worry about that (it's an environment variable set on Heroku). Just type `heroku pg:reset DATABASE_URL` and be happy. Thanks for answer, Jason :) – Paladini May 15 '16 at 13:24
1

@FernandoPaladini, oh, hell, thanks. I've been having trouble with this for hours before I stumbled upon your comment. – Evgenia Karunus Nov 25 '16 at 23:20
1

@lakesare, I actually mention this comment in my answer. It must not be prominent enough for others to see. I'll fix that. – thank_you Nov 26 '16 at 00:04
@jason328, great, thanks. I did notice that in your answer after I read fernando's comment, I'd not pay attention otherwise. – Evgenia Karunus Nov 26 '16 at 00:11

score 5 · Accepted Answer · edited May 23 '17 at 11:55

5

I should've read the stack trace more closely. My factories.rb was creating user objects as attributes for other factories. Wrapping them in a block fixed it:

FactoryGirl screws up rake db:migrate process

edited May 23 '17 at 11:55

Community

1
1

answered Dec 01 '13 at 19:40

user2043725

255
1
2
9

I would recommend installing a local copy of PostgreSQL or creating a development instance of PostgresSQL on Heroku [1] and connecting to it remotely from your dev machine. This will ensure the same behavior in dev as in production. [1] https://www.heroku.com/postgres – Steve Wilhelm Dec 01 '13 at 21:24

score 4 · Answer 3 · answered Jun 10 '15 at 08:16

4

Run

heroku pg:reset DATABASE_URL

and you are done.

answered Jun 10 '15 at 08:16

Saqib R.

2,919
2
26
21

9

Literally, "DATABASE_URL" (I was initially confused wondering what I should substitute in there) – Freedom_Ben Jun 16 '15 at 18:17
1

@Freedom_Ben Yes Literally :-). Its actually is confusing. – Saqib R. Jun 18 '15 at 05:11

score 3 · Answer 4 · edited Aug 19 '18 at 23:13

Jason's answer might fix your problem, but I wanted to expand on why you got the error and how to fix it.

You can't run "rake db:setup" on Heroku, because it will try to create a database, and your database already exists on Heroku
In your database.yml, you should read the database url from the environment variable like: production: url: <%= ENV["DATABASE_URL"] %>
I think you are getting the 2nd error because you have some version error in your migrations, runing them from the begining should fix it : rake db:migrate VERSION=0

score 0 · Answer 5 · answered Feb 13 '16 at 23:16

0

To drop and recreate database

Login to heroku in terminal
cd into your app
Run

heroku pg:reset DATABASE

answered Feb 13 '16 at 23:16

Saraswathy Renuga

172
1
10

Rails 4.0.1 on Heroku, can't create database

5 Answers5

Linked