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

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

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

一、概述：

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

在與數(shù)據(jù)庫服務(wù)器建立連接時(shí)，該連接只能與一個(gè)數(shù)據(jù)庫形成關(guān)聯(lián)，不允許在一個(gè)會(huì)話中進(jìn)行多個(gè)數(shù)據(jù)庫的訪問。如以postgres用戶登錄，該用戶可以訪問的缺省數(shù)據(jù)庫為postgres，在登錄后如果執(zhí)行下面的SQL語句將會(huì)收到PostgreSQL給出的相關(guān)錯(cuò)誤信息。

復(fù)制代碼代碼如下:

    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ù)庫在物理上是相互隔離的，對它們的訪問控制也是在會(huì)話層次上進(jìn)行的。然而模式只是邏輯上的對象管理結(jié)構(gòu)，是否能訪問某個(gè)模式的對象是由權(quán)限系統(tǒng)來控制的。
執(zhí)行下面的基于系統(tǒng)表的查詢語句可以列出現(xiàn)有的數(shù)據(jù)庫集合。

復(fù)制代碼代碼如下:

SELECT datname FROM pg_database;

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

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

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

復(fù)制代碼代碼如下:

CREATE DATABASE db_name;

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

復(fù)制代碼代碼如下:

CREATE DATABASE db_name OWNER role_name;

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

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

復(fù)制代碼代碼如下:

ALTER DATABASE db_name SET varname TO new_value;

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

復(fù)制代碼代碼如下:

ALTER DATABASE dbname RESET varname;

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

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

復(fù)制代碼代碼如下:

DROP DATABASE db_name;

五、表空間：

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

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

CREATE TABLE foo(i int) TABLESPACE space1;

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

復(fù)制代碼代碼如下:

    SET default_tablespace = space1;

    CREATE TABLE foo(i int);

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

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

復(fù)制代碼代碼如下:

SELECT spcname FROM pg_tablespace;

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

您可能感興趣的文章: