To add your Heroku DB to PGAdmin, first get your credentials from Heroku.
You’ll need your:
- DB name
- user name
Add a new server using these credentials. In addition, you’ll want to change the server settings, or you’ll see about a thousand DB names in your list.
- make sure you are disconnected from the server,
- select the Server, right click -> properties, Advanced tab,
- in ‘DB restriction’ type in the name of your database(s) enclosed in single quotes and separated by spaces (i.e. ‘db_name’).
Now you’re server should be able to connect and you should just see your DB in the “Databases” section under your server.
I wanted to migrate from Heroku’s shared database to a MySQL database because:
- the shared DB has a limited level that you can scale to
- some of the Heroku add-on partners are a lot cheaper than Heroku’s DB offerings
- my development is being done with a MySQL DB, as opposed to Heroku’s PostGRESQL, and I did have one snafu where a query worked locally, but not on PostGRESQL
- it’s hard to see (and edit) data that’s in the Shared DB
- Heroku only allows read-access to the Shared DB, so it would’ve forced me to use their worker Dynos which are more expensive than something like Iron Worker workers for a lot of the tasks I wanted to do with workers