如何将Heroku PG转储导入本地计算机 [英] How to import a Heroku PG dump into local machine

问题描述

我正在尝试将生产的Heroku数据库导入到开发计算机中。

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

我的本​​地数据库是PostgreSQL。

My local db is PostgreSQL.

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

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

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

然后，我尝试删除具有 rake db：drop 的本地数据库，然后使用 rake db：create 再次创建空数据库。

Then, I try to drop the local db with rake db:drop and then I create the empty database again by using 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?

推荐答案

您会看到错误，因为在您实际上给他压缩转储（这就是heroku的用途）。

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

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

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

pg_restore -O -d app_development latest.dump

注意：

• 使用 -O ，因为您可能不知道不要使用远程heroku postgres数据库的随机用户名。


• Heroku 不建议使用水龙头，但我不知道它有多大风险。

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

这篇关于如何将Heroku PG转储导入本地计算机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！