PostgreSQL表空间、数据库、模式、表、用户/角色之间的关系（转）

原文：http://blog.csdn.net/kanon_lgt/article/details/5931522

   看PostgreSQL9的官方文档，我越看越迷糊，这表空间，数据库，模式，表，用户，角色之间的关系怎么在PostgreSQL里这么混乱呢？
经过中午的一个小实验，我逐渐理清了个中来龙去脉。下面我来还原我的试验，并循序讲清其中关系。

 

首先，实验出角色与用户的关系
    在PostgreSQL中，存在两个容易混淆的概念：角色/用户。之所以说这两个概念容易混淆，是因为对于PostgreSQL来说，这是完全相同的两个对象。唯一的区别是在创建的时候：
 1.我用下面的psql创建了角色kanon：
   CREATE ROLE kanon PASSWORD 'kanon';
   接着我使用新创建的角色kanon登录，PostgreSQL给出拒绝信息：

   FATAL： role 'kanon' is not permitted to log in.
   说明该角色没有登录权限，系统拒绝其登录。 
 2.我又使用下面的psql创建了用户kanon2:
   CREATE USER kanon PASSWORD 'kanon2';
   接着我使用kanon2登录，登录成功。
   难道这两者有区别吗？查看文档，又这么一段说明："CREATE USER is the same as CREATE ROLE except that it implies LOGIN."----CREATE USER除了默认具有LOGIN权限之外，其他与CREATE ROLE是完全相同的。
   为了验证这句话，修改kanon的权限，增加LOGIN权限：ALTER ROLE kanon LOGIN;再次用kanon登录，成功！
   那么，事情就明了了：CREATE ROLE kanon PASSWORD 'kanon' LOGIN 等同于CREATE USER kanon PASSWORD 'kanon'.
   这就是ROLE/USER的区别。

 

然后，数据库与模式的关系
    看文档了解到：模式（schema）是对数据库（database）逻辑分割。
在数据库创建的同时，就已经默认为数据库创建了一个模式--public，这也是该数据库的默认模式。所有为此数据库创建的对象（表、函数、试图、索引、序列等）都是常见在这个模式中的。
 实验如下：
 1.创建一个数据库dbtt----CREATE DATABASE dbtt;
 2.用kanon角色登录到dbtt数据库,查看dbtt数据库中的所有模式：/dn; 显示结果是只有public一个模式。
 3.创建一张测试表----CREATE TABLE test(id integer not null);
 4.查看当前数据库的列表： /d; 显示结果是表test属于模式public.也就是test表被默认创建在了public模式中。
 5.创建一个新模式kanon，对应于登录用户kanon：CREATE SCHEMA kanon OWNER kanon；
 6.再次创建一张test表，这次这张表要指明模式----CREATE TABLE kanon.test (id integer not null);
 7.查看当前数据库的列表： /d; 显示结果是表test属于模式kanon.也就是这个test表被创建在了kanon模式中。
   得出结论是：数据库是被模式(schema)来切分的，一个数据库至少有一个模式，所有数据库内部的对象(object)是被创建于模式的。用户登录到系统，连接到一个数据库后，是通过该数据库的search_path来寻找schema的搜索顺序,可以通过命令SHOW search_path；具体的顺序，也可以通过SET search_path TO 'schema_name'来修改顺序。
   官方建议是这样的：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"，
   这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

 

接下来，再来研究下表空间与数据库的关系
    数据库创建语句CREATE DATABASE dbname 默认的数据库所有者是当前创建数据库的角色，默认的表空间是系统的默认表空间--pg_default。
    为什么是这样的呢？因为在PostgreSQL中，数据的创建是通过克隆数据库模板来实现的，这与SQL SERVER是同样的机制。
    由于CREATE DATABASE dbname并没有指明数据库模板，所以系统将默认克隆template1数据库，得到新的数据库dbname。（By default, the new database will be created by cloning the standard system database template1）.

    而template1数据库的默认表空间是pg_default，这个表空间是在数据库初始化时创建的，所以所有template1中的对象将被同步克隆到新的数据库中。
    相对完整的语法应该是这样的：CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename; 
    下面我们来做个实验验证一下：
 1.连接到template1数据库，创建一个表作为标记：CREATE TABLE tbl_flag(id integer not null);向表中插入数据INSERT INTO tbl_flag VALUES (1);
 2.创建一个表空间:CREATE TABLESPACE tskanon OWNER kanon LOCATION '/tmp/data/tskanon';在此之前应该确保目录/tmp/data/tskanon存在，并且目录为空。
 3.创建一个数据库，指明该数据库的表空间是刚刚创建的tskanon：CREATE DATABASE dbkanon TEMPLATE template1 OWNERE kanon TABLESPACE tskanon;
 4.查看系统中所有数据库的信息：/l；可以发现，dbkanon数据库的表空间是tskanon,拥有者是kanon;
 5.连接到dbkanon数据库，查看所有表结构:/d;可以发现，在刚创建的数据库中居然有了一个表tbl_flag,查看该表数据，输出结果一行一列，其值为1，说明，该数据库的确是从template1克隆而来。

 仔细分析后，不难得出结论：在PostgreSQL中，表空间是一个目录，里面存储的是它所包含的数据库的各种物理文件。

 

最后，我们回头来总结一下这张关系网
    表空间是一个存储区域，在一个表空间中可以存储多个数据库，尽管PostgreSQL不建议这么做，但我们这么做完全可行。
    一个数据库并不知直接存储表结构等对象的，而是在数据库中逻辑创建了至少一个模式，在模式中创建了表等对象，将不同的模式指派该不同的角色，可以实现权限分离，又可以通过授权，实现模式间对象的共享，并且，还有一个特点就是：public模式可以存储大家都需要访问的对象。
    这样，我们的网就形成了。可是，既然一个表在创建的时候可以指定表空间，那么，是否可以给一个表指定它所在的数据库表空间之外的表空间呢？
    答案是肯定的!这么做完全可以：那这不是违背了表属于模式，而模式属于数据库，数据库最终存在于指定表空间这个网的模型了吗？！
    是的，看上去这确实是不合常理的，但这么做又是有它的道理的，而且现实中，我们往往需要这么做：将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间。
    但我们再查看表所属的模式还是没变的，它依然属于指定的模式。所以这并不违反常理。实际上，PostgreSQL并没有限制一张表必须属于某个特定的表空间，我们之所以会这么认为，是因为在关系递进时，偷换了一个概念：模式是逻辑存在的，它不受表空间的限制。

 

回顾：看文档只是知道一个事实的存在，亲手实验验证这个事实是真实的，思考则能从事实中得出另一个事实