Cannot connect to Amazon PostgreSQL RDS via EC2

Question

I currently have a PostgreSQL database setup on Amazon RDS. The database is connected to an Elastic Beanstalk server that runs a Rails application, and the Rails app works fine with it (on the production site, I can modify the database just fine. I can also view the database over SQL workbench just fine, and I can see that the changes I have made on the production site reflect in the database).

So i know the database is working correctly, and that my elastic beanstalk server connects to it fine, and my Rails app connects to it fine too.

However, when I try to connect to the database via my EC2 instance (note: this instance is in a different region (Ohio) than my EB and RDS instance (N. Virginia), I get this error:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The directory /var/run/postgresql exists, but that file does not exist. I tried searching for that file from root, and it turns out that it doesn't exist anywhere.

This is a problem because I need to be able to seed the database remotely, and trying to run rake db:seed from the console produces this error. The error also arises when I run the rails console in production, and try to view anything that is part of the database.

This is my config/database.ymlfile:

default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

test:
  <<: *default
  database: db/test.sqlite3

production:
    <<: *default
    adapter: postgresql
    encoding: unicode
    database: <%= ENV['RDS_DB_NAME'] %>
    username: <%= ENV['RDS_USERNAME'] %>
    password: <%= ENV['RDS_PASSWORD'] %>
    host: <%= ENV['RDS_HOSTNAME'] %>
    port: <%= ENV['RDS_PORT'] %>

This is how EB knows to connect to the PG database upon deployment.

I have tried to edit the security rules for my RDS to accept PostgreSQL connections from the EC2's public IP, but that doesn't seem to work.

Can anyone help me with this?

Edit:

Ok, I can connect to the remote database using the psql command with options specifying the server just fine. I tested it by executing a simple query, and I am positive that the connection is established. I am now unsure how to tell the server to connect to it automatically, though.

How do you connect. Provide the exact command. An educated guess: you don't specify what host to connect to. — zerkms
@zerkms Edited my post. Elastic Beanstalk connects to the server upon deployment — Tetramputechture
@zerkms There is no psql command. All the database commands are processed through the Rails files, and on debployment (eb deploy), db:migrate and db:seed should be automatically executed. Am I missing a command here? Sorry, I'm a little bit new to how everything connects. — Tetramputechture
you try to connect to postgres on local socket. chnage config to include connect string to RDS — Vao Tsun

TJ Biddle TJ Biddle · Accepted Answer · 2017-11-29T18:11:31

It looks like your application is attempting to connect to PostgreSQL on the localhost; it seems like your config/database.yml file is correct in that it is looking for the environment variable RDS_HOSTNAME - so my best guess would be that you are not setting that and it is defaulting to your localhost as it is empty.

You'll need to set this environment variable, along with the other items stated there (Database, user, pass & port) in order for this to work.

This StackOverflow answer explains how to set environment variables for Elastic Beanstalk.

Cannot connect to Amazon PostgreSQL RDS via EC2

2 Answers