Trying to switch from SQLite to PostgreSQL

Question

I was using SQLite in development and test, and PostgreSQL in production on Heroku. I would like to replace SQLite with PostgreSQL. I am programming in the Cloud9 environment (Rails 4). I have no data that I could potentially lose.

What did I do:

First, I edited the database.yml:

default: &default
      adapter: postgresql
      encoding: unicode
      pool: 5
development:
  <<: *default
  database: app_development
test:
  <<: *default
  database: app_test
production:
  <<: *default
  database: app_production

Then:

• In the Gemfile I moved gem 'pg' from production environment only to all environments and removed gem 'sqlite3'
• I ran bundle install.
• I ran sudo service postgresql start
• I ran sudo sudo -u postgres psql
• And entered create database "app_development";
• Entered \q

---

Update: I added the following additional steps:

• I created a new user in psql with CREATE USER my_username SUPERUSER PASSWORD 'my_password';
• In database.yml I added the username and password
• In database.yml I added host: myurl.c9.io
• I entered in psql: GRANT ALL ON DATABASE app_development to my_username

Running sudo sudo -u postgres psql and then \list produces:

      Name          |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
 postgres           | postgres | SQL_ASCII | C       | C     | 
 template0          | postgres | SQL_ASCII | C       | C     | =c/postgres          +
                    |          |           |         |       | postgres=CTc/postgres
 template1          | postgres | SQL_ASCII | C       | C     | =c/postgres          +
                    |          |           |         |       | postgres=CTc/postgres
 app_development    | postgres | SQL_ASCII | C       | C     | 

I don't see my username here as owner of app_development...

Error: Running rake db:migrate times out, stating PG::ConnectionBad: could not connect to server: Connection timed out Is the server running on host "myurl.c9.io" (ip address) and accepting TCP/IP connections on port 5432?.

Why might the connection with the PostgreSQL be failing?

Answer 1

Replace content of database.yml file to:

default: &default
  adapter: postgresql
  host: localhost
  username: yourusername
  password: yourpassword
  timeout: 5000
  port: 5432
development:
  <<: *default
  database: app_development
test:
  <<: *default
  database: app_test
production:
  <<: *default
  database: app_production

Because you use Heroku then you can leave production section as is