Postgres copy Heroku Production DB to local development DB

Question

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?

Maybe [`pg_dump`](http://www.postgresql.org/docs/current/static/app-pgdump.html) and [`pg_restore`](http://www.postgresql.org/docs/current/static/app-pgrestore.html) ? — Ihor Romanchenko, Apr 15 '14 at 15:50

score 82 · Answer 1 · edited Jul 23 '20 at 19:35

82

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.

edited Jul 23 '20 at 19:35

Carl Edwards

13,826
11
57
119

answered Aug 13 '14 at 11:23

JezC

1,868
17
19

3

Not sure if this is a new thing now, but when defining an app you need to put in `--app` before the app name. – jdpipkin Aug 12 '15 at 15:22
3

Same here. I need: `heroku pg:pull DATABASE_URL database_local_name --app myapp` – J0ANMM Jul 25 '17 at 14:56
3

First, I thought I need to replace DATABASE_URL with my heroku config corresponding value, but not. – V-SHY Aug 14 '18 at 07:20

score 11 · Answer 2 · answered Aug 09 '19 at 04:36

11

This command should do the work:

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

answered Aug 09 '19 at 04:36

Ishan Patel

5,571
12
47
68

Surprised this isn't getting more support. – LunaCodeGirl Feb 20 '20 at 00:44

score 7 · Answer 3 · answered Jul 26 '17 at 17:04

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

score 3 · Answer 4 · answered Apr 15 '14 at 17:35

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>

The `pg_dump` can compress the dump without external `gzip`. Look at the `--compress=0..9` option. — Ihor Romanchenko, Apr 17 '14 at 09:58

score 1 · Answer 5 · edited May 23 '17 at 12:34

1

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.

edited May 23 '17 at 12:34

Community

1
1

answered Apr 15 '14 at 16:28

Alex

8,321
1
34
30

score 0 · Answer 6 · answered Jun 20 '17 at 18:46

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.

Postgres copy Heroku Production DB to local development DB

6 Answers6

Linked