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並沒有限制一張表必須屬於某個特定的資料表空間，我們之所以會這麼認為，是因為在關係遞進時，偷換了一個概念：模式是邏輯存在的，它不受資料表空間的限制。

 

回顧：看文檔只是知道一個事實的存在，親手實驗驗證這個事實是真實的，思考則能從事實中得出另一個事實

PostgreSQL資料表空間、資料庫、模式、表、使用者/角色之間的關係（轉）