PostgreSQL教程(十三)：資料庫管理詳解，postgresql詳解

PostgreSQL教程(十三)：資料庫管理詳解，postgresql詳解

一、概述：

    資料庫可以被看成是SQL對象(資料庫物件)的命名集合，通常而言，每個資料庫物件(表、函數等)只屬於一個資料庫。不過對於部分系統資料表而言，如pg_database，是屬於整個叢集的。更準確地說，資料庫是模式的集合，而模式包含表、函數等SQL對象。因此完整的對象層次應該是這樣的：伺服器、資料庫、模式、表或其他類型的對象。

    在與資料庫伺服器建立串連時，該串連只能與一個資料庫形成關聯，不允許在一個會話中進行多個資料庫的訪問。如以postgres使用者登入，該使用者可以訪問的預設資料庫為postgres，在登入後如果執行下面的SQL語句將會收到PostgreSQL給出的相關錯誤資訊。
複製代碼 代碼如下:
    postgres=# SELECT * FROM MyTest."MyUser".testtables;
    ERROR:  cross-database references are not implemented: "otherdb.otheruser.sometable"
    LINE 1: select * from otherdb.otheruser.sometable
 
    在PostgreSQL中，資料庫在物理上是相互隔離的，對它們的存取控制也是在會話層次上進行的。然而模式只是邏輯上的對象管理結構，是否能訪問某個模式的對象是由許可權系統來控制的。
    執行下面的基於系統資料表的查詢語句可以列出現有的資料庫集合。
 複製代碼 代碼如下:
    SELECT datname FROM pg_database;
 
    註：psql應用程式的\l元命令和-l命令列選項也可以用來列出當前伺服器中已有的資料庫。 
   
二、建立資料庫：

    在PostgreSQL伺服器上執行下面的SQL語句可以建立資料庫。
 複製代碼 代碼如下:
    CREATE DATABASE db_name;
 
    在資料庫成功建立之後，當前登入角色將自動成為此新資料庫的所有者。在刪除該資料庫時，也需要該使用者的特權。如果你想讓當前建立的資料庫的所有者為其它角色，可以執行下面的SQL語句。
 複製代碼 代碼如下:
    CREATE DATABASE db_name OWNER role_name;
 

三、修改資料庫配置：

    PostgreSQL伺服器提供了大量的運行時組態變數，我們可以根據自己的實際情況為某一資料庫的某一組態變數指定特殊值，通過執行下面的SQL命令可以使該資料庫的某一配置被設定為指定值，而不再使用預設值。
 複製代碼 代碼如下:
    ALTER DATABASE db_name SET varname TO new_value;
 
    這樣在之後基於該資料庫的會話中，被修改的配置值已經生效。如果要撤消這樣的設定並恢複為原有的預設值，可以執行下面的SQL命令。
 複製代碼 代碼如下:
    ALTER DATABASE dbname RESET varname;
 

四、刪除資料庫：

    只有資料庫的所有者和超級使用者可以刪除資料庫。刪除資料庫將會刪除資料庫中包括的所有對象，該操作是不可恢複的。見如下刪除SQL命令：
 複製代碼 代碼如下:
    DROP DATABASE db_name;
 
   
五、資料表空間：

    在PostgreSQL中，資料表空間表示一組檔案存放的目錄位置。在建立之後，就可以在該資料表空間上建立資料庫物件。通過使用資料表空間，管理員可以控制一個PostgreSQL伺服器的磁碟布局。這樣管理員就可以根據資料庫物件的資料量和資料使用頻度等參照來規劃這些對象的儲存位置，以便減少IO等待，從而最佳化系統的整體運行效能。比如，將一個使用頻繁的索引放在非常可靠、高效的磁碟裝置上，如固態硬碟。而將很少使用的資料庫物件存放在相對較慢的磁碟系統上。下面的SQL命令用於建立資料表空間。
 複製代碼 代碼如下:
    CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
 
    需要說明的是，資料表空間指定的位置必須是一個現有的空目錄，且屬於PostgreSQL系統使用者，如postgres。在成功建立之後，所有在該資料表空間上建立的對象都將被存放在這個目錄下的檔案裡。
    在PostgreSQL中只有超級使用者可以建立資料表空間，但是在成功建立之後，就可以允許普通資料庫使用者在其上建立資料庫物件了。要完成此操作，必須在資料表空間上給這些使用者授予CREATE許可權。表、索引和整個資料庫都可以放在特定的資料表空間裡。見如下SQL命令：
 複製代碼 代碼如下:
    CREATE TABLE foo(i int) TABLESPACE space1;
 
    此外，我們還可以通過修改default_tablespace組態變數，以使指定的資料表空間成為預設資料表空間，這樣在建立任何資料庫物件時，如果沒有顯示指定資料表空間，那麼該對象將被建立在預設資料表空間中，如：
 複製代碼 代碼如下:
    SET default_tablespace = space1;
    CREATE TABLE foo(i int);
 
    與資料庫相關聯的資料表空間用於儲存該資料庫的系統資料表，以及任何使用該資料庫的伺服器處理序建立的臨時檔案。
    要刪除一個空的資料表空間，可以直接使用DROP TABLESPACE命令，然而要刪除一個包含資料庫物件的資料表空間，則需要先將該資料表空間上的所有對象全部刪除後，才可以再在刪除該資料表空間。

    要檢索當前系統中有哪些資料表空間，可以執行以下查詢，其中pg_tablespace為PostgreSQL中的系統資料表。
 複製代碼 代碼如下:
    SELECT spcname FROM pg_tablespace;
 
    我們還可以通過psql程式的\db元命令列出現有的資料表空間。