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

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:

我已经回答了一些问题:

How can you get subdomain-fu to work with domains as well? Here's someone that asked the same question which leads you to this blog.
如何让子域-fu也与域一起工作?有人问了同样的问题，把你引向了这个博客。
What database, and how will it be structured? Here's an excellent talk by Guy Naor, and good question about PostgreSQL and schemas.
什么数据库，它将如何构造?这是Guy Naor的精彩演讲，还有关于PostgreSQL和模式的好问题。
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.
我已经知道我的模式都有相同的结构。他们持有的数据会有所不同。那么，如何为所有模式运行迁移呢?这是一个答案。

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.

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

Should I pass it on to a shell script that 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.
我是否应该将它传递给一个shell脚本，该脚本将公共模式转储到一个临时脚本中，并将它导入到我的主数据库中(非常类似Guy Naor在他的视频中所说的)?这是我从freenode上有用的#postgres得到的一个快速摘要/脚本。虽然这可能行得通，但我将不得不做很多Rails之外的事情，这让我有点不舒服。这也引出了下一个问题。
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.
有没有一种直接从Ruby on Rails实现的方法?与创建PostgreSQL模式一样，只需加载Rails数据库模式(schema)。rb——我知道，这让人困惑)到PostgreSQL模式中。
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).
有没有一个gem/plugin已经有这些东西了?方法,如“create_pg_schema_and_load_rails_schema(the_new_schema_name)”。如果没有的话，我可能会做一个，但是我怀疑它对所有移动部分的测试有多好(特别是如果我最终使用shell脚本创建和管理新的PostgreSQL模式)。

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

谢谢，我希望不会太久!

3 个解决方案

#1

Update Dec 5, 2011

更新12月5日,2011年

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

多亏了布拉德·罗伯逊和他的团队，才有了这套公寓。它非常有用，可以做很多繁重的工作。

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 post and 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.

从昨晚开始，我已经能够获得一个方法来创建一个新的模式并加载模式。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

#2

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在加载模式时试图查找现有的表名。rb，由于您已经将search_path设置为只包含新模式，所以它失败了。当然，这是假设您的数据库中仍然有公共模式。

Hope that helps.

希望有帮助。

#3

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

有一个gem/插件已经有这些东西了吗?

pg_power provides 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.

它还需要注意正确地将模式转储到模式中。rb文件。

#1