postgresql 如何将 Heroku PG 转储导入本地机器

How to import a Heroku PG dump into local machine

提问by Martin

I'm trying to import my production Heroku database into my development machine.

我正在尝试将我的生产 Heroku 数据库导入我的开发机器。

My local db is PostgreSQL.

我的本地数据库是 PostgreSQL。

First, I'm exporting the dump from Heroku to my machine

首先，我将转储从 Heroku 导出到我的机器

curl -o latest.dump `heroku pgbackups:url`

Then, I try to drop the local db with rake db:dropand then I create the empty database again by using rake db:create.

然后，我尝试删除本地数据库，rake db:drop然后使用rake db:create.

The problem I'm getting is when actually trying to import the dump to the database

我遇到的问题是实际尝试将转储导入数据库时

psql -d app_development -U myusername -f mydumpfile.sql

I begin seeing errors like this

我开始看到这样的错误

psql:latest.dump:24: ERROR:  syntax error at or near "PGDMP"
LINE 1: PGDMP
        ^
psql:latest.dump:28: ERROR:  syntax error at or near ""
LINE 1:     INCREMENT BY 1
        ^
psql:latest.dump:36: ERROR:  syntax error at or near ""
LINE 1:     id integer NOT NULL,
        ^
psql:latest.dump:40: ERROR:  syntax error at or near ""
LINE 1:     INCREMENT BY 1
        ^
psql:latest.dump:45: ERROR:  syntax error at or near ""
LINE 1:     id integer NOT NULL,
        ^
psql:latest.dump:49: ERROR:  syntax error at or near ""
LINE 1:     INCREMENT BY 1

... 

psql:latest.dump:1601: invalid command \S4???(???A?|c?e0<00K?A?}F??????A(??~?t?I?????G(?    K???l??k"?H????S?,N*?[(@??a5J??j}
psql:latest.dump:1602: invalid command \??k???|??w???h?
psql:latest.dump:1603: invalid command \=??????o?h?
psql:latest.dump:1609: invalid command \????^.?????????E???/-???+??>#??E?.2)?&????    g????"7},_??]?:?f?Tr|o???)?p????h?KO?08[Rqu???|3?cW???ahbm??H?H8??$???2?a?-?
psql:latest.dump:1613: invalid command \D!qVS???L??*???R??I!???
psql:latest.dump:1614: invalid command \??-?}Q
psql:latest.dump:12565: ERROR:  invalid byte sequence for encoding "UTF8": 0xb0

Any idea what is happening this and how to solve it?

知道这是怎么回事以及如何解决吗？

回答by Maxime R.

You see errors because psql tries to interpret SQL queries when you're actually giving him a compressed dump(that's what heroku uses).

您会看到错误，因为 psql 在您实际向他提供压缩转储时尝试解释 SQL 查询（这是 heroku 使用的内容）。

While you can't read the dump, pg_restore -O latest.dumpgives you valid SQL you could pipe to psql but the easy solution is the following one :

虽然您无法读取转储，但pg_restore -O latest.dump为您提供了可以通过管道传输到 psql 的有效 SQL，但简单的解决方案如下：

pg_restore -O -d app_development latest.dump

Notes :

注意事项：

• Use -Obecause you probably don't use the random username of your remote heroku postgres db.
• Heroku doesn't recommend to use tapsbut I don't know how really risky it is.

• 使用-O是因为您可能不使用远程 heroku postgres db 的随机用户名。
• Heroku不建议使用水龙头，但我不知道它的风险有多大。

回答by Lior Elrom

Follow these 4 simple steps in your terminal
(Heroku Dev Center):

在您的终端
（Heroku 开发中心）中执行以下 4 个简单步骤：

1. Create a backup copy of your database:

   $ heroku pg:backups capture DATABASE_NAME
   

2. Download the copy from Heroku (to your local machine) using curl:

   $ curl -o latest.dump `heroku pg:backups public-url`
   

3. Load it*:

   $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME -d DATABASE_NAME latest.dump
   

   • get YOUR_USERNAME and choose the desired database from your config/database.ymlfile.
   • DATABASE_NAME can be your development/test/production db (Ex. mydb_development)

1. 创建数据库的备份副本：

   $ heroku pg:backups capture DATABASE_NAME
   

2. 使用 curl 从 Heroku 下载副本（到您的本地机器）：

   $ curl -o latest.dump `heroku pg:backups public-url`
   

3. 加载它*：

   $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME -d DATABASE_NAME latest.dump
   

   • 获取 YOUR_USERNAME 并从您的config/database.yml文件中选择所需的数据库。
   • DATABASE_NAME 可以是您的开发/测试/生产数据库（例如 mydb_development）

That's it!

而已！

回答by theIV

Use the Tapsgem. It allows you to run a simple heroku db:pullto populate a local database.

使用Taps宝石。它允许您运行一个简单的程序heroku db:pull来填充本地数据库。

回答by Patrick

I wanted to avoid having to set up Postgres on my local machine (blowing away and recreating the database is a pain if you're just looking for quick instructions). I put together some exact instructions for doing this with a local Postgres database running Docker. I'm adding a link here, since Google kept bringing me to this question (and it's a possible solution, though probably not what you're looking for): https://gist.github.com/locofocos/badd43131f14b3e40c760741d5a26471

我想避免在我的本地机器上设置 Postgres（如果你只是在寻找快速说明，那么吹嘘并重新创建数据库是一件痛苦的事）。我整理了一些使用运行 Docker 的本地 Postgres 数据库执行此操作的确切说明。我在这里添加一个链接，因为谷歌一直给我带来这个问题（这是一个可能的解决方案，虽然可能不是你正在寻找的）：https: //gist.github.com/locofocos/badd43131f14b3e40c760741d5a26471