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:
- Dump production database to my local
- Import the dump into my local Postgres
- 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 firstname.lastname@example.org # 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