Change from SQLite to PostgreSQL in a fresh Rails project

Question

I have a rails app that's databases are in SQLite (The dev and production). Since I am moving to heroku, I want to convert my database to PostgreSQL.

Anyways, I heard that the local, development, database does not need to be changed from SQLite, so I don't need to change that, however, how do I go about changing the production environment from SQLite to PostgreSQL?

Has anyone ever done this before and can help?

P.S. I'm not sure what exactly this process is called, but I've heard about migrating the database from SQLite to PostgreSQL, is that what needs to be done?

Do you have live production data that needs to go with it, or is it a new/fresh app? — Dylan Markow, Jul 15 '11 at 17:06
I'd recommend that you change your development environment to PostgreSQL as well. SQLite and PostgreSQL (and every other database) have different ideas about what "valid SQL" means and no ORM can insulate you from all of the database's idiosyncrasies. — mu is too short, Jul 15 '11 at 17:53

score 102 · Accepted Answer · edited May 05 '12 at 16:56

102

You can change your database.yml to this instead of using the out of the box sqlite one:

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

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

production:
  adapter: postgresql
  encoding: utf8
  database: project_production
  pool: 5
  username: 
  password:

cucumber:
  <<: *TEST

edited May 05 '12 at 16:56

mu is too short

426,620
70
833
800

answered Jul 15 '11 at 17:48

Chris Barretto

9,379
3
42
45

1

Should I put project_test or the name of my database? – Vasseurth Jul 16 '11 at 03:40
5

You can name it whatever you want. If your project name is 'calculator' I would name them calculator_production, calculator_test, calculator_development – Chris Barretto Jul 24 '11 at 17:28
1

I'm curious, do you actually need the `username` and `password` for configuring PG? I've only used `adapter`, `database`, and `host` in my Rails apps since switching from SQLite3. Is there a benefit (i.e. for security) to adding the username and password? – vich Mar 04 '13 at 16:44
2

@mmichael it really depends on how you have your postgres set up. Using postgres.app, brew, or native if you are on MacOS X Lion+ have different restrictions on their default setup. So if username and password do not apply, you can leave them out, or in with no values. This was just more of a 'catch all' type of configuration. – Chris Barretto Mar 05 '13 at 16:58
2

What is '&TEST' doing in there (line 9)? – David Rhoden Aug 31 '13 at 11:34
2

"&TEST" is setting TEST as the default set of options. They can later be overridden or just left out. "<<: *TEST" is the way to access the default @DavidRhoden – Chris Barretto Sep 06 '13 at 00:52
this is my third day on this postgresql, maybe I'm updating the database.yml file incorrectly. Do I need a migration when changing the database.yml file? I've just been changing its text and saving it. – flobacca Nov 09 '13 at 13:59
Yes, you'll need a migration after the change to database.yml, if you were using SQLite before. – Tamara Jan 10 '14 at 15:16

port5432 · Answer 2 · 2013-07-16T16:02:16.757

The steps below worked for me. It uses the taps gem, created by Heroku and mentioned in Ryan Bates's Railscast #342. There are a few steps but it worked perfectly (even dates were correctly migrated), and it was far easier than the Oracle -> DB2 or SQL Server -> Oracle migrations I have done in the past.

Note that SQLite does not have a user id or password, but the taps gem requires something. I just used the literals "user" and "password".

Create the Postgres database user for the new databases

$ createuser f3
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

EDIT - Updated command below - use this instead

$ createuser f3 -d -s

Create the required databases

$ createdb -Of3 -Eutf8 f3_development
$ createdb -Of3 -Eutf8 f3_test

Update the Gemfile

gem 'sqlite3'
gem 'pg'
gem 'taps'
$ bundle

Update database.yml

#development:
#  adapter: sqlite3
#  database: db/development.sqlite3
#  pool: 5
#  timeout: 5000

development:
  adapter: postgresql
  encoding: unicode
  database: f3_development
  pool: 5
  username: f3
  password:

#test:
#  adapter: sqlite3
#  database: db/test.sqlite3
#  pool: 5
#  timeout: 5000

test:
  adapter: postgresql
  encoding: unicode
  database: f3_test
  pool: 5
  username: f3
  password:

Start the taps server on the sqlite database

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

Migrate the data

$ taps pull postgres://f3@localhost/f3_development http://user:password@localhost:5000

Restart the Rails webserver

$ rails s

Cleanup the Gemfile

#gem 'sqlite3'
gem 'pg'
#gem 'taps'
$ bundle

K ABHIRAM · Answer 3 · 2020-06-22T11:14:42.230

14

Now its become easy with the single command

bin/rails db:system:change --to=postgresql

