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
– Haider Ali, Commented May 13, 2015 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
– Nick, Commented May 13, 2015 at 20:35

Sasha · Accepted Answer · 2015-05-13 20:41:54Z

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, 2015 at 20:41

Sasha

20.9k9 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nick Over a year ago

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?

Sasha Over a year ago

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.

Nick Over a year ago

Thanks, I added it but now got the error message PG::ConnectionBad: FATAL: Peer authentication failed for user "here_states_my_username"

Sasha Over a year ago

This is anther issue. Is PG on your local machine? If not then you have to change host. Take a look here: stackoverflow.com/questions/9987171/…

Nick Over a year ago

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.

|

Collectives™ on Stack Overflow

Trying to switch from SQLite to PostgreSQL

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related