PostgreSQL中的数据库实例、模式、用户（角色）、表空间

最新推荐文章于 2023-12-12 14:44:27 发布

weixin_34167819

最新推荐文章于 2023-12-12 14:44:27 发布

阅读量645

点赞数

原文链接：https://my.oschina.net/liuyuanyuangogo/blog/497272

版权

2019独角兽企业重金招聘Python工程师标准>>>

本文参考：http://blog.csdn.net/kanon_lgt/article/details/5931522

http://blog.csdn.net/kanon_lgt/article/details/6220928

DB实例与schema: 模式是数据库实例的逻辑分割。

数据库是被模式(schema)来切分的，一个数据库至少有一个模式，所有数据库内部的对象(object)是被创建于模式的。用户登录到系统，连接到一个数据库后，是通过该数据库的search_path来寻找schema的搜索顺序：

可以通过命令SHOW search_path；来查看具体搜索顺序（默认如下）：

postgres=# show search_path;
  search_path
----------------
 "$user",public

也可以通过SET search_path TO 'schema_name'来修改顺序。

可以通过\d 表名来查看表所属的模式：

postgres=# \d test
  资料表"postgres.test"
  栏位 | 型别 | 修饰词
------+---------+--------
id   | integer |

官方建议是这样的：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"(即缺省模式为与用户名相同的模式)，这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

角色（role）与用户（user）：对于PostgreSQL来说，这是完全相同的两个对象。

唯一的区别是在创建的时候：

CREATE ROLE kanon PASSWORD 'kanon';   --(ROLE创建时缺省不具有LOGIN权限)
CREATE USER kanon PASSWORD 'kanon2';  --(USER创建时缺省就具备了LOGIN权限)

CREATE ROLE kanon PASSWORD 'kanon' LOGIN;
---等同于
CREATE USER kanon PASSWORD 'kanon';

ROLE不具有缺省的LOGIN权限，这就是ROLE/USER的区别。

表空间与数据库：一个表空间可以存储多个数据库（虽然不建议如此，但却是可行的）

数据库创建语句CREATE DATABASE dbname 默认的数据库所有者是当前创建数据库的用户，默认的表空间是系统的默认表空间--pg_default。

在PostgreSQL中，数据库的创建是通过克隆数据库模板来实现的，这与SQL SERVER是同样的机制。CREATE DATABASE dbname并没有指明数据库模板，所以系统将默认克隆缺省的template1数据库。而template1数据库的默认表空间是pg_default，这个表空间是在数据库初始化时创建的，所以所有template1中的对象将被同步克隆到新的数据库中。

数据库创建时相对完整的语法应该是：

CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;

表空间的概念：表空间就是一个简单的目录，其主要用途分两个：

    1.单独扩展表空间用，一旦磁盘或分区被耗尽，可以创建一个表空间到其他磁盘或分区上面。
    2.区分不同对象的存储位置，比如可将索引放入较快磁盘的表空间上，而将固定不变的数据放入较慢磁盘的表空间上。
    与Oracle数据库中的表空间被独占不同，PostgreSQL的表空间是可以被共享的。当创建了一个表空间后，这个表空间可以被多个数据库、表、索引等数据库对象使用。达到对象的分离与归类的目的。
在PostgreSQL中有两个系统自建表空间：pg_global和pg_default。

前者是系统全局表空间，存储了关键的共享系统目录。后者是系统全局表空间，存储了关键的共享系统目录。
后者是系统默认表空间，可通过set default tablespace=tablespacename来指定为其他表空间，在建立数据库、表、索引等数据库对象时，若不指定表空间参数，则系统自动将对象创建到默认表空间中。如create table tt(id int) tablespace space1.该语句等价于set default tablespace=space1;create table tt(id int);

tablespace的记录存储在系统数据库postgrse的pg_tablspace表中，可以使用命令/db 或者sql语句select * from pg_tablespace命令查看。

                   List of tablespaces
   Name    |  Owner   |             Location
-----------+----------+-----------------------------------
pg_default  | postgres  |
pg_global   | postgres   |
ts_licai    | kanon       | D:/PostgreSQL90/TableSpc/ts_licai

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

总结：

表空间是一个存储区域，在一个表空间中可以存储多个数据库，尽管PostgreSQL不建议这么做，但我们这么做完全可行。

一个数据库并不直接存储表结构等对象的，而是在数据库中逻辑创建了至少一个模式，在模式中创建了表等对象，将不同的模式指派给不同的角色，可以实现权限分离，又可以通过授权，实现模式间对象的共享，并且，还有一个特点就是：public模式可以存储大家都需要访问的对象。

一个表在创建的时候可以指定表空间，那么，是否可以给一个表指定它所在的数据库表空间之外的表空间呢？答案是肯定的!这么做完全可以：那这不是违背了表属于模式，而模式属于数据库，数据库最终存在于指定表空间这个网的模型了吗？！是的，看上去这确实是不合常理的，但这么做又是有它的道理的，而且现实中，我们往往需要这么做：将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间。

但我们再查看表所属的模式还是没变的，它依然属于指定的模式。所以这并不违反常理。实际上，PostgreSQL并没有限制一张表必须属于某个特定的表空间，我们之所以会这么认为，是因为在关系递进时，偷换了一个概念：模式是逻辑存在的，它不受表空间的限制。

转载于:https://my.oschina.net/liuyuanyuangogo/blog/497272