使用PostgreSQL的架构和Rails创建多租户应用程序 [英] Creating a multi-tenant application using PostgreSQL's schemas and Rails

问题描述

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

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.

我已经回答了一些问题:

I already have a few concerns answered:

1. 如何使subdomain-fu也能与域一起使用? 这里有人问了同样的问题，导致您此博客.
2. 什么数据库以及如何构建?这是一个出色的对话，以及很好的答案.

1. 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.
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.

这三点涵盖了我需要了解的许多常规知识.但是，在接下来的步骤中，我似乎有很多实现事情的方法.我希望有一种更好，更轻松的方法.

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.

新用户注册后，我可以轻松创建架构.但是，什么是加载其余模式已经拥有的结构的最佳和最简便的方法?以下是一些可能使您有更好主意的问题/方案.

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. 我应该将其传递给 shell脚本，该脚本将公共模式转储到临时模式中，然后将其导入到我的主数据库中(与Guy Naor在视频中所说的很像)?这是我从freenode上有用的#postgres上获得的快速摘要/脚本.尽管这可能行得通，但我将不得不在Rails之外做很多事情，这使我有些不舒服..这也使我想到了下一个问题.
2. 有没有办法直接从Ruby on Rails做到这一点?就像创建PostgreSQL模式一样，然后只需将Rails数据库模式(schema.rb-我知道，这很令人困惑)加载到该PostgreSQL模式中.
3. 是否已经有一个包含这些东西的gem/插件?这样的方法，例如"create_pg_schema_and_load_rails_schema(the_new_schema_name)".如果没有，我可能会做一个，但是我怀疑它在所有运动部件上的测试效果如何(特别是如果我最终使用shell脚本来创建和管理新的PostgreSQL模式).

1. 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.
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).

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

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

推荐答案

2011年12月5日更新

多亏了布拉德·罗伯逊(Brad Robertson)和他的团队，这里有公寓宝石.这非常有用，并且可以完成很多繁重的工作.

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

但是，如果您要修改模式，我强烈建议您了解它的实际工作方式.熟悉 Jerod Santo的演练，这样您就会知道Apartment宝石或多或少在做什么.

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.

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

有人创建了博客发布，并且在整个过程中都能很好地行走.

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

更新2010年5月11日格林尼治标准时间+8

自昨晚以来，我已经能够使用一种方法来创建一个新的架构并将schema.rb加载到其中.不知道我在做什么是否正确(到目前为止，似乎工作正常)，但至少离它还差一步.如果有更好的方法，请告诉我.

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.

  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

这篇关于使用PostgreSQL的架构和Rails创建多租户应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！