Rails: Overhauling a live heroku postgres database

First of all if your application is live put application on maintenance mode.

So there will be no new database update.

Now take db backup from your original application and save somewhere on your local machine. Anytime you need data you have proper data with you.

In my view you should create a new heroku instance and upload original db their will be best option. Because you can test every functionality there.

You can do same thing on your local environment also but sometime local changes not work properly in live mode.

Now make new db changes in new heroku instance, test your application.

If you are satisfied with changes you made, copy newly created db to original application.

Turn off maintenance mode and you are done.

As it seems you don't need to automate it I would:

• create a backup of the production db just in case
• prepare the database locally, with all definitive data (you can use whatever method of getting the correct records into it including your csv way - there are many ways, I usually tend to prefer to build json models and then use that to seed)
• update/create the new app
• copy the local db into production using heroku's CLI Heroku's PG-Push/Pull

Your new app schema will need to conform of course and this is one of the reasons why PG-Push is good, because if you're working on a new app or one that strayed way too much from the production "branch" in terms of migrations and such, you can just push your local branch to deploy and then push the underlying Database in local to production as well, keeping them synched. Nonetheless you'll need to have the "local" version of the Db tidy and ready.

Addendum To seed records from partially existing records I would do something like (keep in mind you should do this as methods of something, somewhere, that you can then run in the console):

new_hash_of_objects = { }
Model_you_want.all.each do |model|
  new_hash_of_objects[model.id.to_sym] = {
    new_attribute_1 = '...',
    new_attribute_2 = '...',
    old_attribute_1 = model.old_attribute_1,
    ...
  }
end

File.open("somewhere/json_records/records.json","w") do |f|
  f.write("#{new_hash_of_objects.to_json}")
end

Then you can have something to load them back:

def read_json_copy_of_months
    @json = JSON.parse(File.read("somewhere/json_records/records.json")).as_json.with_indifferent_access
    @json.each do |m,record|
      Record.create!(record)
      end
    end
  end

If you have millions of rows this might get heavy, so you need to break it up into smaller files.