Skip to content

Instantly share code, notes, and snippets.

@hartleybrody
Last active April 10, 2024 16:09
Show Gist options
  • Save hartleybrody/44a5e54a477dbfa6c091de0e6ea50302 to your computer and use it in GitHub Desktop.
Save hartleybrody/44a5e54a477dbfa6c091de0e6ea50302 to your computer and use it in GitHub Desktop.
Copy data from Heroku Postgres into local database
# 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
@hartleybrody
Copy link
Author

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

@hartleybrody
Copy link
Author

This comes in handy, yet again!

@patcoll
Copy link

patcoll commented Jul 20, 2018

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

@JeffrySan
Copy link

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

@hartleybrody
Copy link
Author

@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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment