Pushing a single table to Heroku

Question

I am aware of the heroku pg:push command which pushes an entire database up to Heroku.

Now that I am launching my product, I would like to be able to push up only a specific table that contains information collected locally without overwriting existing tables (such as users).

Is there a command that enables me to only push specific tables to heroku?

Answer 1

I wrote script which extracts DB url from heroku. Then it dumps single tables from production and restores them on development/localhost. Run it like this:

rake production_to_development:run\['users;news;third_table',my-sushi-app\]

Code:

namespace :production_to_development do
  task :run, [:tables, :app] => [:environment] do |t, args|
    tables = args["tables"].split(';')
    database_url = nil
    Bundler.with_clean_env { database_url = `heroku config:get DATABASE_URL --app=#{args["app"]}` }

    require 'addressable/uri'
    uri = Addressable::URI.parse(database_url)
    remote_database = uri.path[1,uri.path.length-2] # there is \n at the end of the path!

    tables.each do |table|
      backup_file = "tmp/#{table}.backup"
      #bin_dir = "/Applications/Postgres.app/Contents/Versions/latest/bin"
      bin_dir = ""

      dump_command = "PGPASSWORD=#{uri.password} #{bin_dir}/pg_dump --file \"#{backup_file}\" --host \"#{uri.host}\" --port \"#{uri.port}\" --username \"#{uri.user}\" --no-password --verbose --format=c --blobs --table \"public.#{table}\" \"#{remote_database}\""
      `#{dump_command}`
      `psql -U 'root' -d my_table -c 'drop table if exists #{table}'`
      `pg_restore -d my_table --no-owner  #{backup_file}`
    end

  end
end

Answer 2

If I understand correctly, you just need a single database table with its locally created data pushed to your Rails production app. Maybe this is a simplistic approach, but you could create a migration for your table and then populate using db/seeds.rb.

After you've populated the seeds.rb file and pushed your repo to heroku:

heroku run rake db:migrate
heroku run rake db:seed

Also, if your local table has a ton of data and you're using Rails 4, check out the seed dump gem: https://github.com/rroblak/seed_dump. This will take your existing db data and map it to the seed format.

Answer 3

My suggestion is to use PostgreSQL dump/restore capabilities directly using the pg_dump and psql commands.

With pg_dump you can dump a specific table from your local database

$ pg_dump --data-only --table=products sourcedb > products.sql

Then grab the Heroku PostgreSQL connection string from the configs

$ heroku config | grep HEROKU_POSTGRESQL

# example
# postgres://user3123:[e-mail]:6212/db982398

and restore the table in the remote database, using the information retrieved from Heroku.

$ psql -h ec2-117-21-174-214.compute-1.amazonaws.com -p 6212 -U user3123 db982398 < products.sql

You will need to customize the -p, -h and -U parameters, as well as the database name. The password will be prompted by psql.

You can also use the pg_restore to filter a dump and restore the table, but I personally prefer psql.

Note that Heroku is recommending the use of PostgreSQL tools in several documentations, such as Importing and Exporting for large data, or whenever the provided CLI commands don't cover specific cases like the one in this question.