如何将.sql文件导入Heroku postgres数据库?

I have a backup sql file from another database that I want to import into Heroku's postgres database. How do you do that?

我有一个来自另一个数据库的备份sql文件，我想将它导入Heroku的postgres数据库。你是怎么做到的?

3 个解决方案

#1

This is how you do it:

你就是这样做的:

heroku pg:psql --app YOUR_APP_NAME_HERE < updates.sql

And if you want to restore your production into staging (assuming both are heroku postgres DBs):

如果您想要将您的产品恢复到阶段(假设两者都是heroku postgres DBs):

heroku pgbackups:restore YOUR_STAGING_DATABASE_NAME `heroku pgbackups:url --app YOUR_PRODUCTION_APP_NAME` --app YOUR_STAGING_APP_NAME --confirm YOUR_STAGING_APP_NAME

Make sure to preserve the special single quotes around "heroku pgbackups:url --app YOUR_PRODUCTION_APP_NAME".

确保在“heroku pgbackup:url——app YOUR_PRODUCTION_APP_NAME”周围保留特殊的单引号。

-------- HEROKU TOOLBELT UPDATE --------

----- HEROKU工具带更新------

Heroku has recently updated their toolbelt so the old commands are no longer valid (see this link for more info). Below is the new version of the restore command.

Heroku最近更新了他们的工具带，所以旧的命令不再有效(更多信息请参见这个链接)。下面是恢复命令的新版本。

heroku pg:backups restore `heroku pg:backups public-url -a YOUR_PRODUCTION_APP_NAME` YOUR_STAGING_DATABASE_NAME --app YOUR_STAGING_APP_NAME --confirm YOUR_STAGING_APP_NAME

#2

Making backup file:

备份文件:

pg_dump -U USERNAME DATABASE --no-owner --no-acl -f backup.sql

Restoring from sql file to heroku :

从sql文件恢复到heroku:

heroku pg:psql --app APPNAME < backup.sql

(Bonus) Deleting all tables from heroku app database (example):

(附注)从heroku app数据库中删除所有表(示例):

heroku pg:reset --app APPNAME HEROKU_POSTGRESQL_ROSE

get DATABASE_URL from posgresql heroku panel (psql line)

从posgresql heroku面板获取DATABASE_URL

#3

Load the SQL into a local Postgres instance and make sure it's correct. Then dump the data using the directions here: https://devcenter.heroku.com/articles/heroku-postgres-import-export

将SQL加载到本地Postgres实例，并确保它是正确的。然后使用这里的说明转储数据:https://devcenter.heroku.com/articles/heroku postgres-import-export

Finally, upload the dump to a public web server (like S3) and restore to Heroku like this:

最后，将转储文件上载到公共web服务器(如S3)并将其还原到Heroku，如下所示:

heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump'

#1