Rails uses sqlite3 by default on development. If you are not careful enough, you may get so comfortable with sqlite which I did. After a while, there will be a need to push the application to production and at this point, you wouldn’t want sqlite on your web server. I’m using heroku for my app and they stated some good reasons not to use sqlite. On heroku, you wouldn’t be able to push your application to production if sqlite3 remains in your Gemfile.
If you are starting a fresh project, you can just begin this way:
~$: rails new myproject -d postgresql
On an existing project you’ll have to replace modify your Gemfile to replace
~$: bundle install
After which you should change your database.yml in config directory. It should look somewhat like this:
default: &default adapter: postgresql pool: 5 timeout: 5000 development: <<: *default database: development_db username: joe password: 'foobar' # Warning: The database defined as "test" will be erased and # re-generated from your development database when you run "rake". # Do not set this db to the same as development or production. test: <<: *default database: test_db username: joe password: 'foobar' production: <<: *default database: production_db username: appname password:
The adapter has been changed from the default sqlite3 to postgresql.
Next you should create this db in postgre with rake
If you are like me, you should encounter an error at this point. You can’t access psql. This is because the username (joe) is not set as a role in psql. To create one
~$:sudo -u postgres createuser --interactive
If you’re on psql < 9.3 you may encounter an error with the –interactive switch so it’s best you try to upgrade.
Now you can create a password for the user that has been created. Log in to psql
~$:sudo -u postgres psql
and alter the role of the user created with:
ALTER ROLE joe WITH PASSWORD 'secret';
If for any reasons this fails, you should modify your pg_hba.conf file to use md5.