I need to point my Heroku application to my AWS RDS database. My RDS database is up and running and has a security group with 0.0.0.0/0 access.
Currently, I've removed my Heroku postgreSQL database and I am attempting to point my Heroku application to my RDS database. For some reason my application is crashing. The step that I believe I am on is setting my DATABASE_URL on the Heroku side.
Let's say that my database credentials are:
db instance: mydb
dbname: mydb
user: wcronyn
pass: password
I've tried:
heroku config:set DATABASE_URL=postgres://wcronyn:[email protected]:5432/mydb
and I've attempted to set the permissions by downloading the .pem file into my config folder and then referencing it:
DATABASE_URL=postgres://wcronyn:[email protected]:5432/mydb?sslca=config/amazon-rds-ca-cert.pem&sslmode=require&encrypt=true
I have tried these two database urls but my application keeps crashing.
Can someone outline the steps that I need to take to successfully host my RDS database and point my application to it?
The following steps worked for me (Feb 2017), given the following setup:
heroku-app-stage
)stage
)postgresql://username:password@awsrdshost:5432/dbname
There are broadly four steps to this:
Download and install Amazon RDS SSL root certificate
git push stage master
)heroku run bash --app heroku-app-stage
to see your files in the dyno)Configure Heroku to refer to root certificate
heroku-app-stage
, go to Settings tab, and click on Reveal Config Vars?sslrootcert=rds-combined-ca-bundle.pem&sslmode=require
. The new value should now be postgresql://username:password@awsrdshost:5432/dbname?sslrootcert=rds-combined-ca-bundle.pem&sslmode=require
Note that this answer uses a root certificate; there may be other options which may be what you want in which case refer to the following SO:
How to connect to a remote PostgreSQL database with Python
Enable SSL on your RDS instance
psql postgres -h awsrdshost -p 5432 -U username
, you should see SSL in the connection detailsConfigure RDS security group to allow all incoming IP ranges
Note: instructions are only relevant if you're using an RDS setup that uses VPC Security Groups
That's it!
Links to the reference pages used:
Amazon's guide to SSL on Postgres http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL
Heroku's (very short) guide to Amazon RDS https://devcenter.heroku.com/articles/amazon-rds
I figured out how to point heroku app to AWS RDS database. This allows me to have a giant database, test out Amazon's free tier for a year and have more customization over my database instance. This answer is in response to @pseudopeach question (pardon the delay).
So this is a pretty simple set up. To configure RDS for Heroky you need to know heroku well and you need to know AWS VERY well.
a) set up your region. Pick the region closest to you e.g. US EAST (Ohio)
b) then click services tab and select rds
c) i have a db ts micro (i think that's the free tier option)
d) do the rds set up and after it is up and running you click "instance actions" and see details
e) here you will be able to view your db-username, dbname, endpoint (which for me is a url similar to this [dbname].[randomstring].us-east-1.rds.amazon.com) and port number. You need these things plus you db password for the heroku side.
a) go to your heroku app on heroku.com, then settings b) click revealconfig variables
typical heroku variables look like this:
DATABASE_URL xxxxxx
HEROKU_POSTGRESQL_VIOLET_URL xxxxxx
LANG xxxxxx
RACK_ENV production
RAILS_ENV production
SECRET_KEY_BASE xxxxxx
you need to do change these up pretty drastically
DATABASE_URL [note: this changes to a different and long url – mine looks like this broken down for easy understanding]
postgres://
[db-username]:
[your db password]
@[endpoint]:
[your db port number]/
[db name]
?sslca=config/amazon-rds-ca-cert.pem
&sslmode=require
&encrypt=true
example database url:
postgres://jdoe:supersecretpassword@mydb.coua7574xvna.us-east-1.rds.amazonaws.com:5432/mydb?sslca=config/amazon-rds-ca-cert.pem&sslmode=require&encrypt=true
EXTERNAL_DATABASE amazon-rds-ca-cert.pem
EXTERNAL_DATABASE_CA amazon-rds-ca-cert.pem
LANG (same)
RACK_ENV (same)
RAILS_ENV (same)
RDS_DB_PASS [your db password]
RDS_DB_PORT [your db port number i.e. 5432]
RDS_READS_DB_NAME [db name]
RDS_HOST [end point url]
RDS_USER [db-username]
This worked for me and I got a free year of RDS for a database way over 10,000 rows (which I believe is the free tier limit on heroku). I used postgreSQL as my database, so these configs might be biased toward postgres.
I did exactly the same that Andy G answer said.
However I ran into the following error:
no pg_hba.conf entry for host "XX.XX.XX.XX", user "username", database "dbname", SSL off
To fix this issue on Configure Heroku to refer to root certificate step 2:
Instead of adding ?sslrootcert=rds-combined-ca-bundle.pem&sslmode=require
add ?ssl=true&sslrootcert=rds-combined-ca-bundle.pem&sslmode=require
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With