I’m a big Heroku fan. I used it’s hosted Redis and Postgres services for my startup and it scaled incredibly well and saved me a ton of time not having to ever worry about devops.

One of things I loved about Heroku was it’s CLI API. You could very easily manage infrastructure through a very thoughtful CLI.

For instance, a common process I would run was:

1. Dump production database to my local
2. Import the dump into my local Postgres
3. Clean & sanitize the data

This process looked something like:

curl -o latest.dump `heroku pg:backups public-url`

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d app latest.dump

bundle exec rake app:sanitize

That first line was all you needed to download a production copy of your DB.

I’ve been playing around with AWS for a side project and replicating a similar process was surprisingly challenging. AWS RDS (Amazon hosted relational databases) has a concept of ‘snapshots’ which sounds like exactly what you’d want, but all of the instructions I found looked complicated and there wasn’t a simple GUI or CDK interface to create one. Very frustrating!

The easiest solution I was able to find is to tunnel a port from your local to the RDS instance through the EC2 instance (or a bastion host, if you have one) connecting to the RDS DB.

Here’s what this looks like:

# don't bind to 5432 on your local, you probably have pg running on that port already
local_host=localhost:5433

# pull the remote host from your db connection string attached to your app
remote_host=domain.hash.us-east-2.rds.amazonaws.com:5432

# you shouldn't be abler to access RDS
# proxy connection to RDS from your local via your EC2 box
ssh -N -L $local_host:$remote_host ubuntu@domain.com

# assumes that `postgres` is your local username

# on your local, in another terminal, you'll run this command to dump the entire remote database
# you'll need your pg password on hand in order to run this command
pg_dump -c -p 5433 -h localhost -U postgres -f ./latest.dump postgres

# after that's completed, you can pull the database into your local
psql app_dev < ./latest.dump

Resources:

• https://stackoverflow.com/questions/14916899/download-rds-snapshot
• https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b
• https://medium.com/@deepspaceprog/how-to-connect-via-ssh-to-an-amazon-rds-instance-running-postgresql-5e7661cdd37e