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, Oct 17 '16 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, Oct 17 '16 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, Oct 17 '16 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, Oct 17 '16 at 12:21
That means your postgres user `FC` dont have privilege to create database. check this http://stackoverflow.com/a/31669921/4587148 to create a new user and grant privilege and change the user in database.yml — Sajan, Oct 17 '16 at 12:25
Well, now I have privilege to create database. I did it. When I run : $ taps server sqlite://db/development.sqlite3 fc password it returns : Failed to connect to database: Sequel::DatabaseConnectionError -> SQLite3::CantOpenException: unable to open database file — Florian_CEO, Oct 17 '16 at 12:47

Sasha Stadnik · Answer 1 · 2017-01-26T23:40:22.653

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"]

score 0 · Answer 2 · edited Nov 05 '21 at 14:03

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 05 '21 at 14:03

Ruli

2,592
12
30
40

answered Nov 05 '21 at 10:06

Azeez Ibrahim

13
5

score -1 · Answer 3 · answered Oct 17 '16 at 12:07

-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 '16 at 12:07

Aditya Y

198
3
13

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 Oct 17 '16 at 12:13
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 ? – Florian_CEO Oct 17 '16 at 12:17
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. https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL – jaydel Oct 17 '16 at 16:18
@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. – Aditya Y Oct 18 '16 at 05:20

score -1 · Answer 4 · answered Oct 17 '16 at 12:48

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

Sqlite to Postgres - existing Rails project

4 Answers4