一、创建模式
·标准语句
postgres=# create schema tdsql_pg;CREATE SCHEMA
·扩展语法，不存在时才创建
postgres=# create schema if not exists tdsql_pg ; NOTICE: schema "tdsql_pg" already exists, skippingCREATE SCHEMApostgres=#
·指定所属用户
postgres=# create schema tdsql_pg_pgxz AUTHORIZATION pgxz; CREATE SCHEMA postgres=# \\dn tdsql_pg_pgxzList of schemas Name | Owner ————+——-tdsql_pg_pgxz | pgxz (1 row)
二、修改模式属性
·修改模式名
postgres=# alter schema tdsql_pg rename to tdsql_pg_new; ALTER SCHEMA
·修改所有者
postgres=# alter schema tdsql_pg_pgxz owner to tdsql_pg; ALTER SCHEMA
三、删除模式
postgres=# drop schema tdsql_pg_new;DROP SCHEMA
当模式中存在对象时，则会删除失败，提示如下。
postgres=# create table tdsql_pg_pgxz.t(id int); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE postgres=# drop schema tdsql_pg_pgxz; ERROR: cannot drop schema tdsql_pg_pgxz because other objects depend on it DETAIL: table tdsql_pg_pgxz.t depends on schema tdsql_pg_pgxz HINT: Use DROP … CASCADE to drop the dependent objects too.
参考如下强制删除。
postgres=# drop schema tdsql_pg_pgxz CASCADE; NOTICE: drop cascades to table tdsql_pg_pgxz.t DROP SCHEMA
四、配置用户访问模式权限
普通用户对于某个模式下的对象访问除了访问对象要授权外，模式也需要授权。
[tbase@VM_0_37_centos root]$ psql -U dbadmin psql (PostgreSQL 10.0 tdsql_pg V2) Type "help" for help. postgres=# create table tdsql_pg.t(id int); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE
授权用户可以访问 tdsql_pg.t 表。
postgres=# grant select on tdsql_pg.t to pgxz;GRANTpostgres=# \\q
pgxz 用户登录。
[tbase@VM_0_37_centos root]$ psql -U pgxzpsql (PostgreSQL 10.0 tdsql_pg V2)Type "help" for help.
在没授权用户可以使用 tdsql_pg 模式前，还是无法访问。
postgres=> select * from tdsql_pg.t;ERROR: permission denied for schema tdsql_pgLINE 1: select * from tdsql_pg.t; ^postgres=> \\q
给 schema 授权。
[tbase@VM_0_37_centos root]$ psql -U dbadminpsql (PostgreSQL 10.0 tdsql_pg V2)Type "help" for help.postgres=# grant USAGE on SCHEMA tdsql_pg to pgxz;GRANTpostgres=# \\q
pgxz 用户再次访问表。
[tbase@VM_0_37_centos root]$ psql -U pgxzpsql (PostgreSQL 10.0 tdsql_pg V2)Type "help" for help.postgres=> select * from tdsql_pg.t; id \\—-(0 rows)postgres=>
五、配置访问模式的顺序
TDSQL PostgreSQL版 有一个运行变量叫 search_path，其值为模式名列表，用于配置访问数据对象的顺序，如下所示。
当前连接用户。
postgres=# select current_user; current_user ————– tdsql_pg (1 row)
总共三个模式。
postgres=# \\dn List of schemas Name | Owner ————–+——- public | dbadmin tdsql_pg | dbadmin tdsql_pg_schema | dbadmin(3 rows)
搜索路径只配置为 "$user", public，其中 "$user" 为当前用户名，即上面的 current_user 值 “tdsql_pg”。
postgres=# show search_path ; search_path —————– "$user", public (1 row)
不指定模式创建数据表，则该表存放于第一个搜索模式下面。
postgres=# create table t(id int,mc text); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE postgres=# \\dt List of relations Schema | Name | Type | Owner ——–+——+——-+——- tdsql_pg | t | table | dbadmin (1 row)
指定表位于某个模式下，不同模式下表名可以相同。
postgres=# create table public.t(id int,mc text); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command. CREATE TABLE postgres=# \\dt public.t List of relations Schema | Name | Type | Owner ——–+——+——-+——- public | t | table | dbadmin (1 row)
postgres=# create table tdsql_pg_schema.t1(id int,mc text); NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=#
访问不在搜索路径对象时，需要写全路径。
postgres=# select * from t1;ERROR: relation "t1" does not existLINE 1: select * from t1; ^postgres=# select * from tdsql_pg_schema.t1; id | mc —-+—-(0 rows)
上面出错是因为模式 tdsql_pg_schema 没有配置在 search_path 搜索路径中。