Cloud9 postgres

Question

I am trying to set up a postgres database in a Rails app in Cloud9.

I have followed the instructions here: https://docs.c9.io/setting_up_postgresql.html and set up a database called cc_database.

My database.yml file looks like this:

development:
  adapter: postgresql
  encoding: SQL_ASCII
  database: cc_database
  pool: 5
  username: postgres
  password: password

When I run rake db:setup I get the following error:

 PG::ConnectionBad: FATAL:  Peer authentication failed for user "postgres"

I am quite new to all this, so any advice would be much appreciated.

Answer 1

Do the following steps:

1. Create a new username and password for postgresql on cloud9:

   $ sudo service postgresql start
   $ sudo sudo -u postgres psql
   postgres=# CREATE USER username SUPERUSER PASSWORD 'password';
   postgres=# \q
   

2. Create ENV variables on cloud9:

   $ echo "export USERNAME=username" >> ~/.profile
   $ echo "export PASSWORD=password" >> ~/.profile
   $ source ~/.profile
   

   My database.yml for rails 4.2.0 on cloud9:

   default: &default
     adapter: postgresql
     encoding: unicode
     pool: 5
     username: <%= ENV['USERNAME'] %>
     password: <%= ENV['PASSWORD'] %>
     host:     <%= ENV['IP'] %>
   
   development:
     <<: *default
     database: sample_app_development
   
   test:
     <<: *default
     database: sample_app_test
   
   production:
     <<: *default
     database: sample_app_production
   

3. Include the gem pg in Gemfile and install:

   gem 'pg', '~> 0.18.2'

   $ bundle install
   

4. Update template1 postgresql for database.yml on cloud9:

   postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
   postgres=# DROP DATABASE template1;
   postgres=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
   postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
   postgres=# \c template1
   postgres=# VACUUM FREEZE;
   postgres=# \q
   

5. From command line run:

   bundle exec rake db:create
   

Answer 2

The postgresql in cloud9 is setup to authenticate with peer when localhost connection. So the quickly resolution is change the user in your database.yaml to current user. In my case the name of user is ubuntu. To see your current user use the command

$ echo $USER

So a list of command in terminal is .

$ sudo su - postgres
$ createuser ubuntu -dslP
$ Enter password for new role: **same password from your yaml file**
$ Enter it again:

Set your yaml file like this

development:
  adapter: postgresql
  encoding: SQL_ASCII
  database: cc_database
  pool: 5
  username: ubuntu
  password: password

Now you can run

rake db:create
rake db:migrate

Answer 3

Use the username "ubuntu" with a blank password. My database.yml looks like this:

development:
adapter: postgresql
encoding: unicode
database: myflix_development
pool: 5
username: ubuntu
password:

test:
adapter: postgresql
encoding: unicode
database: myflix_test
pool: 5
username: ubuntu
password:

If it then complains the password is wrong, try changing the password using Cloud9's instructions:

In the command line, type: sudo sudo -u postgres psql

postgres=# \password
Enter new password: leave it blank and press enter
Enter it again: leave it blank and press enter
postgres=# \q

I'm pretty new to this to. Hope that works!

Answer 4

How to setup PostgreSQL & Rails on Cloud9

At time of writing, Cloud9 has PostgreSQL pre-installed, so you won't need to install it yourself. However, its not running by default, so you will need to start it with this command in the terminal:

sudo service postgresql start

Change the PostgreSQL password to 'password' (or choose a different password):

sudo sudo -u postgres psql

# This will open the psql client.

# Type \password and press enter to begin process
# of changing the password:
postgres=# \password

# Type your new password (e.g. "password") and press enter twice:
Enter new password: 
Enter it again: 

# Password changed, quit psql with \q
postgres=# \q 

Edit your config/database.yml to be:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

  # Important configs for cloud9, change password value
  # to what you entered in the previous psql step.
  template: template0
  username: ubuntu
  password: password

development:
  <<: *default
  database: your_app_name_development

test:
  <<: *default
  database: your_app_name_test

production:
  <<: *default
  database: your_app_name_production
  username: your_app_name
  password: <%= ENV['YOUR_APP_NAME_DATABASE_PASSWORD'] %>

(Note the template, username, and password configs in the default section above are essential).

Add the pg gem to your Gemfile:

gem 'pg'

Run bundle install.

Remove the sqlite gem from your Gemfile (and optionally delete the db/*.sqlite3 files).

Create the database, load schema.rb, and seed the database using the db:setup task:

bundle exec rake db:setup

# Run bin/rake -AD db to see all db-related tasks

Start or restart your rails app and check it is working.

Note, the non-seed data from your old sqlite database will not be present in the new database.

If you ever want to use the psql client to interact with PostgreSQL directly, in the terminal run psql or run bin/rails db.

Answer 5

For me, doing the steps in Cloud9 workspace setup with Rails and Postgresql wasn't enough. It was passing in my user, but not the password. echo $USERNAME was coming up with nothing.

Solution

$ sudo su - postgres
$ createuser ubuntu -dslP

Then I did this:

sudo sudo -u postgres psql
postgres=# \password
Enter new password: entered a real password
Enter it again: entered it again
postgres=# \q

Then I did this in my yaml file (note I killed the host part):

development:
  adapter: postgresql
  encoding: unicode
  database: my_database_name
  pool: 5
  username: ubuntu
  password: actual_password

Then I was able to create my database with:

rake db:create

And my Rails server started without any more hiccups.

Answer 6

Found the solution. Needed to edit the pg_hba.conf file to change the authentication from peer to md5 like this:

local    postgres     postgres     md5

It's hard to find the file as it can only be accessed via the terminal in cloud9. You cannot find it in the file tree.

If you type the following into postgres it will show you the location

SHOW hba_file;

You can then find and edit in in vim via the terminal.

Answer 7

A short version when you have rails app prior to creating any databases:

add the gem 'pg' remove gem sqlite3

$ bundle install
$ gem install pg

Then,

$sudo service postgresql start
$psql -c "create database myapp_development owner=ubuntu"

as per https://community.c9.io/t/how-do-i-set-up-postgresql-on-c9-for-my-rails-app/2614/4

Changing myapp for whatever name. Then, copy-paste below, changing myapp for whatever name.

/myapp/config/database.yml
    development:
      adapter: postgresql
      encoding: unicode
      database: myapp_development
      pool: 5
      username: ubuntu
      password:
      timeout: 5000

As above comments, no password needed, username can stay ubuntu.

$rake db:migrate

If you're using heroku, you won't need the production section in database.yml