Export data from PosgreSQL db in batch with pg gem

and-import.rb

require 'pg'

conn = PG::Connection.open ENV['DATABASE_URL']

Dir.glob("tmp/exports/*.copy").sort.each do |f|
  puts "#{Time.now} - batch #{f} ..."
  rs = conn.copy_data "COPY some_table FROM STDOUT" do
    File.open(f).each_line do |ln|
      conn.put_copy_data ln
    end
  end
  puts "#{rs.cmd_tuples} records copied."
end

export-in-batch.rb

require 'pg'

deco = PG::TextDecoder::CopyRow.new
conn = PG::Connection.open ENV['DATABASE_URL']

max_id = 0

idx = 0

loop do
  puts "#{Time.now} - batch #{idx} ..."
  last_row = nil
  File.open("tmp/exports/%06d.copy" % idx, "w") do |f|
    conn.copy_data "COPY (SELECT * FROM some_table WHERE id > #{max_id} ORDER BY id LIMIT 1000) TO STDOUT" do
      while row = conn.get_copy_data
        f.puts row
        last_row = row
      end
    end
  end

  unless last_row
    puts "No more"
    exit(0)
  end

  max_id = deco.decode(last_row)[0].to_i # change `0` to the index of ID column
  idx += 1
end