How to backup a PostgreSQL database with pg_dump from Hasura / Heroku instance

We needed to create a backup of our server, as I begin my transition away from the startup I’ve been working at for the past 2 years. I wanted others to know how simple it is to store a backup copy of your database.

We hopped on to Hasura’s partnership with Heroku and we were able to instantly set up a cloud server fully fitted out with a unified GraphQL and REST API in minutes. Now that option has since been discontinued and taken over by their new partner Neon instead.

At the end of this, you’ll have a .bak file which you can use pg_restore to reinstate to your new backend provider or just feel confident that your data is safely stored on your machine.

— — — — — — Edit (start) — — — — — —

Transfering PostgreSQL db from Heroku to Neon

I have to make a few revisions, the pg_restore command did not work. In our specific case we did not have to use pg_dump or pg_restore. We’re currently testing Hasura’s new partner Neon so this only applies if you are transferring your database from Heroku to Neon. We ran this command:

heroku pg:pull --app heroku-app-12345 postgresql-shape-12345 postgres://<neon-user>:....aws.url/neondb

This command directly transfers your PosgreSQL db on Heroku and uploads it to the PostgreSQL connection string at Neon

If you still want to know how to make a pg_dump, this snippet below this edit should still work for you

— — — — — — Edit (end) — — — — — —

Here are the steps to run a pg_dump backup:

1. Run heroku login in the terminal
2. Get the PostgreSQL connection string with the commandheroku config:get --app=APP_NAME
3. Then just run pg_dump "postgres://<user>:<hostname>:<port>/<dbname>" --file=dumpfile.bak -Fc -Z 6 -v

Make sure to include the double quotes

See https://neon.tech/docs/import/import-from-postgres#pg_dump-with-pg_restore for more information

Finally, your file should be in the same folder called dumpfile.bak

If you ever need to fallback to this dump you can simply run pg_restore -d postgres://[user]:[password]@[hostname]/<dbname> -Fc --single-transaction dumpfile.bak.gz -c -v