Skip to content

Instantly share code, notes, and snippets.

@zflat zflat/
Last active Nov 29, 2019

What would you like to do?
Migrate pg dump from Heroku to local sqlite3 development (and beyond)
# Export the Heroku PG database to a local dump file
heroku pgbackups:capture
curl -o latest.dump `heroku pgbackups:url`
# Install postregs & Setup password
# List databases
sudo -u postgres psql -l
# Create database for data
sudo -u postgres psql template1
template1=# CREATE DATABASE bikedb
# or
sudo -u postgres createdb bikedb
# Run restore script with options
sudo -u postgres pg_restore --verbose --clean --no-acl --no-owner -h localhost -d bikedb latest.dump
# Dump the data (only, not structure) from postgres to normal SQL
sudo -u postgres pg_dump --inserts -a -b bikedb > data.sql
# Clear all data from the development database
bundle exec rake db:drop
bundle exec rake db:schema:load
# Read data into development database
sqlite3 development.sqlite3
> .read data.sql
# Additional import-export
# Use taps gem

This comment has been minimized.

Copy link

darylf commented Apr 23, 2017

I'm not sure if something has changed since you wrote this, but I got the following error from the Heroku command (on Ubuntu)

▸ pgbackups:capture is not a heroku command.
▸ Perhaps you meant pg:backups:capture?


This comment has been minimized.

Copy link

CT83 commented Sep 4, 2018

Is this still working in 2018?


This comment has been minimized.

Copy link

jeffreycastro commented Nov 29, 2019

Created a gist based from this one and other sources:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.