PostgreSQL 架构

PostgreSQL 架构

在本节中，我们将学习PostgreSQL Schema，公共 Schema，PostgreSQL 如何使用 Schema 搜索路径来解析 Schema 中的对象，PostgreSQL 模式和权限，以及使用 PostgreSQL Schema 的优势。

PostgreSQL 模式介绍

每个数据库都以一个模式开始，它应该是公共模式。而一个模式是表的命名集合。

PostgreSQL 架构

Schema 是一个命名空间，它提供了几个对象，例如

Views
Indexes
Sequences
Datatypes
Operators
Functions
Tables
Other relations.

PostgreSQL 架构

注意：PostgreSQL Schema 是一个包含命名对象的命名空间。

在PostgreSQL 模式中，我们可以使用模式名称作为前缀来修复名称，以便在以下语句的帮助下访问模式的对象：

schema_name.object_name

否则，我们可以设置一个包含schema的搜索路径，我们也可以在以后覆盖schema搜索路径。

一个数据库可以有一个或多个模式，而每个模式只存在一个数据库，两个模式可以包含不同的对象，它们共享一个相似的名称。

例如

假设我们有包含Employee 表的**Company 模式，并且公共模式也有Employee 表**。当我们引用Employee表时，应该是这样的：

public.comany  
Or  
Employee.company

为什么我们需要在 PostgreSQL 中使用 Schemas？

在PostgreSQL 中，我们使用模式的主要原因如下：

  • 最终用户只能使用那些允许将测试表和逻辑与生产表分离的模式。
  • 在schema中，我们可以高效地实现数据，可以应用于以下一些活动：
    • 我们可以快速恢复存在于不同模式中的数据。因此，面向应用程序的模式可以单独返回和备份以进行恢复和时间旅行。
    • 当应用程序数据在模式中时，我们可以管理应用程序更改。因此，新版本的应用程序可以处理新模式中的表结构，包括对模式名称的简单修改。
    • 我们可以轻松地备份存在于单独模式中的数据。因此，不稳定的数据可以有多个来自非易失性数据的备份计划。

因此，出于创建目的，我们可以在模式高级管理中将我们的应用程序数据分开。出于最终用户的目的，我们可以通过相互踩踏来将我们的用户保持在不同的模式中。

公共架构

我们在 PostgreSQL 教程的早期主题中创建了没有描述任何模式名称的表。默认情况下，这些表和其他对象会自动放置到公共模式中。

对于每个新数据库，PostgreSQL 都会生成一个称为public的模式。因此，以下命令是并行的：

CREATE TABLE table_name;  
And  
CREATE TABLE public.table_name;

PostgreSQL 模式搜索路径

  • 在 PostgreSQL 中，如果我们只通过名称选择一个表，我们可以使用模式搜索路径的帮助来搜索该表。
  • PostgreSQL 将为模式搜索路径中的初始等效表提供特权。如果没有匹配项，则会抛出错误，甚至该名称出现在数据库中的替代模式中。
  • 在 PostgreSQL 中，当前模式被称为搜索路径中的主模式。
  • 我们可以经常在模式搜索路径中引用没有模式名称的表。

例如

要访问 Employee 表，我们使用company.Employee。

笔记：

  • 如果我们在不描述模式名称的情况下创建新对象，PostgreSQL 还将使用新对象的当前模式。
  • 的CURRENT_SCHEMA（）方法用来返回当前架构。
SELECT current_schema();

输出

执行上述命令后，我们将得到以下输出：

PostgreSQL 架构

注意：PostgreSQL 为我们在数据库中创建的每个新对象使用公共。

我们可以使用下面的 SHOW 语句来查看当前的搜索路径。

SHOW search_path;

输出

执行上述命令后，我们将得到以下输出：

PostgreSQL 架构

在上面的输出中：

  • 在“$用户”定义了PostgreSQL会使用找对象具有类似名称的当前用户的第一个架构。 例如 如果我们使用Postgres用户登录并且拥有Employee表的权限。在Postgres schema 中，PostgreSQL 将搜索Employee表。如果找不到任何类似的对象，它将继续在公共模式中搜索对象。
  • 正如我们之前所了解的，第二个组件是指定公共模式。

要创建新模式，我们可以使用CREATE SCHEMA命令：

CREATE SCHEMA Company;

输出

PostgreSQL 架构

为了将新模式添加到搜索路径，我们可以使用以下语句：

SET search_path TO Company, public;

输出

PostgreSQL 架构

如果我们在没有描述架构名称的情况下创建一个新表Employee，那么 PostgreSQL 会将这个Employee 表放入Company 架构中：

CREATE TABLE Employee(  
Emp_id SERIAL PRIMARY KEY,  
first_name VARCHAR(30) NOT NULL,  
last_name VARCHAR(30) NOT NULL,  
Age integer NOT NULL,  
email VARCHAR(100) NOT NULL UNIQUE,  
address CHARACTER(50),   
salary REAL  
);

输出

PostgreSQL 架构

下面的屏幕截图显示了新架构Company和属于Company 架构的Employee表：

PostgreSQL 架构

为了访问Employee表中的公司架构，我们可以使用下面的命令之一：

SELECT * FROM Employee;  
Or   
SELECT * FROM Company.Employee;

在公共模式是在搜索路径的第二部分; 因此，要访问公共模式中的Employee 表，我们应该在下面指定表名：

SELECT * FROM public.Employee;

一旦我们执行了上述所有语句，我们将得到以下输出。

运行第一个命令后，我们将得到以下输出：

PostgreSQL 架构

执行第二条命令后，我们将得到以下输出：

PostgreSQL 架构

执行最后一个命令后，我们会得到以下输出：

PostgreSQL 架构

执行完所有三个命令后，我们将得到相同的输出，这就是为什么我们可以使用上述任何查询来访问Company 架构中的员工表。

注意：我们也可以删除公共架构，因为它不是唯一的架构。

PostgreSQL 模式和权限

用户只能有权限访问他们创建的模式中的对象，这意味着用户不能使用非他们创建的模式中的任何其他对象。

因此，我们必须授予用户使用权限，以获取他们未创建的架构中对象的所有访问权限。

以下命令用于获取架构上用户的使用权限：

GRANT USAGE ON SCHEMA schema_name TO user_name;

为了允许用户在他们没有创建的模式中创建一个对象，我们必须授予他们CREATE 权限。

以下命令用于授予架构的创建权限：

GRANT CREATE ON SCHEMA schema_name TO user_name;

注意：默认情况下，每个用户都对公共架构具有 USAGE 和 CREATE 权限。

如果我们不想使用以下命令的帮助访问该权限，我们可以撤销该权限。

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

这里，第一个public是指架构，第二个public是指每个用户。

PostgreSQL 模式操作

  • 我们可以使用CREATE SCHEMA命令来创建新模式。
  • 我们可以使用DROP SCHEMA命令来删除模式。
  • 我们可以使用ALTER SCHEMA命令来重命名架构或修改所有者。

使用 PostgreSQL Schema 的好处

模式允许我们简化许多用户使用一个数据库而不涉及彼此。PostgreSQL 模式的一些普遍优势如下：

  • 在 PostgreSQL Schema 中，关键的优势在于，如果数据库属于不同的模式，它可以具有相同的表名。
  • 模式可以将数据库对象分类为逻辑组，使它们更易于控制。
  • 模式允许用户使用一个数据库作为多个数据库的替代。
  • 为了避免与其他对象的名称发生冲突，我们可以将第三方模式放入不同的模式中。
  • 名称相似的对象只要放在不同的模式中就不会冲突。