How to set up an RDS database for Rails on EC2

by Jason Swett,

This is part 5 of my series on how to deploy a Ruby on Rails application to AWS. If you found this page via search, I recommend starting from the beginning.

Overview of this step

This is the final step. First we’ll create an RDS database in the AWS console, then we’ll create our actual Rails application database.

Just before we cross the finish line we’ll precompile our assets as the very last step.

1. Create the database

Go to the RDS page in your AWS console. Click Create database.

On the next page, choose PostgreSQL.

Scroll down and select Free tier. If you’re following along with my hello_world repo, set the database name to hello-world. For a password, put whatever you want.

Leave everything else at its default. Click Create database at the bottom.

2. Connect Rails with your RDS database

If you’re using my hello_world app, make note of what’s in config/database.yml under production. If you’re using your own app, set the production section of your app’s config/database.yml to match what’s below.

production:
  <<: *default
  database: <%= ENV['RDS_DATABASE'] %>
  username: <%= ENV['RDS_USERNAME'] %>
  password: <%= ENV['RDS_PASSWORD'] %>
  host: <%= ENV['RDS_HOST'] %>
  post: 5432

The above config code reads environment variable values. Elsewhere we need to set environment variable values.

In our nginx config file, /etc/nginx/sites-enabled/default, we need to add the following, under the server section. This will allow nginx to read our env var values.

passenger_env_var RDS_DATABASE hello-world;
passenger_env_var RDS_USERNAME postgres;
passenger_env_var RDS_PASSWORD your-password;
passenger_env_var RDS_HOST your-endpoint.rds.amazonaws.com;
passenger_env_var RAILS_ENV production;

If you don’t know where to find your endpoint URL, it can be found under Connectivity & security on the detail page for your RDS database, as pictured below.

In addition to nginx being able to read our env var values, we also need the terminal to be aware of our env var values. Unfortunately I don’t know of a way to satisfy both nginx and the terminal in a way that doesn’t involve duplication. To bring the env var values into the terminal, add the following to /home/ubuntu/.bash_profile.

export RDS_DATABASE=hello-world
export RDS_USERNAME=postgres
export RDS_PASSWORD=your-password
export RDS_HOST=your-endpoint.rds.amazonaws.com
export RAILS_ENV=production

Now execute ~/.bash_profile and echo a value to verify that the env vars have been successfully set.

. ~/.bash_profile
echo $RDS_HOST

3. Create the app’s database

Even though we’ve created the RDS database, we still need to create the actual Rails database instance. (The RDS database is more like a container.)

rails db:create

Unfortunately this won’t work. We get an error about Yarn packages. We need to address this, and in order to address this we need to install Yarn.

curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | sudo apt-key add -
echo "deb https://dl.yarnpkg.com/debian/ stable main" | sudo tee /etc/apt/sources.list.d/yarn.list
sudo apt-get update
sudo apt-get install -y yarn

Now we can install the Yarn dependencies.

yarn

And now, finally, we can actually create the database.

rails db:create
rails db:schema:load

4. Precompile assets

Before we check what’s in the browser, let’s tail the logs.

tail -f log/production.log

If the database steps were successful, we should now be past any database-related errors. The error we see should be related to assets not being precompiled.

This is easily fixed by running the rails assets:precompile command.

sudo chown -R ubuntu:ubuntu .
rails assets:precompile

5. Verify success

When you visit your EC2 instance’s URL in the browser, it should now actually work!

If you try to add a person via the form, that should actually work too!

Congratulations. You now have a working Rails application on AWS.

  •  
  •  
  •  
  •  

