Sqlite to Postgres - existing Rails project

Question

I'm new to Rails.

I already have a project ready to be deployed.

I'm using Sqlite3 but I want to switch to Postgres.

I follow many tutorials but nothing works, so I need your help.

When I follow instructions from Railscast, it doesn't work : [http://railscasts.com/episodes/342-migrating-to-postgresql?view=asciicast][1]

When I run :

rake db:migrate

it return :

rake aborted! ActiveRecord::NoDatabaseError: FATAL:  database "development" does not exis

When I run.. :

$ taps server sqlite://db/development.sqlite3 User password

..with the User I set up in database.yml,

I set "SECRET_KEY_BASE=mypassword" into .env.development fil.

Here is my database.yml :

development:
  adapter: postgresql
  encoding: utf8
  database: development
  pool: 5
  username: FC
  password: 

test: &TEST
  adapter: postgresql
  encoding: utf8
  database: test
  pool: 5
  username: FC
  password:

it return :

/Users/fc/.rvm/rubies/ruby-2.3.0/lib/ruby/site_ruby/2.3.0/rubygems/core_ext/kernel_require.rb:133:in `require': cannot load such file -- rack/showexceptions (LoadError)
    from /Users/fc/.rvm/rubies/ruby-2.3.0/lib/ruby/site_ruby/2.3.0/rubygems/core_ext/kernel_require.rb:133:in `rescue in require'
    from /Users/fc/.rvm/rubies/ruby-2.3.0/lib/ruby/site_ruby/2.3.0/rubygems/core_ext/kernel_require.rb:40:in `require'
    from /Users/fc/.rvm/gems/ruby-2.3.0/gems/sinatra-1.0/lib/sinatra/showexceptions.rb:1:in `<top (required)>'

PG version :

psql (PostgreSQL) 9.4.4

which psql :

/usr/local/bin/psql

I'm lost at this point because nothing works.

How can I do the migration easily, step by step ?

to solve database "development" does not exist run rake db:create first then migrate — Sajan
– Sajan, Commented Oct 17, 2016 at 11:43
How "rake db:create" could know that I need to create "development database" ? And why do I have to create a DB even I already have from sqlite3 ? I just want to migrate sqlite db to postgres. — Florian_CEO
– Florian_CEO, Commented Oct 17, 2016 at 12:03
you said you are moving from sqlite to postgres so you need to create database in postgres first and then migrate and it will know that it needs to create development data base since in database.yml you have mentioned database: development and regarding keeping the data, you need to feed the sqlite dump to psql — Sajan
– Sajan, Commented Oct 17, 2016 at 12:16
When I run rake db:create it returns : PG::InsufficientPrivilege: ERROR: permission denied to create database : CREATE DATABASE "development" ENCODING = 'utf8' — Florian_CEO
– Florian_CEO, Commented Oct 17, 2016 at 12:21
That means your postgres user FC dont have privilege to create database. check this stackoverflow.com/a/31669921/4587148 to create a new user and grant privilege and change the user in database.yml — Sajan
– Sajan, Commented Oct 17, 2016 at 12:25

Sasha Stadnik · Accepted Answer · 2017-01-26 23:40:22Z

0

I had the same issue

 kernel_require.rb:133:in `require': cannot load such file -- rack/showexceptions

My ruby version was 2.3.0 i just switched into ruby 1.9.3 temporary and this error disappeared

taps server sqlite://db/development.sqlite3 user password

and now command above works fine so i can migrate sqlite to postgresql with taps gem

You need install these gems

tilt '1.4.1'
rack '1.0.1'

because when you hit command below

taps pull postgres://admin:password@localhost/db_development http://user:password@localhost:5000

you can see such error

 ERROR: Rack::Utils::OkJson::Error: cannot encode Symbol: :schema_migrations
An error occurred but Hoptoad was not notified. To use Hoptoad, please
install the 'hoptoad_notifier' gem and set ENV["HOPTOAD_API_KEY"]

edited Jan 26, 2017 at 23:40

answered Jan 26, 2017 at 23:11

Sasha Stadnik

5321 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ruli · Accepted Answer · 2021-11-05 14:03:56Z

0

You can do this using this command. (If it is rails 6)

rails db:system:change --to=postgresql

See list of other database type below:

rails db:system:change --to=mysql, 
rails db:system:change --to=sqlite3, 
rails db:system:change --to=oracle, 
rails db:system:change --to=frontbase, 
rails db:system:change --to=sqlserver, 
rails db:system:change --to=jdbc

edited Nov 5, 2021 at 14:03

Ruli

2,83113 gold badges35 silver badges48 bronze badges

answered Nov 5, 2021 at 10:06

Azeez Ibrahim

277 bronze badges

Comments

Aditya Y · Accepted Answer · 2016-10-17 12:07:11Z

-1

I followed these steps when I migrated my application to postgres from sqlite3

1. Remove or comment sqlite3 gem
2. Add gem "pg" to your gem file and bundle
3. Add your database.yml 
4. bundle exec rake db:setup

You should have your postgres server up and running.

answered Oct 17, 2016 at 12:07

Aditya Y

3284 silver badges14 bronze badges

4 Comments

Florian_CEO Over a year ago

I don't want to loose all my datas stored in my DB. Does it work when you already have datas in your sqlite3 DB ?

Florian_CEO Over a year ago

I don't understand how do you transfer the datas from sqlite3 to Postgres with this process ? How and when do you set up User/password for your Postgres DB ?

user483040 Over a year ago

Do you have production data in your database or is it application seed data? If it's not a ton of data, I'd suggest writing seeds for it so you can rebuild that data on the fly as needed. If it's production data, I'm sorry you did that. sqlite3 is really not good for real production data. Check this link for possible tools/scripts to migrate the data itself. wiki.postgresql.org/wiki/…

Aditya Y Over a year ago

@Florian_CEO when I made this change the application was in development. So I did not care about the data. And yes when I made this change the data is lost. If you are in production I suggest not to go with this approach.

Bandi · Accepted Answer · 2016-10-17 12:48:29Z

What the errors saying is that your database developement dosen'nt exist. if your want start rails app with pg in developement mode, you have to create the database first with:

rake db:create

But if you won't to deploy your app and still using it locally without loosing any of tour data, just set sqlite3 to developement and postgres to production:

First, you need to add pg gem to production group in your gemfile and sqlite3 to developement, so you don't lose any of your data: change in your Gemfile from:

source 'https://rubygems.org'
.....
gem 'sqlite3'
.
.
.

to:

source 'https://rubygems.org'
...
group :development, :test do
    gem 'sqlite3'

end

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

Run

bundle install

Second, uncoment your config.yml production part that contain sqlite3 configuration and add postgresql config like so:

production:
  adapter: postgresql
  pool: 5
  timeout: 5000

Collectives™ on Stack Overflow

Sqlite to Postgres - existing Rails project

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related