Greenplum中定义数据库对象之创建与管理模式

创建与管理模式

• 概述：DB内组织对象的一种逻辑结构，一个DB内可以有多个模式，在未指定模式时默认放置在public中，可以通过”\dn”方式查看数据库中现有模式；

testdw=# \dn        List of schemas         Name        |  Owner  --------------------+---------  gp_toolkit         | gpadmin  information_schema | gpadmin  pg_aoseg           | gpadmin  pg_bitmapindex     | gpadmin  pg_catalog         | gpadmin  pg_toast           | gpadmin  public             | gpadmin (7 rows)

•   创建模式：使用CREATESCHEMA命令，通过查看帮助如下所示：

testdw=# \h CREATE SCHEMA Command:     CREATE SCHEMA Description: define a new schema Syntax: CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]   将所有者设置为其他角色通过AUTHORIZTION CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]

• 访问模式的对象：schema.table

testdw=# CREATE SCHEMA sc01; CREATE SCHEMA testdw=# \dn        List of schemas         Name        |  Owner  --------------------+---------  gp_toolkit         | gpadmin  information_schema | gpadmin  pg_aoseg           | gpadmin  pg_bitmapindex     | gpadmin  pg_catalog         | gpadmin  pg_toast           | gpadmin  public             | gpadmin  sc01               | gpadmin (8 rows) testdw=# create schema sc02 authorization mavshuang; ERROR:  permission denied for database testdw  (seg1 slave2:40000 pid=5424)  提示testdw数据库中权限拒绝 testdw=# grant all on database testdw to mavshuang;            将testdw数据库的所有权限赋给mavshuang GRANT testdw=# create schema sc02 authorization mavshuang; CREATE SCHEMA testdw=# \dn         List of schemas         Name        |   Owner   --------------------+-----------  gp_toolkit         | gpadmin  information_schema | gpadmin  pg_aoseg           | gpadmin  pg_bitmapindex     | gpadmin  pg_catalog         | gpadmin  pg_toast           | gpadmin  public             | gpadmin  sc01               | gpadmin  sc02               | mavshuang                    此时用户是mavshuang (9 rows)

• 模式搜索路径：若不想通过指定模式名称的方式来搜索需要的对象，可以通过设置search_path的方式来实现，第一个模式为缺省。

testdw=# show search_path;   search_path   ----------------  "$user",public (1 row)

•  通过ALTERDATABASE修改DB的模式搜索路径

testdw-# \h alter database Command:     ALTER DATABASE Description: change a database Syntax: ALTER DATABASE name [ [ WITH ] option [ ... ] ] where option can be:     CONNECTION LIMIT connlimit ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }    通过此命令来修改DB的模式搜索路径 ALTER DATABASE name RESET parameter ALTER DATABASE name RENAME TO newname ALTER DATABASE name OWNER TO new_owner

 

testdw=# alter database testdw set search_path to sc01,public,pg_catalog;   设置testdw数据库的搜索路径为sc01,public,pg_catalog; ALTER DATABASE testdw=# \q                                  修改完成后通过\q退出testdw数据库后重新登录 [gpadmin@master ~]$ psql -d testdw psql (8.2.15) Type "help" for help.   testdw=# show search_path;        search_path        --------------------------  sc01, public, pg_catalog (1 row)

 

• 通过ALTER ROLE修改ROLE(User)的模式搜索路径：

testdw-# \h alter role Command:     ALTER ROLE Description: change a database role Syntax: ALTER ROLE name RENAME TO newname ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT} ALTER ROLE name RESET config_parameter ALTER ROLE name RESOURCE QUEUE {queue_name | NONE} ALTER ROLE name [ [WITH] option [ ... ] ] where option can be:       SUPERUSER | NOSUPERUSER     | CREATEDB | NOCREATEDB     | CREATEROLE | NOCREATEROLE     | CREATEEXTTABLE | NOCREATEEXTTABLE       [ ( attribute='value'[, ...] ) ]            where attributes and values are:            type='readable'|'writable'            protocol='gpfdist'|'http'|'gphdfs' | INHERIT | NOINHERIT     | LOGIN | NOLOGIN     | CONNECTION LIMIT connlimit     | [ENCRYPTED | UNENCRYPTED] PASSWORD 'password'     | VALID UNTIL 'timestamp'

 

testdw=# select * from pg_roles;    查询pg_roles字典表   rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | rolresqueue |  oid  | rolcreaterextgpfd | rolcreaterexthttp | rolcreatewextgpfd | rolcreaterexthdfs | olcreatewexthdfs -----------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+-------------+-------+-------------------+-------------------+-------------------+-------------------+- -----------------  mavshuang | f        | t          | f             | f           | f            | t           |           -1 | ********    |               |           |        6055 | 16384 | f                 | f                 | f                 | f                 |    admin     | f        | t          | t             | t           | f            | f           |           -1 | ********    |               |           |        6055 | 16385 | f                 | f                 | f                 | f                 |    gpadmin   | t        | t          | t             | t           | t            | t           |           -1 | ********    |               |           |        6055 |    10 | t                 | t                 | t                 | t                 |   (3 rows)   testdw=# alter role mavshuang set search_path to public,sc01,pg_catalog;      修改mavshuang角色的搜索路径为public,sc01,pg_catalog; ALTER ROLE testdw=# select * from pg_roles;                                          再次查询显示   rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil |                rolconfig                 | rolresqueue |  oid  | rolcreaterextgpfd | rolcreaterexthttp | rolcreate extgpfd | rolcreaterexthdfs | rolcreatewexthdfs -----------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+------------------------------------------+-------------+-------+-------------------+-------------------+---------- --------+-------------------+-------------------  admin     | f        | t          | t             | t           | f            | f           |           -1 | ********    |               |                                          |        6055 | 16385 | f                 | f                 | f                | f                 | f  gpadmin   | t        | t          | t             | t           | t            | t           |           -1 | ********    |               |                                          |        6055 |    10 | t                 | t                 | t                | t                 | t  mavshuang | f        | t          | f             | f           | f            | t           |           -1 | ********    |               |{"search_path=public, sc01, pg_catalog"}|        6055 | 16384 | f                 | f                 | f                | f                 | f (3 rows)

• 查看当前的模式：通过current_schema()函数或者SHOW命令来查看：

testdw=# select current_schema();    只能显示一个模式  current_schema ----------------  sc01 (1 row)   testdw=# show search_path;   显示当前数据库所有的模式        search_path        --------------------------  sc01, public, pg_catalog (1 row)

 

• 删除模式：使用DROPSCHEMA命令(空模式)

testdw=# \h drop schema Command:     DROP SCHEMA Description: remove a schema Syntax: DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]  当该模式下有对象时可以使用CASCADE命令 testdw=# drop schema sc01; DROP SCHEMA

• 系统模式

pg_catalog模式存储系统日志表.内置类型.函数和运算符。

Information_schem模式由一个标准化视图构成。其中包含DB中对象的信息。

pg_toast模式是存储大对象(系统内部使用)

pg_bitmapindex模式存储bitmap index对象(系统内部使用)

pg_aoseg存储append-only表(系统内部使用)

gp_toolkit是管理用的模式，可以查看和检索系统日志文件和其他系统信息。