从Oracle过渡到PostgreSQL: 理解模式的概念

前言

随着企业越来越多地转向开源技术，许多Oracle数据库专业人员发现他们需要使用最流行的开源关系数据库管理系统(RDBMS)之一PostgreSQL。尽管Oracle和PostgreSQL共享许多相似的概念，但在处理某些数据库结构的方式上存在根本的差异，其中之一就是模式。

在Oracle中，术语“模式”与用户的概念紧密地交织在一起，但在PostgreSQL中，模式扮演的角色有些不同。对于习惯于用户和模式之间一对一关系的Oracle用户来说，这可能是一个困惑的来源。在本文中，我们将深入探讨Oracle和PostgreSQL之间模式使用的差异，解释它们的结构和使用方式，以及如何在数据库管理实践中有效地利用PostgreSQL模式。

简介:什么是schema(模式)?

在我们探讨Oracle和PostgreSQL之间的区别之前，让我们简单地定义一下模式是什么。在数据库系统中，模式通常指的是数据库对象的集合，例如表、视图、索引和过程。模式为这些对象提供了一个名称空间，确保每个对象在该空间中是唯一可识别的。

然而，模式的概念可以根据所使用的RDBMS而有所不同。在Oracle和PostgreSQL中，模式都提供了一种组织对象的机制，但是它们与用户、访问控制和对象管理交互的方式有很大的不同。

让我们先回顾一下Oracle中的模式是如何工作的，然后看看PostgreSQL如何处理不同的模式。

Oracle的模式方法:以用户为中心的模型

模式是用户的同义词

在Oracle中，模式本质上相当于一个用户帐户。当在Oracle中创建一个用户时，会自动为该用户创建一个模式。模式包含该用户拥有的所有对象(表、视图、索引等)。因此，在Oracle数据库中，用户和模式之间存在一对一的关系。

这种结构意味着每个用户在数据库中都有自己的工作空间，并且他们创建的任何对象都存储在他们自己的模式中。这种用户和模式的紧密耦合简化了访问控制，因为只有拥有模式的用户才能访问和修改该模式中的对象，除非向其他用户授予显式权限。

例如，如果一个名为SCOTT的用户拥有一个名为EMPLOYEES的表，那么该表在Oracle中的完全限定名将是SCOTT.EMPLOYEES。这种命名约定清楚地标识了用户和他们拥有的对象。

用户模式分离:一个关键的限制

Oracle中的用户模式关系存在一些限制，特别是在多个用户需要使用同一组数据库对象的环境中。由于每个用户都有自己的模式，因此在用户之间共享对象可能会变得很麻烦。例如，如果多个用户需要访问SCOTT。employee表中，每个用户必须被授予显式权限，或者使用同义词来引用对象，而不包括查询中的模式。

随着用户和共享对象数量的增加，这种限制可能导致更复杂的访问控制机制和对象管理。此外，Oracle中的模式设计和数据库组织倾向于围绕用户帐户，这使得它在更大的协作环境中不太灵活，在这种环境中，对象可能不属于特定用户，而是属于部门、团队或应用程序。

PostgreSQL的模式方法:一个灵活的命名空间模型

作为名称空间的模式

在PostgreSQL中，模式作为数据库中的逻辑名称空间，它们不直接绑定到任何特定的用户。这个区别很重要:在Oracle中，每个用户拥有一个模式，而PostgreSQL允许在同一个数据库中创建多个模式，这些模式可以包含不同用户拥有的对象。

PostgreSQL中这种灵活的模式方法提供了一种更可扩展和模块化的组织级别。PostgreSQL中的模式可以被认为是将相关对象组合在一起的文件夹或容器。这使得在同一数据库中按功能、部门或应用程序组织对象变得更加容易。

例如，考虑一个用于人力资源应用程序的PostgreSQL数据库。您可能有一个名为hr的模式，其中包含员工、部门和薪水等表。employees表的完全限定名是hr.employees。多个用户可以访问该模式，其中的对象可以由不同的用户拥有。

解耦用户和模式

与Oracle在用户和模式之间的严格耦合不同，PostgreSQL允许更大的灵活性。一个用户可以跨多个模式拥有对象，一个模式可以包含不同用户拥有的对象。这种解耦提供了对数据库组织和访问的更细粒度的控制，这在具有许多用户和应用程序的环境中尤其有益。

例如，在PostgreSQL中，您可以为组织中的每个部门(例如，人力资源，财务，销售)创建一个模式，并且在每个模式中，您可以拥有由不同用户拥有的对象。这提供了一个更清晰的结构，并允许用户在同一个数据库中工作，而不需要频繁地跨越模式边界或依赖于模式特定的权限。

PostgreSQL还允许你控制搜索路径，当一个对象被引用时，它决定了搜索模式的顺序。通过配置搜索路径，您可以简化查询，允许用户访问对象，而不必总是指定模式名称。

Oracle和PostgreSQL模式的主要区别

现在我们已经在Oracle和PostgreSQL中建立了模式的基本概念，让我们总结一下每个系统如何处理模式的关键差异:

1.模式和用户之间的关系

Oracle

模式直接与用户帐户相关联，并且用户与其模式之间存在一对一的关系。模式本质上表示数据库中用户的工作空间。

PostgreSQL

模式独立于用户。多个用户可以拥有同一模式中的对象，单个用户可以拥有跨多个模式的对象。这允许采用更灵活和模块化的方法来组织数据库。

