PostgreSQL教程(十三)：數(shù)據(jù)庫管理詳解

更新時間：2015年05月04日 10:38:48 投稿：junjie

這篇文章主要介紹了PostgreSQL教程(十三)：數(shù)據(jù)庫管理詳解,本文講解了概述、創(chuàng)建數(shù)據(jù)庫、修改數(shù)據(jù)庫配置、刪除數(shù)據(jù)庫、表空間,需要的朋友可以參考下

一、概述：

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

在與數(shù)據(jù)庫服務器建立連接時，該連接只能與一個數(shù)據(jù)庫形成關聯(lián)，不允許在一個會話中進行多個數(shù)據(jù)庫的訪問。如以postgres用戶登錄，該用戶可以訪問的缺省數(shù)據(jù)庫為postgres，在登錄后如果執(zhí)行下面的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中，數(shù)據(jù)庫在物理上是相互隔離的，對它們的訪問控制也是在會話層次上進行的。然而模式只是邏輯上的對象管理結(jié)構(gòu)，是否能訪問某個模式的對象是由權限系統(tǒng)來控制的。
執(zhí)行下面的基于系統(tǒng)表的查詢語句可以列出現(xiàn)有的數(shù)據(jù)庫集合。

復制代碼代碼如下:

SELECT datname FROM pg_database;

注：psql應用程序的\l元命令和-l命令行選項也可以用來列出當前服務器中已有的數(shù)據(jù)庫。

二、創(chuàng)建數(shù)據(jù)庫：

在PostgreSQL服務器上執(zhí)行下面的SQL語句可以創(chuàng)建數(shù)據(jù)庫。

復制代碼代碼如下:

CREATE DATABASE db_name;

在數(shù)據(jù)庫成功創(chuàng)建之后，當前登錄角色將自動成為此新數(shù)據(jù)庫的所有者。在刪除該數(shù)據(jù)庫時，也需要該用戶的特權。如果你想讓當前創(chuàng)建的數(shù)據(jù)庫的所有者為其它角色，可以執(zhí)行下面的SQL語句。

復制代碼代碼如下:

CREATE DATABASE db_name OWNER role_name;

三、修改數(shù)據(jù)庫配置：

PostgreSQL服務器提供了大量的運行時配置變量，我們可以根據(jù)自己的實際情況為某一數(shù)據(jù)庫的某一配置變量指定特殊值，通過執(zhí)行下面的SQL命令可以使該數(shù)據(jù)庫的某一配置被設置為指定值，而不再使用缺省值。

復制代碼代碼如下:

ALTER DATABASE db_name SET varname TO new_value;

這樣在之后基于該數(shù)據(jù)庫的會話中，被修改的配置值已經(jīng)生效。如果要撤消這樣的設置并恢復為原有的缺省值，可以執(zhí)行下面的SQL命令。

復制代碼代碼如下:

ALTER DATABASE dbname RESET varname;

四、刪除數(shù)據(jù)庫：

只有數(shù)據(jù)庫的所有者和超級用戶可以刪除數(shù)據(jù)庫。刪除數(shù)據(jù)庫將會刪除數(shù)據(jù)庫中包括的所有對象，該操作是不可恢復的。見如下刪除SQL命令：

復制代碼代碼如下:

DROP DATABASE db_name;

五、表空間：

在PostgreSQL中，表空間表示一組文件存放的目錄位置。在創(chuàng)建之后，就可以在該表空間上創(chuàng)建數(shù)據(jù)庫對象。通過使用表空間，管理員可以控制一個PostgreSQL服務器的磁盤布局。這樣管理員就可以根據(jù)數(shù)據(jù)庫對象的數(shù)據(jù)量和數(shù)據(jù)使用頻度等參照來規(guī)劃這些對象的存儲位置，以便減少IO等待，從而優(yōu)化系統(tǒng)的整體運行性能。比如，將一個使用頻繁的索引放在非?？煽?、高效的磁盤設備上，如固態(tài)硬盤。而將很少使用的數(shù)據(jù)庫對象存放在相對較慢的磁盤系統(tǒng)上。下面的SQL命令用于創(chuàng)建表空間。

復制代碼代碼如下:

CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

需要說明的是，表空間指定的位置必須是一個現(xiàn)有的空目錄，且屬于PostgreSQL系統(tǒng)用戶，如postgres。在成功創(chuàng)建之后，所有在該表空間上創(chuàng)建的對象都將被存放在這個目錄下的文件里。
在PostgreSQL中只有超級用戶可以創(chuàng)建表空間，但是在成功創(chuàng)建之后，就可以允許普通數(shù)據(jù)庫用戶在其上創(chuàng)建數(shù)據(jù)庫對象了。要完成此操作，必須在表空間上給這些用戶授予CREATE權限。表、索引和整個數(shù)據(jù)庫都可以放在特定的表空間里。見如下SQL命令：

復制代碼代碼如下:

CREATE TABLE foo(i int) TABLESPACE space1;

此外，我們還可以通過修改default_tablespace配置變量，以使指定的表空間成為缺省表空間，這樣在創(chuàng)建任何數(shù)據(jù)庫對象時，如果沒有顯示指定表空間，那么該對象將被創(chuàng)建在缺省表空間中，如：

復制代碼代碼如下:

    SET default_tablespace = space1;

    CREATE TABLE foo(i int);

與數(shù)據(jù)庫相關聯(lián)的表空間用于存儲該數(shù)據(jù)庫的系統(tǒng)表，以及任何使用該數(shù)據(jù)庫的服務器進程創(chuàng)建的臨時文件。
要刪除一個空的表空間，可以直接使用DROP TABLESPACE命令，然而要刪除一個包含數(shù)據(jù)庫對象的表空間，則需要先將該表空間上的所有對象全部刪除后，才可以再在刪除該表空間。

要檢索當前系統(tǒng)中有哪些表空間，可以執(zhí)行以下查詢，其中pg_tablespace為PostgreSQL中的系統(tǒng)表。

復制代碼代碼如下:

SELECT spcname FROM pg_tablespace;

我們還可以通過psql程序的\db元命令列出現(xiàn)有的表空間。

您可能感興趣的文章: