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, May 13 '15 at 20:28
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, May 13 '15 at 20:35

score 1 · Accepted Answer · answered May 13 '15 at 20:41

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

answered May 13 '15 at 20:41

Sasha

20,424
9
40
57

But wouldn't that be unsafe? Shouldn't I add instead `password: <%= ENV['PASSWORD'] %>`?. If yes, how should I "hard code" my password? Is this with `echo "export PASSWORD=fill_in_my_password" >> ~/.profile`? – Nick May 13 '15 at 20:44
This is your development evn then your data must be insensitive. ENV['PASSWORD'] used on production only but in your case Heroku doing it for you so no worries. – Sasha May 13 '15 at 20:47
Thanks, I added it but now got the error message `PG::ConnectionBad: FATAL: Peer authentication failed for user "here_states_my_username"` – Nick May 13 '15 at 20:53
This is anther issue. Is PG on your local machine? If not then you have to change host. Take a look here: http://stackoverflow.com/questions/9987171/rails-3-2-fatal-peer-authentication-failed-for-user-pgerror – Sasha May 13 '15 at 20:58
I added `host: myurl.c9.io`. Running `rake db:migrate` now 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?` I also did the `GRANT ALL ON DATABASE [dbname] to [usrname]` suggested in the other topic, but that made no difference. What might be important is that when I run `psql` is says `psql: FATAL: role "ubuntu" does not exist` instead of `psql: FATAL: role "your_username" does not exist`, which is mentioned in the other topic. – Nick May 13 '15 at 21:12
Looks like firewall issue. Was you able to connect to the db directly (without rails) from your machine? – Sasha May 13 '15 at 21:43
Yes, using `sudo sudo -u postgres psql` I can connect to the db. – Nick May 14 '15 at 14:25
I also contacted Cloud9 and they responded host should be 127.0.0.1 or 0.0.0.0. The reason the external hostname won't work is because only port 8080 is forwarded outside your workspace. Setting host accordingly solved it. – Nick May 15 '15 at 08:50

Trying to switch from SQLite to PostgreSQL

1 Answers1