32 thoughts on “How to set up an RDS database for Rails on EC2

  1. Andrew

    Hey Jason,

    Getting close, hopefully this will be my last question.

    I’m on Part 5, Step 3 Create the App’s Database.

    Should I be doing all of this just from my instance…
    ubuntu@ip-***-**-**-***:~$

    Or should I be running all of this from within my project…
    ubuntu@ip-***-**-**-***:/var/www/my-project$
    ?

    Either way I’m having trouble getting rails db:create working, but I’ll work through the errors soon enough!

    Reply
    1. Jason Swett Post author

      Depends on the step – some stuff needs to be run from your project directory, other stuff doesn’t. Doesn’t hurt to just run everything from your project directory if you’re not sure.

      As for `rails db:create`, my first question would be what exactly happens when you run `rails db:create` that tells you it’s not working?

      BTW I suggest we move our conversation over to the RDS post – that way people with similar problems might benefit.

      Reply
  2. Andrew

    Okay great, that’s what I figured so I’ve been running everything from my project directory.

    Apologies for the confusion re: location of this conversation, looks like we’re on the RDS post now though. Hopeful my struggles can help others in the future!

    As for when I rails db:create, here’s the error I’m getting…

    rails db:create
    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”?
    Couldn’t create ‘my_project_production’ database. Please check your configuration.
    rails aborted!

    If I figure out how to get past this before hearing back from you, I will make sure to post my fix. Excited to have my RDS database up soon!

    Reply
    1. Jason Swett Post author

      Okay, your error tells me that PostgreSQL is trying to connect locally rather than trying to connect to your remote RDS instance as it should.

      What that tells me is that for whatever reason your `config/database.yml` isn’t set up properly. Most likely your `RDS_HOST`, `RDS_DB_NAME` etc. environment variables aren’t getting set properly on your EC2 instance.

      I would double-check the step above where you set the `passenger_env_var` values and the step where you set env vars in `/home/ubuntu/.bash_profile`. One diagnostic step you can take is to run `echo $RDS_HOST` on the command line and see what you get. If you get nothing, that means the value isn’t set and Rails won’t be able to connect to your RDS instance.

      Reply
  3. Andrew

    I’m getting the correct endpoint when running echo $RDS_HOST. It’s possible that I’ve got my RDS_DATABASE name wrong though. As ridiculous as this sounds, I’m not sure that I know how to figure this out. My GitHub repo does not have the same name as my apps directory on my computer. I’m trying to sort this out now, hopefully this is the issue!

    Reply
    1. Jason Swett Post author

      The next question I would want to answer then is whether it’s not connecting because my creds are bad or it’s not connecting because of some problem with my EC2 instance configuration.

      On the command line I would do `psql my_db_name -U postgres -h my_rds_host` and see what happens. If it prompts me for a password and lets me connect, then my creds are good and the problem lies with the EC2 setup somewhere.

      Reply
  4. Andrew

    For some reason I got…

    Command ‘psql’ not found, but can be installed with:

    sudo apt install postgresql-client-common

    So I installed.

    Then I got,

    psql: could not translate host name “my_rds_host” to address: Temporary failure in name resolution

    Reply
    1. Jason Swett Post author

      Sorry, I didn’t mean literally “my_rds_host” or “my_db_name”, I meant replace those values with whatever your RDS instance’s hostname and db name are.

      And if it doesn’t connect, the first thing I would check is the security group associated with your RDS instance to see what ports it has open. I might even suggest temporarily allowing “all traffic” just so you know that the security group isn’t the thing causing the problem.

      Reply
  5. Andrew

    Apologies for the confusion. Ran with the proper inputs and got

    psql: could not connect to server: Connection timed out
    Is the server running on host “my_db_name” (***.**.*.**) and accepting
    TCP/IP connections on port 5432?

    Tried temporarily allowing “all traffic” but this didn’t change the connection.

    Would it matter if my database name was different than my GitHub repo & DB identifier’s name?

    Your patience is much appreciated!

    Reply
    1. Jason Swett Post author

      Happy to help. What I suggest is to run `psql my_db_name -U postgres -h my_rds_host` on your laptop, not on the EC2 instance.

      We’re trying to answer the question “Is the problem with the RDS instance or is the problem on the EC2 instance?” If we can successfully connect to your RDS instance from your laptop, we know the problem is somewhere on the EC2 instance. Right now we don’t know.

      Also, remember to replace `my_db_name` and `my_rds_host` with your actual database name and host URL. It looks from your error message like those might still be hard-coded to the placeholder values.

      Reply
  6. Andrew

    That time I’d understood and translated it back to “my_db_name”! Just ran that from my computer and got

    psql: could not connect to server: Network is unreachable

    So I’m not sure what’s going on there.

    I also tried re-running all of Part 4 and Part 5 within my app directory in my server to make sure everything was run in the same place. Now I’m up to this error…

    rails db:create
    rails aborted!
    Errno::EACCES: Permission denied @ rb_sysopen – /var/www/my-project/config/master.key

    Reply
    1. Jason Swett Post author

      If you’re getting “psql: could not connect to server: Network is unreachable” then I’m fairly certain there’s a problem with your RDS instance. If that’s true, then anything you do on your EC2 instance is going to be a waste of time until you get your RDS instance fixed.

      If you open your RDS instance’s security group back up to all traffic and still get the “psql: could not connect to server: Network is unreachable” error, then I would google “aws rds psql: could not connect to server: Network is unreachable” and see what you get.

      I would also suggest using an open port checker to see if your RDS instance is really reachable on the right port. I use this one: https://www.yougetsignal.com/tools/open-ports/

      Reply
      1. Andrew

        Will look into all of these suggestions, thank you! For now though, I have to sign off for the day. Will be back on tomorrow around 9am EST to get this all figured out. Have a great evening!

        Reply
          1. Jason Swett Post author

            Thanks for sharing. Setting the RDS instance to public is a good measure during troubleshooting. I would add though that when it comes time for actual production usage I’d suggest locking it down a little more than that.

  7. Muhammad Ahmad

    I am getting this error while running this command rails db:create
    could not connect to server: Connection timed out
    Is the server running on host “hello-world.c.us-east-2.rds.amazonaws.com” and accepting
    TCP/IP connections on port 5432?
    Couldn’t create ‘hello-world’ database. Please check your configuration.
    rails aborted!

    I have made it public also. But didn’t work out.

    Reply
    1. Jason Swett Post author

      Are you able to manually connect to your RDS instance from your dev machine by running `psql my_db_name -U postgres -h my_rds_host`? (You’ll of course need to change `my_db_name` and `my_rds_host` to your actual db name and host.)

      Reply
      1. Muhammad Ahmad

        That command is also giving the same error. I tried it many times. Is there any other way to connect with RDS on aws?

        Reply
          1. Muhammad Ahmad

            I have changed security groups and also check it with different port. But still not able to connect it. Can you please help me?

          2. Jason Swett Post author

            Well, your current problem is that you don’t know how to get your RDS instance’s port 5432 open. That’s the thing you have to figure out before anything else. There’s something there that you don’t understand. Maybe you’re misunderstanding how the security groups work. I think the next thing I would recommend for you if you’re stuck is to put up a Stack Overflow question.

          1. Jason Swett Post author

            Okay, if port 5432 is closed then there’s your problem. You’ll need to adjust your RDS instance’s security group such that the open port checker will show port 5432 open.

  8. Saeed shaban abdel razek

    welcome dear , thak you more for explains but pleasen i have the error take more tie and i cannot resolve
    ——————————–

    We’re sorry, but something went wrong.
    The issue has been logged for investigation. Please try again later.

    Error ID:
    9a154640
    Details:
    Web application could not be started by the Phusion Passenger application server.

    Please read the Passenger log file (search for the Error ID) to find the details of the error.

    You can also get a detailed report to appear directly on this page, but for security reasons it is only provided if Phusion Passenger is run with environment set to development and/or with the friendly error pages option set to on.

    For more information about configuring environment and friendly error pages, see:

    Nginx integration mode
    Apache integration mode
    Standalone mode
    This website is powered by

    ip : http://18.234.71.79/

    Reply
  9. vedanta

    Thank you soooooo much! You saved my life and mind from being completely blown with goddamn Elastic Beanstalk!!
    Working directly with an EC2 instance is so much easier! Thank you, everything went smoothly for Ruby 2.7.1 (as a small note I had to install the last version of NodeJS as default one was incompatible O_o)

    Reply
      1. vigdev

        I can ditto the comment. IDK what is up with Beanstalk but this is the way to go and this was the first guide that actually led me to an app published on aws so thanks.

        ruby 2.7.1
        rails 6.0.3.2

        Reply

Leave a Reply to Muhammad Ahmad Cancel reply

Your email address will not be published. Required fields are marked *