Migrate existing Rails project from SQLite3 to PostgreSQL for Heroku production

heroku
sqlite
postgresql

#1

I launched my Cloud9 Rails project to Heroku (yay!). In doing so, I changed the Gemfile from “gem ‘sqlite3’” to “gem ‘pg’” and added “gem ‘rails_12factor’, group: :production” and bundle installed it. I did this because Heroku prefers PostgreSQL.

My project is running fine on Heroku and is using PostgreSQL in production. However my development environment on Cloud9 is still SQLite3 (my database.yml is still the same) – is this an issue? I heard that we should always use the same database platfrom for dev and prod.

So I’m now migrating my Cloud9 dev environment to PostgreSQL. I saw this guide: Setting up PostgreSQL But it seems like it’s for a brand new project… what do I do if I have a pre-existing project?

I saw this guide - http://railscasts.com/episodes/342-migrating-to-postgresql?view=asciicast (scroll down to “Migrating an Existing Application”) and the directions look quite different.

Which one do I follow? Is there a good Cloud9 tutorial for migrating existing projects to postgres?


#2

If you are going to use Postgres for production, then you should use it as well for development. Otherwise you will be entering a world full of pain and suffering. Not worth it.

You can use this guide as reference to switch to Postgres in your workspace and development environment.

Let us know if you encounter any problems.


#3

Thanks so much for for your reply!

That looks like a solid guide. Quick question: should I follow that guide, will I have the same tables in my current schema.rb on the new database? Also, will the current user data in the table fields also get pushed over? Since this is a pre-existing project I want to make sure that I don’t loose my work in the process.


#4

When you switch to Postgres, you will have the same tables after your run your migrations because your schema.rb is constructed according to all your migrations.

Regarding the data, most likely it will be lost. You should back it up and see how you can export it to the new Postgres database.


#6

Thank you so much, I understand perfectly now.

One last question regarding the guide. The first part is titled “setup workspace” to create a “new custom private workspace.” I’m not sure exactly what that means. I already have my Cloud9 workspace for my existing project that I’m in. Should I skip this section and go down to “Postgresql setup”? Other guides don’t have this first section, such as the this top answer on http://stackoverflow.com/questions/26545746/cloud9-postgres.

I did follow both guides in two different cloned workspaces and saw no notable difference in the end result.


#7

Yes you can pretty much ignore the Setup Workspace section and start from the Postgresql setup section. I believe the guide is quite old and C9 was a bit different back then.


#8

Andres you’ve been such a great help. I appreciate you following up on my questions. I’ve migrated my database successfully. Thank you!


#9

My pleasure! Glad you sorted it out. If you have more questions we are here to help.


#10

@naridi Hey!

When you upgraded your rails app to PostgreSQL, did you find that guide useful? I am looking to do the same and would appreciate hearing from someone who as recently gone through this process :slight_smile:
Thanks!