After spending hours researching a method to easily transfer (backup / restore) a heroku postgresql database to a VPS, I finally found a quick way to do it that didn’t involve tons of errors about version mismatches, incomplete restores (using psql), and etcetera. I’m documenting this here in case I need to refer to it, and in hopes that it’s useful to someone.

This guide assumes you have the heroku CLI installed and associated with your heroku account. If you don’t have that configured, you should do it now.

heroku pg:backups capture --app appname

The above will initiate a backup of your database, you should replace “appname” with your heroku application’s name. Feel free to CTRL + C as it will continue to run. Go over to the heroku website and navigate to Databases > App Database > PG Backups, your backup should be visible and tagged as “Capturing”, when this is complete you can download the database and transfer it to the VPS where you intend to restore it.

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U deployer -d existing_db heroku_backup.dump

This begins the restore the process, replace “existing_db” with the database you want to upload to, “deployer” with the database user and “heroku_backup.dump” with the file name you downloaded off AWS. You should be prompted to enter the user’s password, if not, force it with the “-W” flag.

—
The above are steps I took to painlessly transfer a heroku database to a VPS.

References:

• https://devcenter.heroku.com/articles/heroku-postgres-backups
• https://gist.github.com/wrburgess/5528649