Problems with postgresql COPY command with Rails on different server

Question

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.

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

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.

Am I missing something?

Answer 1

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)

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...

Answer 2

In pg-0.17.1 (Rails 4) there's an improved PG::Connection::copy_data interface to Postgres COPY.

  def load_file(filename)

    dbconn = ActiveRecord::Base.connection_pool.checkout
    raw  = dbconn.raw_connection
    count = nil

    result = raw.copy_data "COPY my_table FROM STDIN" do

      File.open(filename, 'r').each do |line|
        raw.put_copy_data line
      end

    end

    count = dbconn.select_value("select count(*) from #{ttable}").to_i

    ActiveRecord::Base.connection_pool.checkin(dbconn)

    count
  end

You can even pass an entire file buffer to put_copy_data if you aren't worried about memory usage:

      result = raw.copy_data "COPY my_table FROM STDIN" do
        raw.put_copy_data File.read(filename)
      end

Answer 3

You can also try this, and execute the command with psql:

config = YourApp::Application.config.database_configuration[::Rails.env]
dbhost, dbuser, dbname = config['host'], config['username'], config['database']

copy_command = "\\copy theTable (col1, col2, col3) from '/a/path/to/csv' csv header;"
sql_command = "psql -U #{dbuser} -h #{dbhost} #{dbname} -c \"#{copy_command}\""

`#{sql_command}`