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?

you didn't mention username/password in your database.yml .... — Haider Ali
How should I set the password up? I have entered sudo sudo -u postgres psql, then \password and then entered my new password. In database.yml I understand I should include username: <%= ENV['USERNAME'] %> , password: <%= ENV['PASSWORD'] %> and host: <%= ENV['IP'] %>. In what file should I then actually enter my password (perhaps in config\secrets.yml?)? And what should the other two variables be? — Nick

Sasha Sasha · Accepted Answer · 2015-05-13T20:41:54

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

Trying to switch from SQLite to PostgreSQL

1 Answers