postgresql 如何将大型数据库从 Heroku 导入本地 mysql 或 sqlite3？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7148663/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import a big database from Heroku to local mysql or sqlite3?
提问by aledalgrande
As per title I need to import, but PG backups is giving me strict Postgres SQL that doesn't work with MySQL, also with a non-specified encoding that I guess is UTF-16. Using db:pull takes ages and errors before finishing. I'd appreciate any suggestion. Thanks.
根据标题，我需要导入，但 PG 备份为我提供了不适用于 MySQL 的严格 Postgres SQL，还有一个我猜是 UTF-16 的非指定编码。在完成之前使用 db:pull 需要很长时间和错误。我很感激任何建议。谢谢。
回答by mu is too short
Set up PostgreSQL locally, use PG backupsto copy the data from Heroku to your local machine, then pg_restoreto import it into your new local PostgreSQL. Then you can copy it from PostgreSQL to MySQL or SQLite locally without having to worry about timeouts. Or, since you'd have a functional PostgreSQL installation after that, just start developing on top of PostgreSQL so that your development stack better matches your deployment stack; developing and deploying on the same database is a good idea.
在本地设置 PostgreSQL，使用PG 备份将数据从 Heroku 复制到本地机器，然后pg_restore将其导入新的本地 PostgreSQL。然后就可以在本地将它从 PostgreSQL 复制到 MySQL 或 SQLite，而不必担心超时。或者，因为之后您将拥有一个功能性的 PostgreSQL 安装，只需在 PostgreSQL 之上开始开发，以便您的开发堆栈更好地匹配您的部署堆栈；在同一个数据库上开发和部署是一个好主意。
You're probably getting binary dumps (i.e. pg_dump -Fc) from Heroku, that would explain why the dump looks like some sort of UTF-16 nonsense.
您可能会pg_dump -Fc从 Heroku获得二进制转储（即），这可以解释为什么转储看起来像某种 UTF-16 废话。
You can use the pgbackups addonto export the database dump
您可以使用 pgbackups插件导出数据库转储
$ heroku addons:add pgbackups # To install the addon
$ curl -o latest.dump `heroku pgbackups:url` # To download a dump
回答by michael
Heroku has instructions on how to do this: https://devcenter.heroku.com/articles/heroku-postgres-import-export#restore-to-local-database, which boil down to:
Heroku 有关于如何执行此操作的说明：https: //devcenter.heroku.com/articles/heroku-postgres-import-export#restore-to-local-database，归结为：
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
where myuseris the current user and mydbis the current database.
其中myuser是当前用户，mydb是当前数据库。
If you're using Postgres.app, it's pretty trivial to copy your production database locally. You can leave out -U myuserunless you have configured it otherwise, and create a database by running $ psql -h localhostand then CREATE DATABASE your_database_name;(from the Postgres.app documentation. Then run the above command and you're set.
如果您使用Postgres.app，在本地复制您的生产数据库非常简单。-U myuser除非您以其他方式配置，否则您可以省略，并通过运行$ psql -h localhost然后创建数据库CREATE DATABASE your_database_name;（来自Postgres.app 文档。然后运行上面的命令并设置。
回答by Lior Elrom
Follow these 4 simple steps in your terminal
(Heroku Dev Center):
在您的终端
（Heroku 开发中心）中执行以下 4 个简单步骤：
Install the Heroku Backup tool:
$ heroku addons:add pgbackupsStart using it:
$ heroku pgbackups:captureDownload the remote db on Heroku (to your local machine) using curl:
$ curl -o latest.dump 'heroku pg:backups public-url'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.yml file.
- DATABASE_NAME can be your development/test/production db (Ex. mydb_development)
安装 Heroku 备份工具：
$ heroku addons:add pgbackups开始使用它：
$ heroku pgbackups:capture使用 curl 下载 Heroku 上的远程数据库（到您的本地机器）：
$ curl -o latest.dump 'heroku pg:backups public-url'加载它*：
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME -d DATABASE_NAME latest.dump- 获取您的用户名并从您的 config/database.yml 文件中选择所需的数据库。
- DATABASE_NAME 可以是您的开发/测试/生产数据库（例如 mydb_development）
That's it!
UPDATE:It is updated to the new interface
而已！
UPDATE：更新到新界面
