如何在heroku上执行.sql脚本?

I have a .sql file with a bunch of insert commands that I want to execute on my postgres database on heroku. But I don't know how to do it:-

我有一个.sql文件，其中包含一些插入命令，我想在heroku上的postgres数据库上执行这些命令。但是我不知道怎么做

If I had access to postgres console I'd type the following:

如果我能访问postgres控制台，我会输入以下内容:

psql -h localhost -d database -U username -f datafile.sql

but it seems that heroku doesn't support this command. I've tried with

但是heroku似乎不支持这个命令。我试过了,

heroku pg:psql

but that doesn't let me input a file.

但是我不能输入文件。

Are there any other options?

还有其他选择吗?

3 个解决方案

#1

For things like seeding a database, I recommend Richard Brown's answer: you're arguably better off using something like Rails seeds mechanism, or something scripted like a rake task.

对于像播种数据库这样的事情，我推荐Richard Brown的答案:您最好使用Rails种子机制，或者使用rake任务之类的脚本。

That said, being able to pipe sql (raw, or a file) is a useful feature, especially for idempotent things like simple look ups or routine queries. In which case you can execute your local sql with any of the following:

也就是说，能够管道sql(原始的，或者文件)是一个有用的特性，特别是对于像简单查找或常规查询之类的幂等特性。在这种情况下，您可以使用以下任何一种sql执行您的本地sql:

$ cat file.sql | heroku pg:psql --app app_name
$ echo "select * from table;" | heroku pg:psql --app app_name
$ heroku pg:psql --app app_name < file.sql

#2

Why not just use psql?

为什么不直接使用psql呢?

If you look at the output of heroku config you will see the database URLs (DATABASE_URL key) your application is using - if you take this and break them apart in to the correct bits for using with the psql all will be good.

如果您查看heroku配置的输出，您将看到您的应用程序正在使用的数据库url (DATABASE_URL键)——如果您将其拆分为正确的位，以便使用psql，那么所有这些都将是好的。

如

DATABASE_URL:  postgres://username:password@host:port/dbname

becomes

就变成了

psql -h host -p port -d dbname -U username -f datafile.sql

#3

I like updates that are testable and repeatable. When I need to update the database I write a rake task to perform the update; that way you can run it against test first to guarantee the output is correct before running in production.

我喜欢可测试和可重复的更新。当我需要更新数据库时，我编写一个rake任务来执行更新;通过这种方式，您可以首先针对测试运行它，以确保在生产环境中运行之前输出是正确的。

You don't mention if this is an initial database load or one run later, but the convention for loading fresh data into a Rails database is to create a db:seed rake file that you can execute after your db:migrate task is done.

您没有提到这是初始的数据库负载还是稍后的一次运行，但是将新数据加载到Rails数据库的惯例是创建一个db:seed rake文件，您可以在db:migrate任务完成后执行该文件。

See: http://justinfrench.com/notebook/a-custom-rake-task-to-reset-and-seed-your-database And: http://railscasts.com/episodes/179-seed-data

参见:http://justinfrench.com/notebook/a-custom-rake-task-to-reset-and-seed-your-database和http://justinfrench.com/notebook/a-custom-rake-task-to-reset-and-seed-your-database

#1