Changing sqlite to PostgreSQL in ruby on rails

Question

I am using win 7 with docker and in docker I am using ruby on rails, I did an application that works very well with sqlite3 but when I try to upload to Heroku, Heroku gave me an error that it is not supported sqlite3 so I had to change my app database to PostgreSQL and I did the following changes: in my Gemfile I commented gem sqlite3:

# Use sqlite3 as the database for Active Record
#gem 'sqlite3'
#posgresql
gem 'pg'

second i did a "bundle install" and then a docker-compuse build

then in my database.yml:

BEFORE:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

# 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: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

after the changes:

development:
  adapter: postgresql
  database: my_database_development
  pool: 5
  timeout: 5000

test:
  adapter: postgresql
  database: my_database_test
  pool: 5
  timeout: 5000

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

so when i tried to create a new datebase with "rake db:create" docker gave me the next error:

 $ docker-compose run web rails db:create
Starting grupo43_postgres_1 ... done
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"?
Couldn't create database for {"adapter"=>"postgresql", "database"=>"my_database_
development", "pool"=>5, "timeout"=>5000}
rails aborted!
PG::ConnectionBad: 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"?
/usr/local/bundle/gems/pg-1.0.0/lib/pg.rb:56:in `initialize'
/usr/local/bundle/gems/pg-1.0.0/lib/pg.rb:56:in `new'
/usr/local/bundle/gems/pg-1.0.0/lib/pg.rb:56:in `connect'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
postgresql_adapter.rb:697:in `connect'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
postgresql_adapter.rb:221:in `initialize'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
postgresql_adapter.rb:38:in `new'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
postgresql_adapter.rb:38:in `postgresql_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:759:in `new_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:803:in `checkout_new_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:782:in `try_to_checkout_new_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:743:in `acquire_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:500:in `checkout'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:374:in `connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_adapters/
abstract/connection_pool.rb:931:in `retrieve_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_handling.
rb:116:in `retrieve_connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/connection_handling.
rb:88:in `connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/postgresql_dat
abase_tasks.rb:10:in `connection'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/postgresql_dat
abase_tasks.rb:19:in `create'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/database_tasks
.rb:117:in `create'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/database_tasks
.rb:137:in `block in create_current'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/database_tasks
.rb:305:in `block in each_current_configuration'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/database_tasks
.rb:302:in `each'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/database_tasks
.rb:302:in `each_current_configuration'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/tasks/database_tasks
.rb:136:in `create_current'
/usr/local/bundle/gems/activerecord-5.1.6/lib/active_record/railties/databases.r
ake:27:in `block (2 levels) in <top (required)>'
/usr/local/bundle/gems/railties-5.1.6/lib/rails/commands/rake/rake_command.rb:21
:in `block in perform'
/usr/local/bundle/gems/railties-5.1.6/lib/rails/commands/rake/rake_command.rb:18
:in `perform'
/usr/local/bundle/gems/railties-5.1.6/lib/rails/command.rb:46:in `invoke'
/usr/local/bundle/gems/railties-5.1.6/lib/rails/commands.rb:16:in `<top (require
d)>'
/example/bin/rails:9:in `require'
/example/bin/rails:9:in `<top (required)>'
/usr/local/bundle/gems/spring-2.0.2/lib/spring/client/rails.rb:28:in `load'
/usr/local/bundle/gems/spring-2.0.2/lib/spring/client/rails.rb:28:in `call'
/usr/local/bundle/gems/spring-2.0.2/lib/spring/client/command.rb:7:in `call'
/usr/local/bundle/gems/spring-2.0.2/lib/spring/client.rb:30:in `run'
/usr/local/bundle/gems/spring-2.0.2/bin/spring:49:in `<top (required)>'
/usr/local/bundle/gems/spring-2.0.2/lib/spring/binstub.rb:31:in `load'
/usr/local/bundle/gems/spring-2.0.2/lib/spring/binstub.rb:31:in `<top (required)
>'
/example/bin/spring:15:in `require'
/example/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Tasks: TOP => db:create
(See full trace by running task with --trace)

i found different solution but for linux and mac, all i could inform here is that maybe the problem is because i have to delete some file that i didn t know where i can find with windows or with docker :(, all the information i found is here: PG::ConnectionBad - could not connect to server: Connection refused

this is my docker-compose.yml file:

version: '3'
services:
  postgres:
    image: postgres:10.3
    ports:
      - "3000"
  web:
    build: .
    env_file:
      - .env
    command: bash -c "(bundle check || bundle install) && bundle exec rails s -p 3000 -b '0.0.0.0'"
    volumes:
      - .:/example
    ports:
      - "3000:3000"
    depends_on:
      - postgres

Answer 1

The simplest way for me was to

• Create a new app like this rails new testapp --database=postgresql
• Open config/database.yml
• Copy everything in that file into my existing app
• Change the name in about 4 places in that file
• Everything worked!

An advantage of this solution is you can use it for any rails version (just make sure the new app you create is the same rails version as the one whose database you're switching).

Answer 2

Make sure you have installed PostgreSQL in your machine and add a helpful tool called pgAdmin, then update the database.yml file like below

default: &default
  adapter: postgresql
  encoding: unicode
  username: postgres
  password: xxxxx #<-- which you have defiend while installing postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: project_development

test:
  <<: *default
  database: project_test

production:
  <<: *default
  database: project_production
  username: username
  password: <%= ENV['PROJECT_DATABASE_PASSWORD'] %>

and gem pg use in your Gemfile like gem 'pg', '~> 0.20.0' with this version then run bundle install

rake db:create
rake db:migrate

for Heroku

heroku run rake db:migrate

and you can use rails command instead of rake if your rails version is rails > 5.0.0