A Beginners Guide To Backing Up A Heroku Hosted PostgreSQL Database Locally

You'd never guess by looking at the Heroku's Postgres dashboard that you can make backups of your hosted databases whenever you want without paying for the higher tiers. All you need is the correct version of the pg_dump tool installed on your local machine.

I prefer making backups this way because it gives me more control over what data is included and how the backup is formatted. Backups made through Heroku are created and stored in their platform specific compiled file format. Meaning that even if you download a backup made through the Heroku dashboard, you can't use it to recreate your database locally or on any other service.

Step 1: Collecting The Required Info From Heroku

The first thing you need to do is find the version of Postgres your Heroku Database is running on and its connection URI:

1. Login to your Heroku account
2. Click on the app you want to backup (if it's within a pipeline you'll have to click into the pipeline first)
3. Click the Heroku Postgres link (it's below Installed add-ons)
4. Make a note of the number that comes after Version in the Health section
5. Go to the settings tab and click the button that says View Credentials...
6. The value you're going to need is the URI (just keep this page open, you can come back and copy it in a minute)

Step 2: Confirming You Have pg_dump Installed Locally

Next you should confirm that you have the correct version of pg_dump installed.

1. Open a command line terminal (CTRL+T on most machines)
2. Type pg_dump --version into your terminal and hit ENTER

The Good

If you get something that starts pg_dump (PostgreSQL) and then a number which matches the Version number you noted down in step 4 above, congrats, you may proceed without issue.

The Bad

If you get something that starts Command 'pg_dump' not found, then you don't have pg_dump installed at all. So you'll need to locally install whichever version of Postgres your Heroku instance is running in order to proceed. Of course you could also decide it's not worth the effort and give up at this point, totally your call to make, I'll respect your decision either way.

The Annoying

Unfortunately, I get this as a result: pg_dump (PostgreSQL) 14.12. Meaning I do have the tool installed but my default local version doesn't match the version on my Heroku hosted database. So I need to figure out if I have the correct version installed, but just not set as my default, or if I need to install the dang thing.

Checking For Multiple Versions of PostgreSQL and THE TOOL

The most direct way to find the paths to all the, *sigh*, pg_dump versions on your system is probably to use find.

In a terminal type: sudo find / -name pg_dump 2>/dev/null and hit ENTER. (YES I'm Sure!)

Alternatively you can try running: ps aux | grep postgres

This will only show you actively running instances of postgres but in my case, and I suspect many others, I end up with the same answer. Turns out I'm running versions 14, 15, and 16 of Postgres simultaneously. I'm sure there's a good reason for that.

Step 3: Putting It All Together

Now all you need to do is run pg_dump locally, supplying your connection URI as the database name, and your local pg_dump tool will reach out over the internet and backup your externally hosted database to a local file. It still blows my mind how easy stuff like this is!

PG_DUMP takes 2 main inputs:

1. The name of your database (we're going to use our full connection URI string since our database is hosted externally)
2. The path you want to save the backup to

If your default pg_dump version is the same as your Heroku version then the command you want to run should look like this:

If you're using a different pg_dump version then your system default, you'll need to replace the first part with the path to the actual binary file. Here's a look at my actual command with all the sensitive parts of the connection URI removed:

That should do it! I hope you found this helpful.