current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 64 down vote favorite
72

I currently have Ruby on Rails installed via RVM in Ubuntu 12.04. The default database is set up in SQLite3, but I'd like to switch to PostgreSQL for the purposes of pushing to Heroku. How can I accomplish this?

share|improve this question

3 Answers 3

up vote 2 down vote

sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

sudo apt-get install postgresql-common

sudo apt-get install postgresql-9.3 libpq-dev

share|improve this answer
up vote 150 down vote accepted

Here are the steps I've followed:

Install PostgreSQL and development package

$ sudo apt-get install postgresql
$ sudo apt-get install libpq-dev

Set up a user that is the same as my Ubuntu log-in

$ sudo su postgres -c psql
postgres=# CREATE ROLE <username> SUPERUSER LOGIN;
postgres=# \q

Modify Gemfile

# Remove gem 'sqlite3'
gem 'pg'

Modify database.yml in app directory

development:
  adapter: postgresql
  encoding: unicode
  database: appname_development
  pool: 5
  timeout: 5000
  username: <username>
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: appname_test
  pool: 5
  timeout: 5000
  username: <username>
  password:

Run bundle install

$ bundle install

Create databases and migrations

$ rake db:create:all
$ rake db:migrate

Here are the sources I used to help:
http://mrfrosti.com/2011/11/postgresql-for-ruby-on-rails-on-ubuntu/
http://railscasts.com/episodes/342-migrating-to-postgresql
https://devcenter.heroku.com/articles/local-postgresql

share|improve this answer
14  
If you want to add a password use this command after running CREATE ROLE: ALTER ROLE <username> WITH PASSWORD '<yourpassword>'; then obviously add your password into database.yml – Hengjie Jan 24 '13 at 5:33
9  
Alternatively, if you're just creating the role for the first time and want to enter in a password as well: CREATE ROLE <username> SUPERUSER LOGIN PASSWORD '<yourpassword>'; – Hengjie Jan 24 '13 at 5:40
    
If you set the user as identical with your unix username, is this secure even if there is no password? Based on, e.g., the mrfrosti.com link above, and also Heroku's Postgress.app, it seems like that's often the suggested route. – Dav Clark Feb 5 '13 at 17:02
    
here's instructions for installing 9.2 on ubuntu askubuntu.com/questions/186610/how-do-i-upgrade-to-postgres-9-2/… – Danny Feb 18 '13 at 17:30
1  
Worked for me. I created my psql user with following command: CREATE ROLE Alex SUPERUSER LOGIN;. In the database.yml I also set my username as Alex and got the same error as jmontross. Then I changed it to alex and everything worked. I also had to run rake db:migrate RAILS_ENV=test. Also note - this method migrates the database from sqlite3 to postgres, but does not migrate the actual data in the database. To migrate it as well, refer to the railscast mentioned in the answer. – Alex Popov Aug 21 '13 at 19:46
up vote 6 down vote

For all Ubuntu 13.10 users that open this thread follow the steps below to install postresql:

sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main' > /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-common -t saucy
sudo apt-get install postgresql-9.2 libpq-dev

since there isn't an official Postgres repository for Ubuntu 13.10.

Then create the user as Nick explain (you can specify a password too):

sudo su postgres -c psql
postgres=# CREATE ROLE gotqn SUPERUSER LOGIN;
postgres=# \password gotqn
postgres=# \q

Note: Replace the gotqn above with whoami result:

enter image description here

The easiest way to create your rails application is to specify you are using postgresql as follows:

rails new Demo -d postgresql

The code above will automatically add the pg gem in your GemFile and create appropriate database.yml file:

development:
  adapter: postgresql
  encoding: unicode
  database: Demo_development
  pool: 5
  username: gotqn
  password: mypass

Note: You need to change the username and to specify the correct password if you have set such.

Then run rake db:create and start the rails server.

share|improve this answer
1  
+1, this was very helpful. Thanks! – Nathan Couch Jan 15 '14 at 22:45
    
I see lots of guides that say to add host: localhost to your development section in database.yml. In your example, will the development database not be on localhost? – Dennis Mar 28 '14 at 16:26
    
@Dennis I have not seen this. In my case, I am using it with localhost too. – gotqn May 7 '14 at 13:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.