747
PostgreSQL服務器管理：管理數據庫
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
一個數據庫是一些SQL對象（"數據庫對象"）的命名集合。通常每個數據庫對象（表、函數等） 屬於並且隻屬於一個數據庫（不過有幾個係統表如pg_database屬於整個集簇並且對集簇中的每個數據庫都是可訪問的）。更準確地說，一個數據庫是一個模式的集合， 而模式包含表、函數等等。因此完整的層次是這樣的：服務器、數據庫、模式、表（或者某些其他對象類型，如函數）。
當連接到數據庫服務器時，客戶端必須在它的連接請求中指定它要連接的數據庫名。每次連接不能訪問超過一個數據庫。不過，一個應用能夠在同一個或者其他數據庫上打開的連接數並沒有受到限製。數據庫是物理上相互隔離的，並且訪問控製是在連接層麵進行管理的。 如果一個PostgreSQL服務器實例用於承載那些應該分隔並且相互之間並不知曉的用戶 和項目，那麼我們建議把它們放在不同的數據庫裏。如果項目或者用戶是相互關聯的， 並且可以相互使用對方的資源，那麼應該把它們放在同一個數據庫裏，但可能在不同的模式 中。模式隻是一個純粹的邏輯結構並且誰能訪問某個模式由權限係統管理。
數據庫是使用CREATE DATABASE，並且用DROP DATABASE命令刪除。要確定現有數據庫的集合，可以檢查係統目錄pg_database，例如
SELECT datname FROM pg_database;
psql程序的\l元命令和-l命令行選項也可以用來列出已有的數據庫。
Note:
SQL標準把數據庫稱作"目錄"，不過實際上沒有區別。
為了創建一個數據庫，PostgreSQL服務器必須啟動並運行。
數據庫用 SQL 命令CREATE DATABASE創建：
CREATE DATABASE name;
其中name遵循SQL標識符的一般規則。當前角色自動成為該新數據庫的擁有者。以後刪除這個數據庫也是該擁有者的特權（同時還會刪除其中的所有對象，即使那些對象有不同的擁有者）。
因為你需要連接到數據庫服務器來執行CREATE DATABASE命令， 那麼還有一個問題是任意給定站點的第一個數據庫是怎樣創建的？第一個數據庫總是由initdb命令在初始化數據存儲區域時創建的。這個數據庫被稱為postgres。因此要創建第一個"普通"數據庫時，你可以連接到postgres。
在數據庫集簇初始化期間也會創建第二個數據庫template1,。當在集簇中創建一個新數據庫時，實際上就是克隆了template1。這就意味著你對template1所做的任 何修改都會體現在所有隨後創建的數據庫中。因此應避免在template1中創建對象，除非你想把它們傳播到每一個新創建的數據庫中。
為了方便，你還可以用一個程序來創建新數據庫： createdb。
createdb dbname
createdb沒什麼神奇的。它連接到postgres數據庫並且發出CREATE DATABASE命令，和前麵介紹的完全一樣。createdb參考頁包含了調用細節。注意不帶任何參數的createdb將創建一個使用當前用戶名的數據庫。
有時候你想為其他人創建一個數據庫，並且使其成為新數據庫的擁有者， 這樣他們就可以自己配置和管理這個數據庫。要實現這個目標，使用下列命令之一： 用於 SQL 環境的
CREATE DATABASE dbname OWNER rolename;
或者用於 shell 的
createdb -O rolename dbname
隻有超級用戶才被允許為其他人（即為一個你不是其成員的角色）創建一個數據庫。
CREATE DATABASE實際上通過拷貝一個已有數據庫進行工作。默認情況下，它拷貝名為template1的標準係統數據庫。所以該數據庫是創建新數據庫的"模板"。 如果你為template1數據庫增加對象，這些對象將被拷貝到後續創建的用戶數據庫中。 這種行為允許對數據庫中標準對象集合的站點本地修改。例如，如果你把過程語言PL/Perl安裝到 template1中，那麼你在創建用戶數據庫後不需要額外的操作就可以使用該語言。
係統裏還有名為template0的第二個標準係統數據庫。這個數據庫包含和template1初始內容一樣的數據，也就是說，隻包含你的PostgreSQL版本預定義的標準對象。在數據庫集簇被初始化之後，不應該對template0做任何修改。通過指示CREATE DATABASE使用template0取代template1進行拷貝，你可以創建一個"純淨的"用戶數據庫，它不會包含任何template1中的站點本地附加物。 這一點在恢複一個pg_dump轉儲時非常方便：轉儲腳本應該在一個純淨的數據庫 中恢複以確保我們重建被轉儲數據庫的正確內容，而不和任何現在可能已經被加入到template1中的附加物相衝突。
另一個從template0而不是template1複製的常見原因是， 可以在複製template0時指定新的編碼和區域設置，而一個template1的副本必須使用和它相同的設置。這是因為的template1可能包含編碼相關或區域相關的數據，而template0中沒有。
要通過拷貝template0來創建一個數據庫，使用：SQL 環境中的
CREATE DATABASE dbname TEMPLATE template0;
或者 shell 中的
createdb -T template0 dbname
可以創建額外的模板數據庫，並且實際上可以通過將集簇中任意數據庫指定為CREATE DATABASE的模板來從該數據庫拷貝。不過，我們必需明白，這個功能並不是設計作為一般性的"COPY DATABASE"功能。主要的限製是當源數據庫被拷貝時，不能有其他會話連接到它。如果在CREATE DATABASE開始時存在任何其它連接，那麼該命令將會失敗。在拷貝操作期間，到源數據庫的新連接將被阻止。
對於每一個數據庫在pg_database中存在兩個有用的標誌： datistemplate和datallowconn列。datistemplate可以被設置來指示該數據庫是不是要作為CREATE DATABASE的模板。如果設置了這個標誌，那麼該數據庫可以被任何有 CREATEDB權限的用戶克隆；如果沒有被設置，那麼隻有超級用戶和該數據庫的擁有者可以克隆它。如果datallowconn為假，那麼將不允許與該數據庫建立任何新的連接（但已有的會話不會因為把該標誌設置為假而被中止）。template0通常被標記為datallowconn = false來阻止對它的修改。template0和template1通常總是被標記為datistemplate = true。
Note:
除了template1是CREATE DATABASE的默認源數據庫名之外， template1和template0沒有任何特殊的狀態。例如，我們可以刪除template1然後從template0重新創建它而不會有任何不良效果。如果我們不小心在template1中增加了一堆垃圾，那麼我們就會建議做這樣的操作（要刪除template1，它必須有pg_database.datistemplate = false）。
當數據庫集簇被初始化時，也會創建postgres數據庫。這個數據庫用於做為用戶和應用連接的默認數據庫。它隻是 template1的一個拷貝，需要時可以刪除並重建。
PostgreSQL服務器提供了大量的運行時配置變量。你可以為其中的許多設置數據庫相關的默認值。
例如，如果由於某種原因，你想禁用指定數據庫上的GEQO優化器，正常情況下你不得不對 所有數據庫禁用它，或者確保每個連接的客戶端小心地發出了SET geqo TO off。要令這個設置在一個特定數據庫中成為默認值，你可以執行下麵的命令：
ALTER DATABASE mydb SET geqo TO off;
這樣將保存該設置（但不是立即設置它）。在後續建立的到該數據庫的連接中它將表現得像在會話開始後馬上調用SET geqo TO off;。注意用戶仍然可以在該會話中更改這個設置，它隻是默認值。要撤消這樣的設置，使用ALTER DATABASE dbname RESET varname。
數據庫用DROP DATABASE命令刪除：
DROP DATABASE name;
隻有數據庫的擁有者或者超級用戶才可以刪除數據庫。刪除數據庫會移除其中包括的所有對象。數據庫的刪除不能被撤銷。
你不能在與目標數據庫連接時執行DROP DATABASE命令。不過，你可以連接到任何其它數據庫，包括 template1數據庫。template1也是你刪除一個給定集簇中最後一個用戶數據庫的唯一選項。
為了方便，有一個在 shell 程序可以刪除數據庫，dropdb：
dropdb dbname
（和createdb不同，刪除當前用戶名的數據庫不是默認動作）。
PostgreSQL中的表空間允許數據庫管理員在文件係統中定義用來存放表示數據庫對象的文件的位置。一旦被創建，表空間就可以在創建數據庫對象時通過名稱引用。
通過使用表空間，管理員可以控製一個PostgreSQL安裝的磁盤布局。 這麼做至少有兩個用處。首先，如果初始化集簇所在的分區或者卷用光了空間，而又不能在邏輯上擴展或者做別的什麼操作，那麼表空間可以被創建在一個不同的分區上，直到係統可以被重新配置。
其次，表空間允許管理員根據數據庫對象的使用模式來優化性能。例如，一個很頻繁使用的索引可以被放在非常快並且非常可靠的磁盤上，如一種非常貴的固態設備。同時，一個很少使用的或者對性能要求不高的存儲歸檔數據的表可以存儲在一個便宜但比較慢的磁盤係統上。
Warning
即便是位於主要的 PostgreSQL 數據目錄之外，表空間也是數據庫集簇的一部分 並且不能被視作數據文件的一個自治集合。 它們依賴於包含在主數據目錄中的元數據，並且因此不能被附加到一個 不同的數據庫集簇或者單獨備份。類似地，如果丟失一個表空間（文件刪除、磁盤失效等）， 數據庫集簇可能會變成不可讀或者無法啟動。把一個表空間放在一個臨時文件係統 （如一個內存虛擬盤）上會帶來整個集簇的可靠性風險。
要定義一個表空間，使用CREATE TABLESPACE命令，例如：
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
這個位置必須是一個已有的空目錄，並且屬於PostgreSQL操作係統用戶。 所有後續在該表空間中創建的對象都將被存放在這個目錄下的文件中。該位置不能放在可移動 或者瞬時存儲上，因為如果表空間丟失會導致集簇無法工作。
Note:
通常在每個邏輯文件係統上創建多於一個表空間沒有什麼意義，因為你無法空值在一個邏輯文件係統中個體文件的位置。不過，PostgreSQL不強製任何這樣的限製，並且事實上它不會注意你的係統上的文件係統邊界。它隻是在你告訴它要使用的目錄中存儲文件。
表空間的創建本身必須作為一個數據庫超級用戶完成，但在創建完之後之後你可以允許普通數據庫用戶來使用它。要這樣做，給數據庫普通用戶授予表空間上的CREATE權限。
表、索引和整個數據庫都可以被分配到特定的表空間。想這麼做，在給定表空間上有 CREATE權限的用戶必須把表空間的名字以一個參數的形式傳遞給相關的命令。例如，下麵的命令在表空間space1中創建一個表：
CREATE TABLE foo(i int) TABLESPACE space1;
另外，還可以使用default_tablespace參數：
SET default_tablespace = space1;
CREATE TABLE foo(i int);
當default_tablespace被設置為非空字符串，那麼它就為沒有顯式TABLESPACE子句的CREATE TABLE和CREATE INDEX命令提供一個隱式TABLESPACE子句。
還有一個temp_tablespaces參數，它決定臨時表和索引的位置，以及用於大數據集排序等目的的臨時文件的位置。 這可以是一個表空間名的列表，而不是隻有一個。因此，與臨時對象有關的負載可以散布在多個表空間上。每次要創建一個臨時對象時，將從列表中隨機取一個成員來存放它。
與一個數據庫相關聯的表空間用來存儲該數據庫的係統目錄。此外，如果沒有給出TABLESPACE子句並且沒有在default_tablespace或temp_tablespaces（如適用）中指定其他選擇，它還是在該數據庫中創建的表、索引和臨時文件的默認表空間。如果一個數據庫被創建時沒有指定表空間，它會使用其模板數據庫相同的表空間。
當初始化數據庫集簇時，會自動創建兩個表空間。pg_global表空間被用於共享係統目錄。pg_default表空間是template1和template0數據庫的默認表空間（並且，因此也將是所有其他數據庫的默認表空間，除非被一個CREATE DATABASE中的TABLESPACE子句覆蓋）。
表空間一旦被創建，就可以被任何數據庫使用，前提是請求的用戶具有足夠的權限。這也意味著，一個表空間隻有在所有使用它的數據庫中所有對象都被刪除掉之後才可以被刪掉。
要刪除一個空的表空間，使用DROP TABLESPACE命令。
要確定現有表空間的集合，可檢查pg_tablespace 係統目錄，例如
SELECT spcname FROM pg_tablespace;
psql程序的\db元命令也可以用來列出現有的表空間。
PostgreSQL使用符號連接來簡化表空間的實現。這就意味著表空間隻能在支持符號連接的係統上使用。
$PGDATA/pg_tblspc目錄包含指向集簇中定義的每個非內建表空間的符號連接。 盡管我們不推薦，但還是可以通過手工重定義這些連接來調整表空間布局。在服務器運行時，絕不要這樣做。注意在 PostgreSQL 9.1 及更早的版本中，你將還需要用新位置更新pg_tablespace目錄（如果你不更新，pg_dump將繼續輸出舊的表空間位置）。
最後更新：2017-08-21 17:32:41