ruby-on-rails-3 - Postgres : Restructuring to Schemas

Question

我有一个基于 Rails 3.2 Multi-Tenancy 子域的应用程序，我正在尝试将其迁移到 PostgreSQL 的模式(每个帐户都有自己的模式——现在所有帐户都使用相同的表)。

所以，我认为我需要:

1. 创建一个新的数据库
2. 为每个帐户(它的 id)和它们下面的表创建一个模式
3. 抓取属于每个账户的所有数据，并将其插入到该账户模式下的新数据库中

这听起来正确吗？如果是这样，这样做的好方法是什么？我是否应该编写一个使用 ActiveRecord 的 ruby​​ 脚本，提取数据，然后将其插入(效率很低，但应该完成工作)到新数据库中？或者 Postgres 是否提供了执行此类操作的好工具？

编辑:

按照 Craig 的建议，我在现有数据库中创建了模式。然后我循环遍历 Rake 任务中的所有帐户，使用类似以下内容复制数据:

Account.all.each do |account|

  PgTools.set_search_path account.id, false
  sql = %{INSERT INTO tags SELECT DISTINCT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."tagger_id" = #{admin.id} AND "taggings"."tagger_type" = 'User'}
  ActiveRecord::Base.connection.execute sql

  #more such commands

end

Answer 1

我会亲自使用 SQL 进行转换。

在与当前数据库相同的数据库中创建新模式以便于迁移，因为您无法使用 PostgreSQL 轻松地跨数据库查询。

使用适当的INSERT INTO ... SELECT 查询迁移数据。要在不必禁用任何外键的情况下执行此操作，您应该构建数据的依赖关系图。首先将数据复制到不依赖于任何内容的表中，然后复制到依赖于它们的表中，依此类推。

您需要为每个客户模式重复此操作，因此请考虑创建一个使用 EXECUTE ... 动态 SQL 的 PL/PgSQL 函数来:

• 为客户创建模式
• 在模式中创建表

• 通过循环硬编码的表名数组以正确的顺序复制数据，执行以下操作:

  EXECUTE `'INSERT INTO '||quote_ident(newschema)||'.'||quote_ident(tablename)||' SELECT * FROM oldschema.'||quote_ident(tablename)||' WHERE customer_id = '||quote_literal(customer_id)'||;'
  

  其中 newschema、tablename 和 customer_id 是 PL/PgSQL 变量。

然后您可以从 SQL 调用该函数。虽然您可以只select convert_customer(c.id) FROM customer GROUP BY c.id，但我可能会从外部控制脚本中执行此操作，这样每个客户的工作都可以单独完成和提交，避免如果倒数第二个客户转换失败，则需要从头开始。

对于额外的疯狂积分，甚至可以在主客户架构的表上定义触发器，将对已迁移客户的更改复制到新架构中的数据副本，这样他们就可以在迁移期间继续使用系统。我会避免这种情况，除非迁移太大而不能在没有停机的情况下进行，因为这将是测试的噩梦，而且您仍然需要触发器在客户 ID x 而 x 的数据迁移实际上正在进行中，因此不会完全透明。

如果您为不同的客户使用不同的登录用户(强烈推荐)，您的函数还可以:

• REVOKE 对 public
模式的权限
• GRANT 将架构的权限限制给将要使用它的用户或角色
• REVOKE 创建的每个表的公共(public)权限
• GRANT 向用户和角色授予每个表所需的有限权限
• GRANT 对这些表使用的任何序列。即使序列是由 SERIAL 伪列创建的，这也是必需的。

这样一来，您的所有权限都是一致的，您以后无需去更改它们。请记住，您的网络应用程序应该永远以 super 用户身份登录。