switching from Sqlite3 to PostgreSQL on an existing rails application

Question

When I tried to switching from sqlite3 to postgresql in my existing application I faced this problem with rake db:migrate, I did the following

1 - rake db:create

2- rake db:migrate I got this error:

== AddColumn1: migrating ===================================================== -- add_column(:users, :first_name, :string) rake aborted! An error has occurred, this and all later migrations canceled: PG::Error: ERROR: relation "users" does not exist

3- rake db:reset

4- rake db:migrate , now migration is done with no errors

I have lost my data specially my admin user because of rake db:reset and my questions is :

1- why I forced to use rake db:reset ?

2- is there ways to transfer my data from a database engine to another without losing it in the next time ?

3- and for PostgreSQL I couldn’t use a blank password , it said fe_sendauth: no password supplied , after adding password this error is gone , using password in another database engine instead of Sqlite3 is a must ?

4- will heroku require a reset also or its like Github will accept the data if I use another db engine in development ?

This Railscast is a great resource on migrating, and should answer most of your questions: railscasts.com/episodes/342-migrating-to-postgresql — Drew
– Drew, Commented Aug 9, 2013 at 15:11
Does looking at schema_migrations help? Can you replicate the error each time? — port5432
– port5432, Commented Aug 9, 2013 at 15:14

Yosep Kim · Accepted Answer · 2013-08-09 15:57:52Z

3

So, what you are saying is you lost data in your sqlite3 database? This should not have happened. Did you change your database.yml in config folder to point your app to the new Postgres DB? If you did point it to the new DB, you should not have lost any data in sqlite3 DB since the app is not hitting it any more.

If you have indeed changed your db config to use the new db, you may not have lost any data. I hope this is the case. :)

1- why I forced to use rake db:reset ?

I don't think you had to run rake db:reset on your new DB, unless your migration files are messed up.

2- is there ways to transfer my data from a database engine to another without losing it in the next time ?

You can create a rake task or a script that points to two db instances (the old one and the new one you are migrating to). You can then pull data from the old db and then massage data as needed (each RDBM has their own minor syntactical differences) and the load them to the new db.

3- and for PostgreSQL I couldn’t use a blank password , it said fe_sendauth: no password supplied , after adding password this error is gone , using password in another database engine instead of Sqlite3 is a must ?

It depends how you set up your Postgres DB instance you created...

4- will heroku require a reset also or its like Github will accept the data if I use another db engine in development ?

Setting up your DB on Heroku would be very simple. All you have to do is the following:

heroku crate your-app-name
git push heroku master
heroku run db:migrate

But, I think you have some issues in your migration files. Nevertheless, you can actually get a username/password for your DB on Heroku and then use your script to push the data over to it from your local DB.

I hope this helps!

answered Aug 9, 2013 at 15:57

Yosep Kim

2,96125 silver badges23 bronze badges

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

2 Comments

Mostafa Hussein Over a year ago

you said i may have some error in my migration files so i forced to make a reset , well , how can i fix this error to not to reset my database ?

Mostafa Hussein Over a year ago

== AddColumn1: migrating ===================================================== -- add_column(:users, :first_name, :string) rake aborted! An error has occurred, this and all later migrations canceled: PG::Error: ERROR: relation "users" does not exist

that was the error what makes me to make a reset , can i solve it without reseting and without losing my data ?

port5432 · Accepted Answer · 2013-11-27 09:41:44Z

1

If you are using Devise, you can reload your user account information using:

rake db:seed

answered Nov 27, 2013 at 9:41

port5432

6,44311 gold badges65 silver badges103 bronze badges

Comments

John David Barbosa · Accepted Answer · 2019-09-25 07:25:27Z

0

Take a look on this guide https://www.digitalocean.com/community/tutorials/how-to-set-up-ruby-on-rails-with-postgres so you can recreate the process manually by changing your database.yml and Gemfile.

If you need to migrate your database information run

pgloader ./production.sqlite3 postgres://username:password@localhost/database

Check https://migara.li/2017/07/24/migrating-from-sqlite-to-postgres/

Other alternatives like taps aren't working right now http://railscasts.com/episodes/342-migrating-to-postgresql

answered Sep 25, 2019 at 7:25

John David Barbosa

413 silver badges7 bronze badges

Collectives™ on Stack Overflow

switching from Sqlite3 to PostgreSQL on an existing rails application

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related