How To Dump Local Database In Postgresql To Heroku

Tweet Vote on HN

I tried to follow this guide by Heroku but the solution was lacking a little bit of context.

This is what I did:

1. Export your local database

First get the name of the database checking your database.yml file on rails.

you should export your local database using pg_dump.

PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

ommit PGPASSWORD=mypassword if you don’t have one and also ommit the user if you don’t have one.

My final command ended up like this:

pg_dump -Fc --no-acl --no-owner -h localhost database_name > mydb.dump

2. Upload the dump to a cloud storage service like S3

Upload your file and make it publicly accesible.

Most of the guides will recommend S3 but Dropbox is ok just don’t use Drive since it will corrupt your upload and it will not work most of the time.

After all of that get the public link.

3. Restore to your Heroku app

heroku pg:backups:restore 'YOUR_LINK_TO_FILE' DATABASE_URL

DATABASE_URL must be left like that. Just make sure to do all of this inside your app folder so that Heroku knows which app you want to restore.

It will ask you to confirm.

You’ll know if it works because the confirmation message should be: Restoring... done

Let me know if you want me to create a guide to use S3 since it’s quite a challenge if you are starting out.

Tweet Vote on HN

Did you learn something useful?

Then consider signing up for my newsletter. It's a collection of fascinating finds, usually about psychology, technology, finances, philosophy, and whatever else catches my interest. I also include new articles, and book notes.