在不同服务器上使用Rails的Postgresql COPY命令的问题 [英] Problems with postgresql COPY command with Rails on different server

问题描述

我有一个已成功运行几个月的Rails应用程序。在一些地方，我通过ActiveRecord :: Base.connection.execute（sql_code）

I have a rails app that has been working successfully for months. In a few places I call directly to the database via ActiveRecord::Base.connection.execute( sql_code )

由于最近需要扩展，我刚刚添加了第二台服务器用于数据处理。我想运行相同的应用程序，但要通过网络连接到其他数据库服务器。那是唯一的区别。该应用程序的所有其他区域都可以工作-它可以连接到远程数据库。

With a recent need to scale, I just added a second server for data processing. I want to run the same app but connect over the network to the other database server. That is the only difference here. All other areas of the app work--it can connect to the remote database.

在哪里中断了，在这里我有rails发出psql COPY命令来导入CSV文件。

Where it is breaking, is where I have rails issue a psql COPY command to import a csv file.

   result = ActiveRecord::Base.connection.execute( @PGSQL_COPY_COMMAND )     # perform the copy command

此操作失败，并说找不到csv文件。我已经验证了它的存在，并且运行rails应用程序的用户和postgres用户都可以读取。

This fails and says that the csv file can not be found. I have verified it is there and is readable to both the user running the rails app and the postgres user.

我错过了什么吗？

推荐答案

您可以使用COPY FROM STDIN来解决此问题……

You can use COPY FROM STDIN to get around this... like so:

conn = ActiveRecord::Base.connection_pool.checkout
raw  = conn.raw_connection
raw.exec("COPY tablename (col1, col2, col3) FROM STDIN")
# open up your CSV file looping through line by line and getting the line into a format suitable for pg's COPY...
raw.put_copy_data line
# once all done...
raw.put_copy_end
while res = raw.get_result do; end # very important to do this after a copy
ActiveRecord::Base.connection_pool.checkin(conn)

$ b $后，执行此操作非常重要b

我相信COPY有一些选项，可以让您指定要传入的CSV数据，这将使它变得更加容易...

I believe there are some options to COPY that will let you specify you're passing in CSV data which would make it even easier...

这篇关于在不同服务器上使用Rails的Postgresql COPY命令的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！