Using heroku pg to restore a backup to a macOS laptop#

Postgres.app#

Postgres.app is the easiest way I know to run a PostgreSQL server on macOS: just install using the installer, then click the “Initialize” button in the UI.

pg#

Assuming you have the Heroku CLI installed, the heroku pg:pull command can pull a Heroku database backup and load it into a local database.

https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull tells you that you need the following:

1
heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

The app name is your app name (whatever comes before .herokuapp.com).

I found my all-caps environment variable by running the following:

1
heroku run env -a simonwillisonblog | grep POSTGRESQL
2
Running env on simonwillisonblog... up, run.2963 (Hobby)
3
HEROKU_POSTGRESQL_JADE_URL=postgres://....

Then I ran this command:

1
heroku pg:pull HEROKU_POSTGRESQL_JADE_URL simonwillisonblog -a simonwillisonblog

This created a local PostgreSQL database called simonwillisonblog and imported my latest backup.

When I ran it a second time I had to use dropdb simonwillisonblog first to drop the existing local database.

createdb: command not found#

I ran this flow on a new computer (with a fresh install of Postgres.app) and got the following error:

1
% heroku pg:pull HEROKU_POSTGRESQL_JADE_URL simonwillisonblog -a simonwillisonblog
2
heroku-cli: Pulling postgresql-animate-15868 ---> simonwillisonblog
3
/bin/sh: createdb: command not found

I fixed this by temporarily adding /Applications/Postgres.app/Contents/Versions/15/bin to my path, like this:

1
PATH=$PATH:/Applications/Postgres.app/Contents/Versions/15/bin \
2
  heroku pg:pull HEROKU_POSTGRESQL_JADE_URL simonwillisonblog -a simonwillisonblog