How to download your PostgreSQL database from Heroku to Local?
This is a quick and easy way to pull your database from Heroku to your local machine.
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
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.
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:
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.