CSV-Export DB from Ruby / PostgreSQL

gistfile1.rb

# inspired by: https://github.com/oxon/logistaegvm/blob/master/lib/tasks/export.rake
# required gems:
# * pg
# * rubyzip

unless ARGV.count == 1
  puts 'Required argument: Output directory'
  exit 1
end

require 'pg'
require 'zip'

conn = PG.connect( host: 'localhost', dbname: '<dbname>' )  # TODO replace <dbname> - preferrably from ARGV
directory = "#{ARGV[0]}/<subdir>" # TODO replace <subdir>
FileUtils.mkdir_p(directory)
output_file = "#{directory}/exported_#{Time.now.strftime('%Y-%m-%dT%H:%M:%S')}.zip"

tables_query = "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'"
tables = conn.exec(tables_query).map{|table_row| table_row['table_name']}

FileUtils.rm(output_file) if File.exists?(output_file)

Zip::File.open(output_file, Zip::File::CREATE) do |zipfile|
  tables.each do |table|
    puts "exporting #{table}"
    filename = "#{directory}/#{table}.csv"
    FileUtils.rm(filename) if File.exists?(filename)
    conn.exec("copy #{table} to '#{filename}' delimiter ',' csv header")
    zipfile.add(filename.split('/').last, filename)
  end
  puts 'zipping output'
end
`rm #{directory}/*.csv`