CODEWITHSUNDEEP
databasepostgresqlheroku
Luigi
Luigi

Reputation: 5603

Postgres copy Heroku Production DB to local development DB

I have a heroku database, d76mj7ltuqs.

I then have a local database, test_development.

The schema is the same on both of these databases - I want to pull all of the data from my production database and overwrite my local database, so that local is an exact replica of production at the time of pull.

How can I do that in Postgres?

Upvotes: 45

Views: 21052

Answers (6)

JezC
JezC

Reputation: 1868

Use heroku's "pg:pull":

You'll need to clear your local DB:

rake db:drop

Then collect some information from Heroku:

heroku pg:pull DATABASE_URL test_development

This will connect to the heroku DB, and copy it to the local database.

See Heroku's documentation on pg:pull for more details.

Upvotes: 82

Ishan Patel
Ishan Patel

Reputation: 6091

This command should do the work:

heroku pg:pull DATABASE_URL database-name --app heroku-app-name

Upvotes: 11

Montells
Montells

Reputation: 6679

clean your local database:

rake db:schema:load

dump your heroku database:

heroku pg:backups:capture -r <**your production git repo name**>
heroku pg:backups:download -r <**your production git repo name**>

load data in your local database

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d <**test database name**> latest.dump

Upvotes: 7

rick
rick

Reputation: 1646

If this is a Rails app, you can use the following script to overwrite your local database with the latest dump you've generated on Heroku. If you uncomment the line with heroku pg:backups capture, the script will generate a new snapshot on Heroku before downloading it to your machine.

Note that you shouldn't have to edit the script, since it reads all the configuration from your database.yml file.

#!/usr/bin/env ruby

require_relative '../config/environment'

# Uncomment the line below if you want to generate a new snapshot of the
# Heroku production database before downloading it to the local machine
# `heroku pg:backups capture`

database_dump_file_pathname = Tempfile.new('latest.dump').path
`heroku pg:backups:download --output #{database_dump_file_pathname}`

# Get database config fom database.yml file
database_config = YAML::load_file(Rails.root.join('config', 'database.yml'))
database_name = database_config['development']['database']
database_username = database_config['development']['username']
database_password = database_config['development']['password']

# Overwrite local database with dump
cmd_line_arguments = [
  '--verbose',
  '--clean',
  '--no-acl',
  '--no-owner',
  '--host localhost',
  "-U #{database_username}",
  "-d #{database_name}",
  database_dump_file_pathname
].join(' ')
`PGPASSWORD=#{database_password} pg_restore #{cmd_line_arguments}`

See the Heroku docs on downloading DB backups for details.

Upvotes: 0

blotto
blotto

Reputation: 3407

this is how i do it, be sure to gzip it as your database grows. also don't export the ACL as you likely don't have the same postgres user on heroku and local accounts. replace with your specific details.

 pg_dump -h ec2-##-##-##-##.compute-1.amazonaws.com -p <port> -Fc --no-acl --no-owner -o -U <username> <databasename> | gzip > dumpfile.gz
 #<Prompt for Password>
 gunzip -c dumpfile.gz | pg_restore --verbose --clean --no-acl --no-owner -d test_development -U <local_username>

Upvotes: 3

Alex
Alex

Reputation: 8539

Use your terminal to make a local pg_dump and then either psql or pg_restore it into your local database.

Similar method can be found here.

Upvotes: 1

Related Questions