Rails 4: changing database to postgresql (from sqlite3)

Question

So I want to change my database form sqlite3 to pg. I tried following this guide. There is something about password to database, I don't know where should I take that password. Is there any default one?

Gemfile

source 'https://rubygems.org'

gem 'rails', '4.2.5.1'
gem 'pg'
gem 'sass-rails', '~> 5.0'
gem 'uglifier', '>= 1.3.0'
gem 'coffee-rails', '~> 4.1.0'
gem 'decent_exposure'
gem 'slim'
gem 'nprogress-rails'
gem 'jquery-rails'
gem 'turbolinks'
gem 'jbuilder', '~> 2.0'
gem 'sdoc', '~> 0.4.0', group: :doc
gem 'ffaker'

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

group :development do
  gem 'pry-rails'
  gem 'better_errors'
  gem 'binding_of_caller'
  gem 'spring'
  # gem 'sqlite3'
end

database.yml

default: &default
  adapter: postgresql
  host: localhost
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: blog_development

test:
  <<: *default
  database: blog_test

production:
  <<: *default
  database: blog_production

Bundle install result is 100% success.

When I try to create db:

bin/rake db:create
Running via Spring preloader in process 12795
could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `initialize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `new'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `connect'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:242:in `initialize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `new'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `postgresql_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:438:in `new_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:448:in `checkout_new_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:422:in `acquire_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:349:in `block in checkout'
/home/dominik/.rvm/rubies/ruby-2.2.4/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:348:in `checkout'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:263:in `block in connection'
/home/dominik/.rvm/rubies/ruby-2.2.4/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:262:in `connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:571:in `retrieve_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_handling.rb:113:in `retrieve_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_handling.rb:87:in `connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/postgresql_database_tasks.rb:6:in `connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/postgresql_database_tasks.rb:15:in `create'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:93:in `create'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:107:in `block in create_current'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:275:in `block in each_current_configuration'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:274:in `each'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:274:in `each_current_configuration'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:106:in `create_current'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/railties/databases.rake:17:in `block (2 levels) in <top (required)>'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:240:in `call'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/home/dominik/.rvm/rubies/ruby-2.2.4/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:150:in `invoke_task'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:106:in `each'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:106:in `block in top_level'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:115:in `run_with_threads'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:100:in `top_level'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:78:in `block in run'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:176:in `standard_exception_handling'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/rake-10.5.0/lib/rake/application.rb:75:in `run'
/home/dominik/github/dd_blog/bin/rake:9:in `<top (required)>'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activesupport-4.2.5.1/lib/active_support/dependencies.rb:268:in `load'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activesupport-4.2.5.1/lib/active_support/dependencies.rb:268:in `block in load'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activesupport-4.2.5.1/lib/active_support/dependencies.rb:240:in `load_dependency'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activesupport-4.2.5.1/lib/active_support/dependencies.rb:268:in `load'
/home/dominik/.rvm/rubies/ruby-2.2.4/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
/home/dominik/.rvm/rubies/ruby-2.2.4/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
-e:1:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "database"=>"blog_development"}

rake db:migrate

rake aborted!
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?
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `initialize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `new'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:651:in `connect'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:242:in `initialize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `new'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/postgresql_adapter.rb:44:in `postgresql_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:438:in `new_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:448:in `checkout_new_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:422:in `acquire_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:349:in `block in checkout'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:348:in `checkout'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:263:in `block in connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:262:in `connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:571:in `retrieve_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_handling.rb:113:in `retrieve_connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/connection_handling.rb:87:in `connection'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:916:in `initialize'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:823:in `new'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:823:in `up'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:801:in `migrate'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/home/dominik/.rvm/gems/ruby-2.2.4/gems/activerecord-4.2.5.1/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
/home/dominik/.rvm/gems/ruby-2.2.4/bin/ruby_executable_hooks:15:in `eval'
/home/dominik/.rvm/gems/ruby-2.2.4/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Note that I never used postgres before. Maybe some configuration is needed?

P.S. I'm using Ubuntu 14.04

Turned out, that i didn't have postgresql installed.

score 1 · Answer 1 · answered May 12 '20 at 22:42

1

Rails 6+

In Rails 6.0 or higher, you can run this command:

rails db:system:change --to=postgresql

It also accepts mysql, sqlite3, oracle, sqlserver and others which can be seen in this class.

answered May 12 '20 at 22:42

Lucas Caton

3,027
1
24
34

score 0 · Answer 2 · answered Feb 28 '16 at 23:58

A couple things, rake db:migrate will only run your migrations and will not create your dev deb. The command to create your db is: rake db:create

You can only run migrations after you have created the db and are connected to it.

So try that first, and if that fails check to make sure you have postgresql running. I'm on ubuntu 15.04, I run postgres as a service look into doing that if need be. Confirm postgresql is properly installed and running. In terminal 'psql -V' will return your installed postgresql version. Check processes $ ps aux | grep postgresl .

Once you have confirmed that you have postgresql up and running. Next, remove the 'host: localhost' line from database.yml, it isn't necessary. Next, in database.yml try adding: port: 5432 below your dev database. If this still doesn't fix it below that add: host: ""

score 0 · Answer 3 · answered Mar 02 '16 at 03:57

1 - Add this to your Gemfile

gem 'pg', '~> 0.15'

2 - Add this to your database.yml changing myapp for the name of your application

# PostgreSQL. Versions 8.2 and up are supported.
#
# Install the pg driver:
#   gem install pg
# On OS X with Homebrew:
#   gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On OS X with MacPorts:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: 5

development:
  <<: *default
  database: myapp_development

  # The specified database role being used to connect to postgres.
  # To create additional roles in postgres see `$ createuser --help`.
  # When left blank, postgres will use the default role. This is
  # the same name as the operating system user that initialized the database.
  #username: myapp

  # The password associated with the postgres role (username).
  #password:

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  #host: localhost

  # The TCP port the server listens on. Defaults to 5432.
  # If your server runs on a different port number, change accordingly.
  #port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # Defaults to warning.
  #min_messages: notice

# 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:
  <<: *default
  database: myapp_test

# As with config/secrets.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password as a unix environment variable when you boot
# the app. Read http://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full rundown on how to provide these environment variables in a
# production deployment.
#
# On Heroku and other platform providers, you may have a full connection URL
# available as an environment variable. For example:
#
#   DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
#
# You can use this database configuration with:
#
#   production:
#     url: <%= ENV['DATABASE_URL'] %>
#
production:
  <<: *default
  database: myapp_production
  username: myapp
  password: <%= ENV['MYAPP_DATABASE_PASSWORD'] %>

2 - then:

rake db:purge
rake db:setup
rake db:migrate

Rails 4: changing database to postgresql (from sqlite3)

3 Answers3

Rails 6+