What is the proper way of merging multiple postgres schemas into a single schema preserving the foreign key relationships?

Question

We have a SAAS platform written in Rails using the postgres' schema based multitenancy and Apartment gem. The different schemas are identical, with same number of tables and same columns in each table. We want to migrate to foreign key based multitenant system where we want to merge all the records from different schemas into a single schema, identifying each record with a tenant_id. What is the proper way of merging all the records from the different schemas, and preserving the foreign key relationships.

Answer 1

This is a situation that will need care. I think (I could be wrong) that the best approach is to add to all tables tenant_id and original_id ... before attempting migration populate original_id in all tables with id of that record. Essentially this is to have a record of what the value of id was before the merging.

After merge you can then run a rake task that rebuilds the associations. So if you had...

class Foo
has_many :bars

Your migration script would do (after migration)

Bar.all.each do |bar|
  foo = Foo.find_by(tenant_id: bar.tenant_id, original_id: bar.foo_id)
  bar.update_column(:foo_id, foo.id)
end

You'd need to do something similar for every relation, so it's a bit of a slog.

Hopefully, someone else will come up with a better solution.

NOTE THIS IS NOT IDEMPOTENT. If it errors, you can't restart it except by redoing the merging completely.