postgresql Ruby on Rails 迁移 - 创建新的数据库架构

Ruby on Rails Migration - Create New Database Schema

提问by Chris Knight

I have a migration that runs an SQL script to create a new Postgres schema. When creating a new database in Postgres by default it creates a schema called 'public', which is the main schema we use. The migration to create the new database schema seems to be working fine, however the problem occurs after the migration has run, when rails tries to update the 'schema_info' table that it relies on it says that it does not exist, as if it is looking for it in the new database schema and not the default 'public' schema where the table actually is.

我有一个运行 SQL 脚本的迁移来创建一个新的 Postgres 模式。默认情况下，在 Postgres 中创建新数据库时，它会创建一个名为“public”的模式，这是我们使用的主要模式。创建新数据库架构的迁移似乎工作正常，但是在迁移运行后出现问题，当 rails 尝试更新它所依赖的“schema_info”表时，它说它不存在，就好像它是在新的数据库模式中寻找它，而不是在表实际所在的默认“公共”模式中。

Does anybody know how I can tell rails to look at the 'public' schema for this table?

有谁知道我如何告诉 Rails 查看该表的“公共”模式？

Example of SQL being executed: ~

正在执行的 SQL 示例：~

CREATE SCHEMA new_schema;
COMMENT ON SCHEMA new_schema IS 'this is the new Postgres database schema to sit along side the "public" schema';
-- various tables, triggers and functions created in new_schema

Error being thrown: ~

抛出错误：~

RuntimeError: ERROR C42P01  Mrelation "schema_info" does not exist
L221    RRangeVarGetRelid: UPDATE schema_info SET version = ??

Thanks for your help

谢谢你的帮助

Chris Knight

克里斯奈特

回答by Jean

Well that depends what your migration looks like, what your database.yml looks like and what exactly you are trying to attempt. Anyway more information is needed change the names if you have to and post an example database.yml and the migration. does the migration change the search_path for the adapter for example ?

嗯，这取决于您的迁移是什么样的，您的 database.yml 是什么样的，以及您尝试尝试的具体内容。无论如何，如果必须并发布示例 database.yml 和迁移，则需要更多信息更改名称。例如，迁移是否会更改适配器的 search_path？

But know that in general rails and postgresql schemas don't work well together (yet?).

但是要知道，一般情况下，rails 和 postgresql 模式不能很好地协同工作（还有吗？）。

There are a few places which have problems. Try and build and app that uses only one pg database with 2 non-default schemas one for dev and one for test and tell me about it. (from thefollowing I can already tell you that you will get burned)

有几个地方有问题。尝试构建和应用程序，它只使用一个 pg 数据库和 2 个非默认模式，一个用于开发，一个用于测试，并告诉我它。（从下面我已经可以告诉你，你会被烧死）

Maybe it was fixed since the last time I played with it but when I see http://rails.lighthouseapp.com/projects/8994/tickets/390-postgres-adapter-quotes-table-name-breaks-when-non-default-schema-is-usedor this http://rails.lighthouseapp.com/projects/8994/tickets/918-postgresql-tables-not-generating-correct-schema-listor this in postgresql_adapter.rb

也许自从我上次玩它以来它已经修复但是当我看到http://rails.lighthouseapp.com/projects/8994/tickets/390-postgres-adapter-quotes-table-name-breaks-when-non- default-schema-is-used或此http://rails.lighthouseapp.com/projects/8994/tickets/918-postgresql-tables-not-generating-correct-schema-list或 postgresql_adapter.rb 中的此

  # Drops a PostgreSQL database
  #
  # Example:
  #   drop_database 'matt_development'
  def drop_database(name) #:nodoc:
    execute "DROP DATABASE IF EXISTS #{name}"
  end

(yes this is wrong if you use the same database with different schemas for both dev and test, this would drop both databases each time you run the unit tests !)

（是的，如果您对开发和测试使用具有不同架构的相同数据库，这是错误的，每次运行单元测试时这都会删除两个数据库！）

I actually started writing patches. the first one was for the indexes methods in the adapter which didn't care about the search_path ending up with duplicated indexes in some conditions, then I started getting hurt by the rest and ended up abandonning the idea of using schemas: I wanted to get myapp done and I didn't have the extra time needed to fix the problems I had using schemas.

我实际上开始编写补丁。第一个是适配器中的索引方法，它不关心 search_path 在某些情况下以重复的索引结束，然后我开始受到其余部分的伤害并最终放弃使用模式的想法：我想得到我的应用程序完成了，我没有额外的时间来解决我在使用模式时遇到的问题。

回答by Dan Harper

I'm not sure I understand what you're asking exactly, but, rake will be expecting to update the version of the Rails schema into the schema_info table. Check your database.yml config file, this is where rake will be looking to find the table to update.

我不确定我是否完全理解您的要求，但是，rake 将期望将 Rails 模式的版本更新到 schema_info 表中。检查您的 database.yml 配置文件，这是 rake 寻找要更新的表的地方。

Is it a possibility that you are migrating to a new Postgres schema and rake is still pointing to the old one? I'm not sure then that a standard Rails migration is what you need. It might be best to create your own rake task instead.

您是否有可能迁移到新的 Postgres 模式而 rake 仍然指向旧的模式？我不确定标准的 Rails 迁移是否是您所需要的。最好创建您自己的 rake 任务。

Edit: If you're referencing two different databases or Postgres schemas, Rails doesn't support this in standard migrations. Rails assumes one database, so migrations from one database to another is usually not possible. When you run "rake db:migrate" it actually looks at the RAILS_ENV environment variable to find the correct entry in database.yml. If rake starts the migration looking at the "development" environment and database config from database.yml, it will expect to update to this environment at the end of the migration.

编辑：如果您引用两个不同的数据库或 Postgres 模式，Rails 在标准迁移中不支持这一点。Rails 假设一个数据库，因此从一个数据库迁移到另一个数据库通常是不可能的。当您运行“rake db:migrate”时，它实际上会查看 RAILS_ENV 环境变量以在 database.yml 中找到正确的条目。如果 rake 从 database.yml 中查看“开发”环境和数据库配置开始迁移，它将期望在迁移结束时更新到此环境。

So, you'll probably need to do this from outside the Rails stack as you can't reference two databases at the same time within Rails. There are attempts at plugins to allow this, but they're majorly hacky and don't work properly.

因此，您可能需要从 Rails 堆栈外部执行此操作，因为您无法在 Rails 中同时引用两个数据库。有一些插件尝试允许这样做，但它们主要是hacky并且不能正常工作。

回答by Sergey Potapov

You can use pg_power. It provides additional DSL for migration to create PostgreSQL schemas and not only.

您可以使用pg_power。它提供了额外的 DSL 用于迁移以创建 PostgreSQL 模式，而不仅仅是。