Copy data from Heroku Postgres into local database

import-db.sh

# copy/import data from heroku postgres to localhost pg database

# useful for copying admin's work on live site into local database to reproduce errors
# https://devcenter.heroku.com/articles/heroku-postgres-import-export

# take heroku pg snapshot and download
heroku pg:backups:capture
heroku pg:backups:download

# load the dump into local postgres database, assuming $DATABASE_URL set locally
pg_restore --verbose --clean --no-acl --no-owner -d $DATABASE_URL latest.dump

rm latest.dump

# =============================================================================== #
# =============================================================================== #
# =============================================================================== #

# copy/import from local database into heroku DB

# dump your local database into a sql file
pg_dump $DATABASE_URL > dump.sql

# import it into the heroku database
psql $(heroku config:get DATABASE_URL) < dump.sql


# remove the dump
rm dump.sql

I've already used this like 5 times in the last week as we've had to move data between different databases and apps.

"In PostgreSQL tools wherever you can specify a database name you can instead specify a connection string."

This comes in handy, yet again!

FYI, heroku config:get DATABASE_URL outputs the value without need for grep or sed

it's need to update, now you need to add -a <app_name> at the end of those heroku capture and heroku download command

@patcoll good tip, thanks!

@JeffrySan i believe if you're in the app's directory (inside the git repo) you don't need to specify the app with -a

---

the simplest version of this process is simply

pg_dump $OLD_DATABASE_URL > dump.sql
psql  $NEW_DATABASE_URL < dump.sql
rm dump.sql