1

I've been trying to make the switch to PostgreSQL (from SQLite). I'm developing in the environment of Cloud9. However, on db migration I get the error message:

PG::ConnectionBad: could not connect to server: Connection timed out. Is the server running on host "mydomain.c9.io" (IP address) and accepting TCP/IP connections on port 5432?

I've been reading all sorts of previously asked questions on this topic but without success. Does anyone see what might be going wrong?

Do I perhaps need to makes change to the pg_hba.conf file? The current version of the file:

# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer  
# IPv4 local connections:
host    all             all             xxx.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local  replication     postgres                                peer
#host   replication     postgres        xxx.0.0.1/32            md5
#host   replication     postgres        ::1/128                 md5

database.yml:

default: &default
  adapter: postgresql
  encoding: unicode
  host: 'mydomain.c9.io'
  pool: 5
  username: my_username
  password: my_password
development:
  <<: *default
  database: app_development
test:
  <<: *default
  database: app_test
production:
  <<: *default
  database: app_production

I have gem 'pg' in the Gemfile and have run bundle install. Also, I have created the database app_development (used sudo service postgresql start, sudo sudo -u postgres psql and then create database "app_development";) as well as the database app_test. And lastly, I created a new user in psql with CREATE USER my_username SUPERUSER PASSWORD 'my_password';.

Using sudo nano /etc/postgresql/9.3/main/postgresql.conf I also set listen_addresses = '*' but that made no difference.

What might be causing the error message?

Nick
  • 3,496
  • 7
  • 42
  • 96

1 Answers1

1

Are you sure that your cloud 9's has postgres port(5432) open for access? I assume the cloud 9 host is not listening on port 5432. You can check out this link for detais

http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

Amit Badheka
  • 2,677
  • 4
  • 19
  • 29
  • I understand from there that I need to change the file pg_hba.conf`. However, I can't open the file. Running `SHOW hba_file;` produces `/var/lib/postgresql/9.3/main/pg_hba.conf` (so the file exists) but how to open it? (it isnt visible in the file tree). – Nick May 14 '15 at 10:50
  • Cloud9 itself basically says nothing about needing to make changes for it to listen the the port: https://docs.c9.io/v1.0/docs/setting-up-postgresql – Nick May 14 '15 at 10:57
  • What does that mean? If I enter `sudo sudo -u postgres psql` in the terminal (which means I then get sudo access?) and then enter `vim /var/lib/postgresql/9.3/main/pg_hba.conf` nothing happens. Only `postgres=#` changes to `postgres-#`. – Nick May 14 '15 at 11:24
  • Rather sudo nano /var/lib/postgres/9.3/main/pg_hba.conf would do. – Amit Badheka May 14 '15 at 11:39
  • Thanks, after some trial and error `sudo nano /etc/postgresql/9.3/main/pg_hba.conf` opened the file. Not sure what changes to apply though to the configuration file (added the file to the original post). – Nick May 14 '15 at 11:55
  • Using `sudo nano /etc/postgresql/9.3/main/postgresql.conf` I also set `listen_addresses ='*'` but that also made no difference. – Nick May 14 '15 at 17:45
  • Cloud9 typically opens port 8080 for user use. Is there any way you can get it to try to connect on port 8080? – Brady Dowling May 14 '15 at 21:05
  • According to the link you shared,http://stackoverflow.com/questions/26545746/cloud9-postgres, you need to change authentication from peer to md5 and it should work. – Amit Badheka May 15 '15 at 06:20
  • 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:47