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, Nov 29 '17 at 07:15
@zerkms Edited my post. Elastic Beanstalk connects to the server upon deployment — Tetramputechture, Nov 29 '17 at 07:42
@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, Nov 29 '17 at 08:20
you try to connect to postgres on local socket. chnage config to include connect string to RDS — Vao Tsun, Nov 29 '17 at 08:25
@VaoTsun Can you help me with that, please? I successfully connected via the psql command, but I don't know how to save that. You can post how to do this as an answer since that should solve my problem most likely (I just need to configure the ec2 instance to connect to psql) — Tetramputechture, Nov 29 '17 at 14:54
to connect with psql, first run `sudo su - postgres` and then `psql` — Vao Tsun, Nov 29 '17 at 15:51
@VaoTsun I can successfully connect to the remote database when I run `psql` with the appropriate options; running `SELECT * FROM users` (where `users` is a relation in my database) successfully executes. Now, how I can seed the database? That is, how can connect to the database with a plain `psql` command? — Tetramputechture, Nov 29 '17 at 16:01

score 1 · Answer 1 · answered Nov 29 '17 at 18:11

1

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.

answered Nov 29 '17 at 18:11

TJ Biddle

6,024
6
40
47

The application connects to the server just fine, though. I have gone into EB via SSH and checked the variables and they are set. The problem doesn't seem to lie with EB, but getting the EC2 instance to seed the database (when I try to run `RAILS_ENV=production rails db:seed` I get the error that it can't connect) – Tetramputechture Nov 29 '17 at 18:17
Are you in the application directory, on the same EC2 server? If you run `env` can you see the same environment variables listed? – TJ Biddle Nov 29 '17 at 18:20
When I run `env` straight from the EC2 instance, those variables are not defined. But when I run `env` from the EB instance via `eb ssh`, those variables are defined. – Tetramputechture Nov 29 '17 at 18:35
Can you run `RAILS_ENV=production rails db:seed` from `eb ssh` then? – TJ Biddle Nov 29 '17 at 18:38
I had to go into where the app was hosted inside EB, and run it from ther and THAT WORKED!!!! – Tetramputechture Nov 30 '17 at 02:20
Heh @Tetramputechture - that was in my second comment :-) Great! – TJ Biddle Nov 30 '17 at 03:02

score 0 · Answer 2 · answered Nov 30 '17 at 02:22

0

I solved this issue by accessing my EB instance via

eb ssh

and going to my app directory located in

var/app/current

and running

rails db:seed

It correctly seeded the production database with my values in seeds.rb

answered Nov 30 '17 at 02:22

Tetramputechture

2,911
2
33
48

Cannot connect to Amazon PostgreSQL RDS via EC2

2 Answers2