CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecordassociations
noman tayyab
noman tayyab

Reputation: 365

Rails - Migrating data to another DB with different IDs

I have a scenario in which I need to dump/transfer data of one user from my Rails App to another database of same configuration and tables. For instance, The application is built as

  class Company < ActiveRecord::Base
    has_many :depots
    has_many :users
  end

  class Depot < ActiveRecord::Base
    belongs_to :company
    has_many :products
  end

  class User < ActiveRecord::Base
    belongs_to :company
  end

  class Product < ActiveRecord::Base
    belongs_to :depot
  end

My requirement is, if companyA stops paying, I want to dump their data into another DB (databse2 for instance) to keep my actual DB clean and once they come back and start paying, I want this data back.

Second requirement is, database2 can already have some data in it. So I need to retain all the records and I want to change the IDs of companyA (as there can already be a company with the same ID) while saving in to database2 keeping associations intact. It might seem silly to do this, but that is my requirement. I am using Postgres as my application DB. Any helps???

Upvotes: 0

Views: 68

Answers (1)

tadman
tadman

Reputation: 211560

You have a few options here worth investigating:

  1. Output everything in a singular JSON file that encodes everything the client had in the database, complete with ID fields.
  2. Dump out a series of CSV files that can be imported on the destination server.
  3. Dump out a single .sql file that will properly restore the data simply by running it.

The first option is the most elegant, but probably requires the most work. It gives you the ability to archive your data in a neat, tidy file that's easily parsed.

The second option might be fine or could be severely ugly depending on the sorts of data you have. If there's any binary data involved that's probably not going to work, but for clean, text-only columns and tabular data it's usually fairly efficient. The advantage here is you can selectively load in parts of your data without having to commit to parsing all of it.

The third option isn't easily parsed, you need to restore it to be able to use it, but it does make insertion really, really simple. You will only have to write an archiver, no specific restoration tool is required.

Whatever approach you take you'll need to be absolutely certain that ID numbers are never, ever reissued. Do not reset your sequence generators to fill in holes, and when moving databases port these over as well and test that they're set correctly. The last thing you need is ID conflicts.

If you're really worried about ID conflicts you might want to switch to non-numeric IDs, like use a UUID for everything where conflicts are basically irrelevant, though this does not come without a cost.

Upvotes: 1

Related Questions