更新时间:2024-08-12 GMT+08:00
分享

创建和管理Schema

Schema又称作模式,从逻辑上组织一个数据库中的对象和数据。通过管理Schema,允许多个用户使用同一数据库而不相互干扰,同时便于将第三方应用添加到相应的Schema下而不引起冲突。

相同的数据库对象名称可以应用在同一数据库的不同Schema中,而没有冲突。例如,a_schema和b_schema都可以包含名为mytable的表。具有所需权限的用户可以访问数据库的多个Schema中的对象。

在当前数据库中创建用户时,系统会在当前数据库中为新用户创建一个同名Schema。

public模式

每个数据库都有一个名为public的模式。 所有的数据库角色(用户)都在public模式上拥有USAGE特权,但是普通角色(用户)没有在public模式上的CREATE权限。

创建Schema

  • 使用CREATE SCHEMA命令来创建一个新的Schema。
    1
    CREATE SCHEMA myschema;
    

    如果需要在模式中创建或者访问对象,其完整的对象名称由模式名称和具体的对象名称组成。中间由符号“.”隔开。例如:myschema.table。

  • 用户可以创建一个由他人拥有的schema。例如,创建名为myschema的Schema,并指定Schema的所有者为用户jack。
    1
    CREATE SCHEMA myschema AUTHORIZATION jack;
    

    若不指定authorization username,则其所有者为执行该命令的用户。

修改Schema

  • 使用ALTER SCHEMA修改Schema名称,只有Schema所有者可以更改Schema。
    1
    ALTER SCHEMA schema_name RENAME TO new_name;
    
  • 使用ALTER SCHEMA修改Schema所有者:
    1
    ALTER SCHEMA schema_name OWNER TO new_owner;
    

设置Schema搜索路径

GUC参数search_path设置Schema的搜索顺序,参数取值形式为采用逗号分隔的Schema名称列表。如果创建对象时未指定目标Schema,则该对象会被添加到搜索路径中列出的第一个Schema中。当不同Schema中存在同名的对象时,查询对象未指定Schema的情况下,将从搜索路径中包含该对象的第一个Schema中返回对象。

  • 使用SHOW命令查看当前搜索路径。
    1
    2
    3
    4
    5
    SHOW SEARCH_PATH;
     search_path
    ----------------
     "$user",public
    (1 row)
    

    search_path参数的默认值为:"$user",public。$user表示与当前会话用户名同名的Schema名,如果这样的模式不存在,$user将被忽略。所以默认情况下,用户连接数据库后,如果数据库下存在同名Schema,则对象会添加到同名Schema下,否则对象被添加到Public Schema下。

  • 使用SET命令修改当前会话的默认Schema。例如,将搜索路径设置为myschema、public,首先搜索myschema。
    1
    SET SEARCH_PATH TO myschema, public;
    
    也可以使用ALTER ROLE命令为特定的角色(用户)设置search_path。例如:
    1
    ALTER ROLE jack SET search_path TO myschema, public;
    

使用Schema

在特定Schema下创建对象或者访问特定Schema下的对象,需要使用有Schema修饰的对象名。名称包含Schema名以及对象名,之间用“.”号分开。

  • 在myschema下创建mytable表。以schema_name.table_name格式创建表。
    1
    CREATE TABLE myschema.mytable(id int, name varchar(20));
    
  • 查询myschema下mytable表的所有数据。
    1
    2
    3
    4
    SELECT * FROM myschema.mytable;
     id | name
    ----+------
    (0 rows)
    

查看Schema

  • 使用current_schema()函数查看当前Schema:
    1
    2
    3
    4
    5
    SELECT current_schema();
     current_schema 
    ----------------
     myschema
    (1 row)
    
  • 要查看Schema所有者,请对系统表PG_NAMESPACE和PG_USER执行如下关联查询。语句中的schema_name请替换为实际要查找的Schema名称。
    1
    SELECT s.nspname,u.usename AS nspowner FROM PG_NAMESPACE s, PG_USER u WHERE nspname='schema_name' AND s.nspowner = u.usesysid;
    
  • 要查看所有Schema的列表,请查询PG_NAMESPACE系统表。
    1
    SELECT * FROM PG_NAMESPACE;
    
  • 使用PGXC_TOTAL_SCHEMA_INFO视图查询整个集群的Schema空间使用情况。
    1
    SELECT * FROM PGXC_TOTAL_SCHEMA_INFO;
    
  • 要查看属于某Schema下表的列表,请查询系统视图PG_TABLES。例如,以下查询会返回Schema PG_CATALOG中的表列表。
    1
    SELECT distinct(tablename),schemaname FROM PG_TABLES where schemaname = 'pg_catalog';
    

Schema的权限控制

默认情况下,用户只能访问属于自己的Schema中的数据库对象。如需要访问其他Schema的对象,则需赋予对应Schema的usage权限。

通过将模式的CREATE权限授予某用户,被授权用户就可以在此模式中创建对象。

  • 将myschema的usage权限赋给用户jack。
    1
    GRANT USAGE ON schema myschema TO jack;
    
  • 将用户jack对于myschema的usage权限收回。
    1
    REVOKE USAGE ON schema myschema FROM jack;
    

删除Schema

  • 使用DROP SCHEMA命令删除一个空的Schema(即该Schema下没有数据库对象)。
    1
    DROP SCHEMA IF EXISTS myschema;
    
  • 默认情况下,删除一个Schema前,它必须为空。 要删除一个Schema及其包含的所有对象(表、数据、函数等),需要使用CASCADE关键字。
    1
    DROP SCHEMA myschema CASCADE;
    

系统Schema

  • 每个数据库都包含一个pg_catalog schema,它包含系统表和所有内置数据类型、函数、操作符。pg_catalog是搜索路径中的一部分,始终在临时表所属的模式后面,并在search_path中所有模式的前面,即具有第二搜索优先级。这样确保可以搜索到数据库内置对象。如果用户需要使用和系统内置对象重名的自定义对象时,可以在操作自定义对象时带上自己的模式。
  • information_schema由一个包含数据库中对象信息的视图集合组成。 这些视图以一种标准化的方式从系统目录表中得到系统信息。

相关文档