Export and import database from heroku

Export
heroku pg:backups capture
curl -o latest.dump `heroku pg:backups public-url`

Import
heroku pg:backups capture
heroku pg:backups restore 'http://example.herokuapp.com/latest.dump' DATABASE_URL

Export specific Table Heroku
pg_dump --no-acl --no-owner -h ec2-ip.compute-.amazonaws.com -U mdvxormgnpnmne -t events --data-only d3irvpu7la5kcd > events1.dump

Import specific Table Heroku
psql -U postgres -d event-kit_development -f events1.dump

Export Db: 
pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Import Db:
psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}



Dump only contents of db:
backup: 
pg_dump -Fc -v -f full.dump -U postgres dbname
restore: 

pg_restore -a -U postgres -d event-kit_development -Fc full.dump

pg_dumpall -g -U postgres > globals.sql

Dump schema of database:

pg_dump -Fp -s -v -f db-schema.sql -U postgres dbname

Dump contents of database:

pg_dump -Fc -v -f full.dump -U postgres dbname

Now restore.

psql -f globals.sql
psql -f db-schema.sql dbname
pg_restore -a -d dbname -Fc full.dump

Import and Export Spreadsheet and CSV file

1. rails g scaffold product title:string description:text

first you have to put gem in gemfile

gem 'roo-xls'
gem 'axlsx_rails'
gem 'zip-zip'

run bundle

In application.rb
   require 'csv'

2. In prodouct model copy and paste code

# Import spread and csv file
 def self.import(file)
 spreadsheet = open_spreadsheet(file)
   header = spreadsheet.row(1)
   (2..spreadsheet.last_row).each do |i|
     row = Hash[[header, spreadsheet.row(i)].transpose]
     product = find_by_id(row["id"]) || new
     product.attributes = row.to_hash.slice(*row.to_hash.keys)
     product.save!
   end
end

 def self.open_spreadsheet(file)
     # xlsx = Roo::Excelx.new(file.path)
    case File.extname(file.original_filename)
    when ".csv" then Csv.new(file.path, nil, :ignore)
    when ".xls" then Roo::Excel.new(file.path)
    when ".xlsx" then Roo::Excelx.new(file.path)
    else raise "Unknown file type: #{file.original_filename}"
   end
 end

# Download or export CSV and Spreadsheet
def self.to_csv(options = {})
 CSV.generate(options) do |csv|
   csv << column_names
   all.each do |product|
     csv << product.attributes.values_at(*column_names)
   end
 end
end

3. In product Controller

def index
    @products = Product.all
    respond_to do |format|
      format.html
      #Download or export csv and spreadsheet
      format.csv { send_data @products.to_csv }
      format.xls { send_data @products.to_csv(col_sep: "\t") }
    end
  end


  def import
    Product.import(params[:file])
    redirect_to root_url, notice: "Products imported."
  end

4. in route.rb

  resources :products do
    collection { post :import }
  end

5. In views products/index.html.erb

<p>
  Download:
  <%= link_to "CSV", products_path(format: "csv") %> |
  <%= link_to "Excel", products_path(format: "xls") %>
</p>

<h2>Import Products</h2>
<%= form_tag import_products_path, multipart: true do %>
  <%= file_field_tag :file %>
  <%= submit_tag "Import" %>
<% end %>

Important Note: When you will download spreadsheet and csv file you have to remove some column id,created_at, updated_at from both file.if you will not remove that column in you file will get error "id not null column created_at, updated_at". You make sure remove these three column(id, created_at, updated_at) in both file