Heroku - Trouble with migrating from Sqlite to Postgres

Question

I built a Rails Application with their built in database SQLITE 3. I have been trying for some time to get the application deployed to Heroku. I understood that Heroku's preferred database is Postgres so I went through the trouble of changing my application's database from SQLITE to postgres.

Here is my Gemfile, which specifies that I now have 'pg' installed instead of sqlite.

source 'https://rubygems.org'

# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
ruby '2.1.0'
gem 'rails', '4.1.1'

group :development, :test do
  gem 'pg', '0.17.1'
  gem 'rspec-rails', '3.0.1'
end

group :test do
  gem 'selenium-webdriver', '2.35.1'
  gem 'capybara', '2.1.0'
end

gem 'sass-rails', '~> 4.0.2'
gem 'uglifier', '>= 1.3.0'
gem 'coffee-rails', '~> 4.0.0'
gem 'sprockets-rails', '~> 2.1.3'
gem 'bootstrap-sass', '3.1.1.1'
# See https://github.com/sstephenson/execjs#readme for more supported runtimes
# gem 'therubyracer', platforms: :ruby

gem 'jquery-rails'
gem 'turbolinks'
gem 'jbuilder', '~> 2.1.1'

group :doc do
  # bundle exec rake doc:rails generates the API under doc/api.
  gem 'sdoc', require: false
end

# Use ActiveModel has_secure_password
gem 'bcrypt', '~> 3.1.7'

# Use unicorn as the app server
# gem 'unicorn'

# Use Capistrano for deployment
# gem 'capistrano', group: :development

# Use debugger
# gem 'debugger', group: [:development, :test]

I have installed the gem pg many times recently to ensure it's installed. Running psql --version gives me the following

psql (PostgreSQL) 9.3.4

Running which psql shows me the directory

/usr/local/bin/psql

In converting from SQLite to Postgres, I updated my database.yml file the best I could.

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: postgresql
  encoding: unicode
  database: games_development
  pool: 5
  timeout: 5000
  host:  localhost

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  database: games_test
  pool: 5
  timeout: 5000

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

As you can see, I changed the adapter from sqlite3 to postgresql as shown on Heroku Dev Center web site. I named my database with the following format. appname_environement for the three environments

I even added host:localhost for the development environment.

Here is the output I receive when running cat pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     edited                               trust
#host    replication     edited        127.0.0.1/32            trust
#host    replication     edited       ::1/128                 trust

Now I would like to describe my problem. I can deploy successfully to Heroku, but when running heroku open I received the following error message.

Application Error

An error occurred in the application and your page could not be served. Please try again in a few moments.

If you are the application owner, check your logs for details.

My next step was to run the heroku logs command.

2014-06-19T16:14:31.269890+00:00 app[web.1]: => Booting WEBrick
2014-06-19T16:14:31.269894+00:00 app[web.1]: => Rails 4.1.1 application starting in production on http://0.0.0.0:36743
2014-06-19T16:14:31.269896+00:00 app[web.1]: => Run `rails server -h` for more startup options
2014-06-19T16:14:31.269898+00:00 app[web.1]: => Notice: server is listening on all interfaces (0.0.0.0). Consider using 127.0.0.1 (--binding option)
2014-06-19T16:14:31.269899+00:00 app[web.1]: => Ctrl-C to shutdown server
2014-06-19T16:14:31.269901+00:00 app[web.1]: Exiting
2014-06-19T16:14:32.519139+00:00 heroku[web.1]: State changed from starting to crashed
2014-06-19T16:14:32.507573+00:00 heroku[web.1]: Process exited with status 1
2014-06-19T16:17:43.026380+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/" host=lit-anchorage-9017.herokuapp.com request_id=d2e55388-dbe6-439e-bba5-2f3f18eb3744 fwd="24.14.65.222" dyno= connect= service= status=503 bytes=

From what I can understand of the above output, it starts off by running the WEBrick server in the production development. I do know that my 'pg' gem is in the development and test environments as indicated in my Gemfile. I think that could be a problem. The output than says that the server is exiting with status 1. The last line is in gold, which indicates to me there is a problem.

Reading that error message, I see error code H10 with the description "APP crashed". Moving forward it shows a status code of 503, which indicates a problem with the server.

I tried running rake db:rollback command to reset the database.

PG::ConnectionBad: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting
TCP/IP connections on port 5432?
/Users/edited/.rvm/gems/ruby-2.1.0/gems/activerecord-  4.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:881:in `initialize'
/Users/edited/.rvm/gems/ruby-2.1.0/gems/activerecord-4.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:881:in `new'
/Users/edited/.rvm/gems/ruby-2.1.0/gems/activerecord-4.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:881:in `connect'
/Users/edited/.rvm/gems/ruby-2.1.0/gems/activerecord-4.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:568:in `initialize'
/Users/edited/.rvm/gems/ruby-2.1.0/gems/activerecord-4.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `new'
/Users/edited/.rvm/gems/ruby-2.1.0/gems/activerecord-4.1.1/lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `postgresql_connection'

One thing I immediately noticed is that it indicated a connection error, that it couldn't connect to the server. I also noticed that it indicated port 5432, when the common port for localhost is 3000. The message then showed a trace of the problem, which seems to be mostly in the connection_adapter/postgresql_adapter.

The above error message is also shown for rake db:migrate, rails c-sandbox, rails c commands and rails s.

I admit that when I created this app, it was created in sqlite so that might have caused some problems. However, since I need to push the app to Heroku, I had to change the database to postgres.

To be completely honest, I feel I have exhausted all of my options. I have no choice but to ask this question here. Any advice is greatly appreciated.

Answer 1

When uploading to heroku using rails version 4.1.1 you need to configure the production environment using the information your provided with. https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-ruby

Heroku replaces the database.yml file for versions not > than 4.1 so above that you have to configure your production details as they auto generate a database for you.

To check out your database details: https://postgres.heroku.com/databases

   production: &production
   adapter: postgres://qrffxpnewoxxxxxxxxxxx
   host:  ec2-xxxxxxxxxxxxxxxxxxxxxxxxx
   database: db8qxxxxxxx
   port: 5432
   username: qrffxpxxxxxk
   password: G9CdRxxxxxxxxx

Answer 2

Hope this solves the issue you have come across..

I have noticed that you haven't specified the postgresql gem for the production environment within your gemfile. You have only specified it for the development and test environments.

Add the following to your gemfile:

group :production do
  gem 'pg'
  gem 'rails_12factor'
end

The 'rails_12factor' gem enables features like static asset serving and logging in Heroku as they have been removed from Rails 4.

When deploying to Heroku, your app isn't required to run Postgresql in your development environment in order for it to run in production on Heroku.

This resource may help out as well if the above doesn't work. All the best!

https://devcenter.heroku.com/articles/getting-started-with-rails4

Answer 3

one possibility. Have you tired running "rake assets:precompile RAILS_ENV=production --trace" in the development environment then git add/commit before push to Heroku ? - Pierre