37. PostgreSQL 模式¶

生产数据库不可避免地会有大量的表和视图，在一个模式中管理所有这些表和视图很快就会变得非常麻烦。幸运的是，PostgreSQL 包含“_模式”的概念。

模式就像文件夹，可以包含表、视图、函数、序列和其他关系。每个数据库都从一个模式开始，即 public 模式。

在该模式内，PostGIS 的默认安装会创建 geometry_columns、 geography_columns 和 spatial_ref_sys 元数据关系，以及 PostGIS 使用的所有类型和函数。因此，PostGIS 的用户始终需要访问 public 模式。

在 public 模式中，您还可以看到我们在研讨会中到目前为止创建的所有表。

37.1. 为什么要使用模式？¶

使用模式有两个非常好的理由：

在模式中管理的数据更容易进行批量操作。
- 备份单独模式中的数据更容易：因此，易失性数据可以与非易失性数据采用不同的备份计划。
- 还原单独模式中的数据更容易：因此，可以单独还原和备份面向应用程序的模式，以便进行时间回溯和恢复。
- 当应用程序数据位于模式中时，更容易管理应用程序差异：因此，新版本的软件可以在新模式中使用表结构，并且切换只涉及对模式名称的简单更改。
可以限制用户在单个模式中工作，以隔离分析表和测试表与生产表。

因此，出于生产目的，将您的应用程序数据单独保存在模式中可以改进管理；而对于用户目的，将您的用户保留在单独的模式中可以防止他们相互干扰。

让我们创建一个新模式并将一个表移到其中。首先，在数据库中创建一个新模式

CREATE SCHEMA census;

接下来，我们将 nyc_census_blocks 表移动到 census 模式

ALTER TABLE nyc_census_blocks SET SCHEMA census;

如果您正在使用 psql 命令行程序，您会注意到 nyc_census_blocks 现在已从您的表列表中消失！如果您正在使用 PgAdmin，您可能需要刷新您的视图才能看到新模式及其内部的表。

您可以通过两种方式访问模式内的表

显式引用很容易，但是过一会儿就会觉得输入很累

SELECT * FROM census.nyc_census_blocks LIMIT 1;

操作 search_path 是一种很好的方法，可以在没有大量额外输入的情况下提供对多个模式中表的访问权限。

您可以使用 SET 命令在运行时设置 search_path

SET search_path = census, public;

这确保了对关系和函数的所有引用都在 census 和 public 模式中进行搜索。请记住，所有 PostGIS 函数和类型都在 public 中，因此我们不想将其从搜索路径中删除。

每次连接时都设置搜索路径也会让人感到疲惫，但幸运的是，可以为用户永久设置搜索路径

ALTER USER postgres SET search_path = census, public;

现在，postgres 用户将始终在其搜索路径中包含 census 模式。

用户喜欢创建表，PostGIS 用户尤其如此：使用 SQL 进行分析操作需要临时表来进行可视化或临时结果，因此，与普通数据库工作负载相比，空间 SQL 往往需要用户具有 CREATE 权限。

默认情况下，Oracle 中的每个角色都会获得个人模式。这也是一个很好的 PostgreSQL 用户实践，并且使用 PostgreSQL 角色、模式和搜索路径很容易复制。

创建一个具有表创建权限的新用户（有关 postgis_writer 角色的信息，请参阅 PostgreSQL 安全），然后创建一个以该用户作为授权的模式

CREATE USER myuser WITH ROLE postgis_writer;
CREATE SCHEMA myuser AUTHORIZATION myuser;

如果您以该用户身份登录，您会发现 PostgreSQL 的默认 search_path 实际上是这样的

show search_path;

  search_path
----------------
 "$user",public

搜索路径上的第一个模式是用户命名的模式！所以现在存在以下条件

仅此而已，用户默认工作区现在与其他模式中的任何表都很好地分隔开来。