How to download your PostgreSQL database from Heroku to Local?

Solution

If you're only interested in the solution, here's what worked for me:

PGUSER=postgres PGPASSWORD=postgres_database  heroku pg:pull  postgresql-address-on-heroku localdbhere --app your-app-on-heroku

Problem

As a developer, I prefer having a local database esp. for debugging and breaking stuff rather than using the one Test/Acceptance/Staging as other colleagues might be using them. If you're using Azure Tables, for instance, you can use a local Azure Storage Emulator.

So how to do it with a Postgres database running on Heroku?

What I like about Heroku is that its technical documents are well-crafted and concise with examples.

First Attempt

Once you install the Heroku CLI tool, you can log in. However, in certain browsers and/or OS like the one I'm using right now, Ubuntu Jammy, the browser doesn't automatically open. It might be a permission issue but even with sudo, it doesn't work.

Thankfully, the URL with the connection string is displayed on the terminal. Once I copy and paste it, I get the Heroku login screen and can enter my credentials.

List of Issues

The next error I encounter is: `cannot login with my laptop username` which is "OEM".

I try to log in with the default Postgres username – same issue.

I don't understand the root problem but for each error I do get, I immediately look it up.

This eventually promptly helps me understand what's happening behind the scenes.

We need the local database user and password to be sent along so that Heroku will then create a local database with this user on our local machine.

Note: it's recommended that this local database doesn't exist prior to avoid data conflicting errors.

Peer Auth Issue

Finally, one last obstacle is the `Peer Auth Issue` with the user I'm using for the project.

On researching, I need to modify the auth method from peer to trust in the file below:

/etc/postgresql/14/main/pg_ident.conf

I try with Postgres user and it works. But I want to do with the user I'm using with the project database with.

But it doesn't work until I grant the necessary role.

Finally, I see the magic command:

heroku-cli: Pulling complete.