使用 PostgreSQL 的模式和 Rails 创建多租户应用程序

Creating a multi-tenant application using PostgreSQL's schemas and Rails

提问by Ramon Tayag

Stuff I've already figured out

我已经想通的东西

I'm learning how to create a multi-tenant application in Rails that serves data from different schemas based on what domain or subdomain is used to view the application.

我正在学习如何在 Rails 中创建一个多租户应用程序，该应用程序根据用于查看应用程序的域或子域来提供来自不同模式的数据。

I already have a few concerns answered:

我已经回答了一些问题：

1. How can you get subdomain-fu to work with domains as well? Here's someone that asked the same questionwhich leads you to this blog.
2. What database, and how will it be structured?Here's an excellent talk by Guy Naor, and good question about PostgreSQL and schemas.
3. I already know my schemas will all have the same structure. They will differ in the data they hold. So, how can you run migrations for all schemas?Here's an answer.

1. 如何让 subdomain-fu 也与域一起使用？有人问了同样的问题，这将您带到了这个博客。
2. 什么数据库，它将如何构建？这是Guy Naor的精彩演讲，以及关于 PostgreSQL 和模式的好问题。
3. 我已经知道我的模式都将具有相同的结构。他们持有的数据会有所不同。那么，如何为所有模式运行迁移？这是一个答案。

Those three points cover a lot of the general stuff I need to know. However, in the next steps I seem to have many ways of implementing things. I'm hoping that there's a better, easier way.

这三点涵盖了很多我需要知道的一般性内容。但是，在接下来的步骤中，我似乎有很多实现方法。我希望有更好、更简单的方法。

Finally, to my question

最后，对于我的问题

When a new user signs up, I can easily create the schema. However, what would be the best and easiest way to load the structure that the rest of the schemas already have?Here are some questions/scenarios that might give you a better idea.

当新用户注册时，我可以轻松创建架构。但是，加载其余模式已经具有的结构的最佳和最简单的方法是什么？这里有一些问题/场景可能会给你一个更好的主意。