edited Jun 22 '20 at 11:14

answered Jan 23 '19 at 04:18

K ABHIRAM

168
1
8

1

This is a great answer, it changes the database.yml with the values needed. You can still go in there and change the database name according to your project. – csalmeida Feb 20 '20 at 13:30

score 10 · Answer 4 · answered Jul 15 '11 at 17:26

10

Since you're moving to heroku, you can use taps to do this:

heroku db:push

This will push your local development sqlite data to production, and heroku will automagically convert to postgres for you.

This should also work to push a production sqlite db to heroku, but it's not tested.

RAILS_ENV=production heroku db:push

answered Jul 15 '11 at 17:26

Jesse Wolgamott

40,197
4
83
109

1

the taps gem doesn't seem to work well with 1.9.3, you might need to install 1.9.2 locally to get it to run - once I did that it was mindblowingly smooth https://github.com/ricardochimal/taps/issues/93 – sbeam Aug 23 '12 at 03:33
This is no longer possible. See this question for more info: https://stackoverflow.com/questions/19817851/push-an-sqlite-database-to-heroku-with-taps – sykaeh May 13 '15 at 14:33

jungledre · Answer 5 · 2015-02-17T07:22:07.950

Simply update the config/database.yml file:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: projectname_development

test:
  <<: *default
  database: projectname_test

production:
  <<: *default
  database: projectname_production
  username: 
  password:

The above is what's generated when you run:

$ rails new projectname --database=postgresql --skip-test-unit

Also add this to your Gemfile:

gem 'pg'

score 5 · Answer 6 · answered Jul 31 '11 at 09:36

5

you will also need to add the line "gem 'pg'" to your gemfile, 'pg' being the current postgres gem for Rails.

answered Jul 31 '11 at 09:36

Gus Shortz

1,711
1
15
24

score 4 · Answer 7 · answered Dec 04 '13 at 14:44

Just Update you datatbase.yml

development: &development
  adapter: postgresql
  database: Your_database_name
  username: user_name
  password: password
  host:     localhost
  schema_search_path: public
  min_messages: warning

test:
  <<: *development
  database: test_database_name

production:
  <<: *development
  database: production_db_name

We are using rails and the basic standards should be follow like DRY, Convention over Configuration etc.. so in above code we are not repeating same code again and again.

score 3 · Answer 8 · answered Jun 09 '12 at 12:31

It's been mentioned above me, but I don't have enough reputation as a lurker to be able to upvote it. In the hopes of drawing a little more attention for Rails newbies reading this answer:

you will also need to add the line "gem 'pg'" to your gemfile, 'pg' being the current postgres gem for Rails.

^^^ This is a key piece in addition to the database.yml file described in the selected answer to migrate your Rails app to Postgres.

Zorak · Answer 9 · 2012-10-27T06:58:26.463

3

After replacing gem 'sqlite3 with gem pg in the gemfile, I kept getting the sqlite3 error when pushing to Heroku master because I forgot to commit the updated gemfile. Simply doing the following solved this:

git add .
git commit -m 'heroku push'
heroku create 
git push heroku master

edited Oct 27 '12 at 06:58

answered Oct 27 '12 at 03:26

Zorak

109
7

ianks · Answer 10 · 2014-09-02T05:00:33.413

This is how I have mine setup. If you are only using MRI and not Jruby you can skip the logic in the adapter settings.

defaults: &defaults
  adapter: <%= RUBY_ENGINE == 'ruby' ? 'postgresql' : 'jdbcpostgresql' %>
  encoding: unicode
  pool: 5
  timeout: 5000

development:
  database: project_development
  <<: *defaults

test:
  database: project_test
  <<: *defaults

production:
  database: project_production
  <<: *defaults

score 0 · Answer 11 · answered May 05 '12 at 16:38

0

A possible solution (not for heroku) it's to use yaml.db from:

http://www.railslodge.com/plugins/830-yaml-db

answered May 05 '12 at 16:38

F.Filippi

93
1
8

score 0 · Answer 12 · answered Oct 02 '18 at 04:20

0

Today I had the same issue. I'm working on Rails 4.2.8. The solution was specify the pg gem version, in my case, 0.18.4.

answered Oct 02 '18 at 04:20

Benjamin Peña Olvera

41
2

score 0 · Answer 13 · answered Jul 15 '11 at 17:36

0

You can try following: sqlite3 development.db .dump | psql dbname username

or try with sqlitetopgscript: http://trac-hacks.org/browser/sqlitetopgscript/0.10/sqlite2pg

answered Jul 15 '11 at 17:36

Vibhor Kumar

184
2

Change from SQLite to PostgreSQL in a fresh Rails project

13 Answers13

Linked