2.模式创建

Oracle

模式是在创建用户帐户时自动创建的。你不会显式地在Oracle中创建一个模式;相反，您创建一个用户，然后为该用户创建模式。

PostgreSQL

模式是通过CREATE SCHEMA命令显式创建的。您可以在单个数据库中创建多个模式，并且可以使用这些模式在逻辑上组织对象，而不必考虑拥有对象的用户。

3.对象组织

Oracle

对象被组织在与创建它们的用户相关联的模式中。每个用户的对象都存储在他们自己的模式中，这使得用户之间的对象共享变得更加复杂。

PostgreSQL

对象在模式中组织，这些模式不绑定到单个用户。多个用户可以共享相同的模式，从而更容易按功能或应用程序而不是按用户组织对象。

4.访问控制

Oracle

由于模式与用户绑定在一起，因此访问控制与用户权限密切相关。如果用户需要访问其他用户模式中的对象，则必须显式授予权限，或者必须使用同义词来简化对象访问。

PostgreSQL

访问控制更加灵活，因为多个用户可以拥有和共享同一模式中的对象。可以在模式级别授予权限，允许用户访问对象，而无需在每个查询中引用模式名称。

5.完全限定对象名称

Oracle

对象使用schema_name.object_name格式引用。由于每个模式对应于一个用户，这意味着对象通常被引用为user_name.object_name。

PostgreSQL

对象使用schema_name.object_name格式引用。但是由于模式独立于用户，模式名反映的是数据库的逻辑组织，而不是拥有该对象的用户。

6.大环境当中的模式管理

Oracle

由于用户和模式之间的一对一关系，在大型环境中管理模式可能会更加复杂。这种结构通常需要更细粒度的访问控制机制，并可能使对象共享更加麻烦。

PostgreSQL

模式和用户的解耦使得管理大型环境变得更加容易。模式可用于对相关对象进行分组，用户可以跨多个模式访问对象，而不需要复杂的访问控制机制。

实际考虑:从Oracle迁移到PostgreSQL

对于迁移到PostgreSQL的Oracle专业人员来说，最重要的调整可能是用户和模式的解耦。在Oracle中，以用户为中心的模式方法意味着数据库组织围绕单个用户帐户进行。然而，在PostgreSQL中，模式成为一种更灵活的组织工具，独立于用户。

下面是一些从Oracle模式模型过渡到PostgreSQL的实用技巧:

1.重新思考数据库组织

在Oracle中，您可能习惯于为每个应用程序或部门创建一个新用户，并依赖于用户和模式之间的一对一关系来组织对象。在PostgreSQL中，您应该考虑基于功能、应用程序模块或组织部门而不是单个用户创建模式。

例如，不是为人力资源部门创建一个单独的用户，而是创建一个人力资源模式，并在该模式中组织所有相关对象(例如，表、视图、函数)。然后可以根据需要授予多个用户对该模式的访问权限。

2.利用搜索路径

PostgreSQL允许您配置搜索路径，这决定了系统如何解析不完全限定的对象名称(即当您没有指定模式时)。通过适当地设置搜索路径，您可以简化查询并避免不断指定模式名称的需要。

例如，如果您的大部分工作都是在hr模式中完成的，那么您可以将搜索路径设置为首先包含hr，这样您就可以引用像employees这样的表，而无需在每个查询中添加模式名称的前缀。

3.使用角色进行访问控制

在PostgreSQL中，角色(可以代表一个用户或一组用户)用于管理跨模式对对象的访问。通过有效地使用角色，您可以控制谁可以访问哪些模式，以及他们可以对这些模式中的对象执行哪些操作。

例如，您可以创建一个read_only角色，该角色具有从hr模式中的表中进行SELECT的权限，然后将该角色分配给需要对hr数据进行只读访问的用户。

4.模式迁移计划

如果你要将Oracle数据库迁移到PostgreSQL，你需要仔细规划如何将Oracle用户和模式映射到PostgreSQL模式。由于Oracle将模式直接与用户绑定，如果您遵循相同的结构，您可能会在PostgreSQL中使用许多模式

相反，考虑基于逻辑关系而不是用户所有权将相关对象整合到更少的模式中。这有助于简化数据库结构，并使其更易于长期管理。

总结: 拥抱PostgreSQL的灵活性

从Oracle过渡到PostgreSQL需要改变你对模式和数据库组织的看法。在Oracle中，模式与用户紧密耦合，创建了一个简单但有时受限的环境。另一方面，PostgreSQL将模式与用户解耦，为组织和管理数据库对象提供了更灵活和可扩展的系统。

通过了解Oracle和PostgreSQL中模式工作方式的差异，您可以对如何在PostgreSQL中构建数据库和管理访问控制做出更明智的决定。凭借其灵活的命名空间模型和强大的模式管理工具，PostgreSQL提供了一个健壮的平台，可以以促进协作和可伸缩性的方式组织复杂的数据库。

进一步阅读

• PostgreSQL is a viable alternative to Oracle – here is why…^[1]
• “But how much does a PostgreSQL license actually cost?” A Frequently Asked Question from Oracle users considering PostgreSQL^[2]

Videos

• Webinar: PostgreSQL 101 for Oracle DBAs^[3]
• Fireside Chat: Oracle to PostgreSQL Migration ^[4]

参考资料