postgresql Postgres 将 Heroku 生产数据库复制到本地开发数据库

Postgres copy Heroku Production DB to local development DB

提问by Luigi

I have a heroku database, d76mj7ltuqs.

我有一个 heroku 数据库，d76mj7ltuqs.

I then have a local database, test_development.

然后我有一个本地数据库，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?

我怎样才能在 Postgres 中做到这一点？

回答by JezC

Use heroku's "pg:pull":

使用 heroku 的“pg:pull”：

You'll need to clear your local DB:

您需要清除本地数据库：

rake db:drop

The collect some information from Heroku:

从 Heroku 收集一些信息：

heroku pg:pull DATABASE_URL test_development

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

这将连接到 heroku 数据库，并将其复制到本地数据库。

See Heroku's documentation on pg:pullfor more details.

有关更多详细信息，请参阅Heroku 关于 pg:pull 的文档。

回答by Montells

clean your local database:

清理本地数据库：

rake db:schema:load

dump your heroku database:

转储您的 heroku 数据库：

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

回答by Ishan Patel

This command should do the work:

这个命令应该做的工作：

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

回答by blotto

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.

这就是我的做法，请确保随着数据库的增长对其进行 gzip。也不要导出 ACL，因为您可能在 heroku 和本地帐户上没有相同的 postgres 用户。替换为您的具体详细信息。

 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>

回答by Alex

Use your terminal to make a local pg_dumpand then either psqlor pg_restoreit into your local database.

使用终端进行本地pg_dump，然后要么psql或者pg_restore到你的本地数据库。

Similar method can be found here.

类似的方法可以在这里找到。

回答by rick

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.

如果这是一个 Rails 应用程序，您可以使用以下脚本用您在 Heroku 上生成的最新转储覆盖您的本地数据库。如果您取消注释该行heroku pg:backups capture，该脚本将在 Heroku 上生成一个新快照，然后再将其下载到您的机器上。

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

请注意，您不必编辑脚本，因为它会从您的 database.yml 文件中读取所有配置。

#!/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 backupsfor details.

有关详细信息，请参阅有关下载数据库备份的Heroku 文档。