1. Should I pass it on to a shell scriptthat dumps the public schema into a temporary one, and imports it back to my main database (pretty much like what Guy Naor says in his video)? Here's a quick summary/script I got from the helpful #postgres on freenode. While this will probably work, I'm gonna have to do a lot of stuff outside of Rails, which makes me a bit uncomfortable.. which also brings me to the next question.
2. Is there a way to do this straight from Ruby on Rails? Like create a PostgreSQL schema, then just load the Rails database schema (schema.rb - I know, it's confusing) into that PostgreSQL schema.
3. Is there a gem/plugin that has these things already?Methods like "create_pg_schema_and_load_rails_schema(the_new_schema_name)". If there's none, I'll probably work at making one, but I'm doubtful about how well tested it'll be with all the moving parts (especially if I end up using a shell script to create and manage new PostgreSQL schemas).

1. 我是否应该将它传递给一个shell 脚本，该脚本将公共模式转储到一个临时模式中，然后将其导入到我的主数据库中（非常像 Guy Naor 在他的视频中所说的）？这是我从 freenode 上有用的 #postgres 获得的快速摘要/脚本。虽然这可能会奏效，但我将不得不在 Rails 之外做很多事情，这让我有点不舒服......这也让我想到了下一个问题。
2. 有没有办法直接从 Ruby on Rails 做到这一点？就像创建一个 PostgreSQL 模式，然后只需将 Rails 数据库模式（schema.rb - 我知道，这很令人困惑）加载到该 PostgreSQL 模式中。
3. 有没有已经拥有这些东西的宝石/插件？像“create_pg_schema_and_load_rails_schema(the_new_schema_name)”这样的方法。如果没有，我可能会努力制作一个，但我怀疑它对所有移动部件的测试情况（特别是如果我最终使用 shell 脚本来创建和管理新的 PostgreSQL 模式）。

Thanks, and I hope that wasn't too long!

谢谢，我希望那不会太长！

采纳答案by Ramon Tayag

Update Dec 5, 2011

2011 年 12 月 5 日更新

Thanks to Brad Robertson and his team, there's the Apartment gem. It's very useful and does a lot of the heavy lifting.

多亏了 Brad Robertson 和他的团队，才有了公寓宝石。它非常有用，可以完成很多繁重的工作。

However, if you'll be tinkering with schemas, I strongly suggest knowing how it actually works. Familiarize yourself with Jerod Santo's walkthrough, so you'll know what the Apartment gem is more or less doing.

但是，如果您要修改模式，我强烈建议您了解它的实际工作原理。熟悉Jerod Santo 的演练，这样您就会或多或少地了解公寓宝石的作用。

Update Aug 20, 2011 11:23 GMT+8

更新 2011 年 8 月 20 日 11:23 GMT+8

Someone created a blog postand walks though this whole process pretty well.

有人创建了一篇博文，并很好地完成了整个过程。

Update May 11, 2010 11:26 GMT+8

更新 2010 年 5 月 11 日 11:26 GMT+8

Since last night I've been able to get a method to work that creates a new schema and loads schema.rb into it. Not sure if what I'm doing is correct (seems to work fine, so far) but it's a step closer at least. If there's a better way please let me know.

从昨晚开始，我已经能够找到一种方法来创建新模式并将 schema.rb 加载到其中。不确定我所做的是否正确（到目前为止似乎工作正常）但至少更近了一步。如果有更好的方法请告诉我。

  module SchemaUtils
   def self.add_schema_to_path(schema)
    conn = ActiveRecord::Base.connection
    conn.execute "SET search_path TO #{schema}, #{conn.schema_search_path}"
   end

   def self.reset_search_path
    conn = ActiveRecord::Base.connection
    conn.execute "SET search_path TO #{conn.schema_search_path}"
   end

   def self.create_and_migrate_schema(schema_name)
    conn = ActiveRecord::Base.connection

    schemas = conn.select_values("select * from pg_namespace where nspname != 'information_schema' AND nspname NOT LIKE 'pg%'")

    if schemas.include?(schema_name)
     tables = conn.tables
     Rails.logger.info "#{schema_name} exists already with these tables #{tables.inspect}"
    else
     Rails.logger.info "About to create #{schema_name}"
     conn.execute "create schema #{schema_name}"
    end

    # Save the old search path so we can set it back at the end of this method
    old_search_path = conn.schema_search_path

    # Tried to set the search path like in the methods above (from Guy Naor)
    # [METHOD 1]: conn.execute "SET search_path TO #{schema_name}"
    # But the connection itself seems to remember the old search path.
    # When Rails executes a schema it first asks if the table it will load in already exists and if :force => true. 
    # If both true, it will drop the table and then load it. 
    # The problem is that in the METHOD 1 way of setting things, ActiveRecord::Base.connection.schema_search_path still returns $user,public.
    # That means that when Rails tries to load the schema, and asks if the tables exist, it searches for these tables in the public schema.
    # See line 655 in Rails 2.3.5 activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
    # That's why I kept running into this error of the table existing when it didn't (in the newly created schema).
    # If used this way [METHOD 2], it works. ActiveRecord::Base.connection.schema_search_path returns the string we pass it.
    conn.schema_search_path = schema_name

    # Directly from databases.rake. 
    # In Rails 2.3.5 databases.rake can be found in railties/lib/tasks/databases.rake
    file = "#{Rails.root}/db/schema.rb"
    if File.exists?(file)
     Rails.logger.info "About to load the schema #{file}"
     load(file)
    else
     abort %{#{file} doesn't exist yet. It's possible that you just ran a migration!}
    end

    Rails.logger.info "About to set search path back to #{old_search_path}."
    conn.schema_search_path = old_search_path
   end
  end

回答by Brendan Hay

Change line 38 to:

将第 38 行更改为：

conn.schema_search_path = "#{schema_name}, #{old_search_path}"

I presume that postgres is trying to lookup existing table names when loading schema.rb and since you've set the search_path to only contain the new schema, it fails. This of course, is presuming you still have the public schema in your database.

我认为 postgres 在加载 schema.rb 时试图查找现有表名，并且由于您已将 search_path 设置为仅包含新架构，因此它失败了。这当然是假设您的数据库中仍然有公共模式。

Hope that helps.

希望有帮助。

回答by Sergey Potapov

Is there a gem/plugin that has these things already?

有没有已经拥有这些东西的宝石/插件？

pg_powerprovides this functionality to create/drop PostgreSQL schemas in migration, like this:

pg_power提供此功能以在迁移中创建/删除 PostgreSQL 模式，如下所示：

def change
  # Create schema
  create_schema 'demography'

  # Create new table in specific schema
  create_table "countries", :schema => "demography" do |t|
    # columns goes here
  end

  # Drop schema
  drop_schema 'politics'
end

Also it takes care about correctly dumping schemas into schema.rb file.

它还负责将模式正确转储到 schema.rb 文件中。