PostgreSQL教程(十八)：用戶端命令（2）_PostgreSQL

七、pg_dump:

    pg_dump是一個用於備份PostgreSQL資料庫的工具。它甚至可以在資料庫正在並發使用時進行完整一致的備份，而不會阻塞其它使用者對資料庫的訪問。該工具產生的轉儲格式可以分為兩種，指令碼和歸檔檔案。其中指令碼格式是包含許多SQL命令的純文字格式，這些SQL命令可以用於重建該資料庫並將之恢複到產生此指令碼時的狀態，該操作需要使用psql來完成。至于歸檔格式，如果需要重建資料庫就必須和pg_restore工具一起使用。在重建過程中，可以對恢複的對象進行選擇，甚至可以在恢複之前對需要恢複的條目進行重新排序。該命令的使用方式如下：
 

複製代碼 代碼如下:

    pg_dump [option...] [dbname]
 

    1. 命令列選項列表：

選項	說明
-a(--data-only)	只輸出資料，不輸出模式(資料對象的定義)。這個選項只是對純文字格式有意義。對于歸檔格式，你可以在調用pg_restore時指定選項。
-b(--blobs)	在dump中包含大對象。
-c(--clean)	在輸出建立資料庫物件的SQL命令之前，先輸出刪除該資料庫物件的SQL命令。這個選項只是對純文字格式有意義。對于歸檔格式，你可以在調用 pg_restore時指定選項。
-C(--create)	先輸出建立資料庫的命令，之後再重新串連新建立的資料庫。對於此種格式的指令碼，在運行之前是和哪個資料庫進行串連就不這麼重要了。這個選項只是對純文字格式有意義。對于歸檔格式，你可以在調用pg_restore時指定選項。
-Eencoding	以指定的字元集建立該dump檔案。
-ffile	輸出到指定檔案，如果沒有該選項，則輸出到標準輸出。
-Fformat	p(plain): 純文字格式的SQL指令檔(預設)。c(custom): 輸出適合於pg_restore的自訂歸檔格式。 這是最靈活的格式，它允許對裝載的資料和對象定義進行重新排列。這個格式預設的時候是壓縮的。t(tar): 輸出適合於pg_restore的tar歸檔檔案。使用這個歸檔允許在恢複資料庫時重新排序和/或把資料庫物件排除在外。同i時也可能可以在恢複的時候限制對哪些資料進行恢複。
-n schema	只轉儲schema的內容。如果沒有聲明該選項，目標資料庫中的所有非系統模式都會被轉儲。該選項也可以被多次指定，以指定不同pattern的模式。
-Nschema	不轉儲匹配schema的內容，其他規則和-n一致。
-o(--oids)	作為資料的一部分，為每個表都輸出對象標識(OID)。
-O(--no-owner)	不輸出設定對象所有權的SQL命令。
-s(--schema-only)	只輸出對象定義(模式)，不輸出資料。
-Susername	指定關閉觸發器時需要用到的超級使用者名稱。它只有在使用--disable-triggers的時候才有關係。
-ttable	只輸出表的資料。很可能在不同模式裡面有多個同名表，如果這樣，那麼所有匹配的表都將被轉儲。通過多次指定該參數，可以一次轉儲多張表。這裡還可以指定和psql一樣的pattern，以便匹配更多的表。(關於pattern，基本的使用方式是可以將它視為unix的萬用字元，即*表示任一字元，?表示任意單個字元，.(dot)表示schema和object之間的分隔字元，如a*.b*，表示以a開頭的schema和以b開頭的資料庫物件。如果沒有.(dot)，將只是表示資料庫物件。這裡也可以使用基本的Regex，如[0-9]表示數字。)
-Ttable	排除指定的表，其他規則和-t選項一致。
-x(--no-privileges)	不匯出存取權限資訊(grant/revoke命令)。
-Z0..9	聲明在那些支援壓縮的格式中使用的壓縮層級。 (目前只有自訂格式支援壓縮)
--column-inserts	匯出資料用insert into table_name(columns_list) values(values_list)命令表示，這樣的操作相對其它操作而言是比較慢的，但是在特殊情況下，如資料表欄位的位置有可能發生變化或有新的欄位插入到原有欄位列表的中間等。由於columns_list被明確指定，因此在匯入時不會出現資料被匯入到錯誤欄位的問題。
--inserts	匯出的資料用insert命令表示，而不是copy命令。即便使用insert要比copy慢一些，但是對於今後匯入到其他非PostgreSQL的資料庫是比較有意義的。
--no-tablespaces	不輸出設定資料表空間的命令，如果帶有這個選項，所有的對象都將恢複到執行pg_restore時的預設資料表空間中。
--no-unlogged-table-data	對於不計入日誌(unlogged)的資料表，不會匯出它的資料，至於是否匯出其Schema資訊，需要依賴其他的選項而定。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的偵聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱，如果-O選項沒有指定，此資料庫的Owner將為該登入使用者。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

2. 應用樣本：

複製代碼 代碼如下:

    # -h: PostgreSQL伺服器的主機為192.168.149.137。
    # -U: 登入使用者為postgres。
    # -t: 匯出表名以test開頭的資料表，如testtable。
    # -a: 僅僅匯出資料，不匯出對象的schema資訊。
    # -f: 輸出檔案是目前的目錄下的my_dump.sql
    # mydatabase是此次操作的目標資料庫。
    /> pg_dump -h 192.168.149.137 -U postgres -t test* -a -f ./my_dump.sql mydatabase
    #-c: 先輸出刪除資料庫物件的SQL命令，在輸出建立資料庫物件的SQL命令，這對於部署乾淨的初始系統或是搭建測試環境都非常方便。
    /> pg_dump -h 192.168.220.136 -U postgres -c -f ./my_dump.sql mydatabase
    #匯出mydatabase資料庫的資訊。在通過psql命令匯入時可以重新指定資料庫，如：/> psql -d newdb -f my_dump.sql
    /> pg_dump -h 192.168.220.136 -U postgres -f ./my_dump.sql mydatabase
    #匯出模式為my_schema和以test開頭的資料庫物件名，但是不包括my_schema.employee_log對象。
    /> pg_dump -t 'my_schema.test*' -T my_schema.employee_log mydatabase > my_dump.sql
    #匯出east和west模式下的所有資料庫物件。下面兩個命令是等同的，只是後者使用了正則。
    /> pg_dump -n 'east' -n 'west' mydatabase -f my_dump.sql
    /> pg_dump -n '(east|west)' mydatabase -f my_dump.sql

八、pg_restore:

    pg_restore用於恢複pg_dump匯出的任何非純文字格式的檔案，它將資料庫重建成儲存它時的狀態。對于歸檔格式的檔案，pg_restore可以進行有選擇的恢複，甚至也可以在恢複前重新排列資料的順序。

    pg_restore可以在兩種模式下操作。如果指定資料庫，歸檔將直接恢複到該資料庫。否則，必須先手工建立資料庫，之後再通過pg_restore恢複資料到該建立的資料庫中。該命令的使用方式如下：
 

複製代碼 代碼如下:

    pg_restore [option...] [filename]
 

    1. 命令列選項列表：

 

選項	說明
filename	指定要恢複的備份檔案，如果沒有聲明，則使用標準輸入。
-a(--data-only)	只恢複資料，而不恢複表模式(資料對象定義)。
-c(--clean)	建立資料庫物件前先清理(刪除)它們。
-C(--create)	在恢複資料庫之前先建立它。(在使用該選項時，資料庫名需要由-d選項指定，該選項只是執行最基本的CREATE DATABASE命令。需要說明的是，歸檔檔案中所有的資料都將恢複到歸檔檔案裡指定的資料庫中)。
-ddbname	與資料庫dbname建立串連並且直接恢複資料到該資料庫中。
-e(--exit-on-error)	如果在向資料庫發送SQL命令的時候遇到錯誤，則退出。預設是繼續執行並且在恢複結束時顯示一個錯誤計數。
-Fformat	指定備份檔案的格式。由於pg_restore會自動判斷格式，因此指定格式並不是必須的。如果指定，它可以是以下格式之一：t(tar): 使用該格式允許在恢複資料庫時重新排序和/或把表模式資訊排除出去，同時還可能在恢複時限制裝載的資料。 c(custom):該格式是來自pg_dump的自訂格式。這是最靈活的格式，因為它允許重新對資料排序，也允許重載表模式資訊，預設情況下這個格式是壓縮的。
-I index	只恢複指定的索引。
-l(--list)	列出備份中的內容，這個操作的輸出可以作為-L選項的輸入。注意，如果過濾選項-n或-t連同-l選項一起使用的話，他們也將限制列出的條目。
-L list-file	僅恢複在list-file中列出的條目，恢複的順序為各個條目在該檔案中出現的順序，你也可以手工編輯該檔案，並重新排列這些條目的位置，之後再進行恢複操作，其中以分號(;)開頭的行為注釋行，注釋行不會被匯入。
-n namespace	僅恢複指定模式(Schema)的資料庫物件。該選項可以和-t選項聯合使用，以恢複指定的資料對象。
-O(--no-owner)	不輸出設定對象所有權的SQL命令。
-Pfunction-name(argtype [, ...])	只恢複指定的命名函數。該名稱應該和轉儲的內容列表中的完全一致。
-s(--schema-only)	只恢複表結構(資料定義)。不恢複資料，序列值將重設。
-Susername	指定關閉觸發器時需要用到的超級使用者名稱。它只有在使用--disable-triggers的時候才有關係。
-t table	只恢複指定表的Schema和/或資料，該選項也可以連同-n選項指定模式。
-x(--no-privileges)	不恢復權限資訊(grant/revoke命令)。
-1(--single-transaction)	在一個單一事物中執行恢複命令。這個選項隱含包括了--exit-on-error選項。
--no-tablespaces	不輸出設定資料表空間的命令，如果帶有這個選項，所有的對象都將恢複到執行pg_restore時的預設資料表空間中。
--no-data-for-failed-tables	預設情況下，即使建立表失敗了，如該表已經存在，資料載入的操作也不會停止，這樣的結果就是很容易導致大量的重複資料被插入到該表中。如果帶有該選項，那麼一旦出現針對該表的任何錯誤，對該資料表的載入將被忽略。
--role=rolename	以指定的角色名稱執行restore的操作。通常而言，如果串連角色沒有足夠的許可權用於本次恢複操作，那麼就可以利用該選項在建立串連之後再切換到有足夠許可權的角色。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的偵聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱，如果-O選項沒有指定，此資料庫的Owner將為該登入使用者。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

 2. 應用樣本： 
 

複製代碼 代碼如下:

    #先通過createdb命令，以myuser使用者的身份登入，建立帶恢複的資料newdb
    /> createdb -U myuser newdb
    #用pg_restore命令的-l選項匯出my_dump.dat備份檔案中匯出資料庫物件的明細列表。
    /> pg_restore -l my_dump.dat > db.list
    /> cat db.list
    2; 145344 TABLE species postgres
    4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    8; 145416 TABLE ss_old postgres
    10; 145433 TABLE map_resolutions postgres
    #將以上列表檔案中的內容修改為以下形式。
    #主要的修改是注釋掉編號為2、4和8的三個資料庫物件，同時編號10的對象放到該檔案的頭部，這樣在基於該列表
    #檔案匯入時，2、4和8等三個對象將不會被匯入，在恢複的過程中將先匯入編號為10的對象的資料，再匯入對象6的資料。
    /> cat new_db.list
    10; 145433 TABLE map_resolutions postgres
    ;2; 145344 TABLE species postgres
    ;4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    ;8; 145416 TABLE ss_old postgres   
    #恢複時指定的資料庫是newdb，匯入哪些資料庫物件和匯入順序將會按照new_db.list檔案中提示的規則匯入。
    /> pg_restore -d newdb -L new_db.list my_dump.dat

九、psql:

    PostgreSQL的互動終端，等同於Oracle中的sqlplus。
    1. 常用命令列選項列表：

 

選項	說明
-c command	指定psql執行一條SQL命令command(用雙引號括起)，執行後退出。
-d dbname	待串連的資料庫名稱。
-E	回顯由\d和其他反斜線命令產生的實際查詢。
-f filename	使用filename檔案中的資料作為命令輸入源，而不是互動式讀入查詢。在處理完檔案後，psql結束並退出。
-h hostname	聲明正在運行伺服器的主機名稱
-l	列出所有可用的資料庫，然後退出。
-L filename	除了正常的輸出源之外，把所有查詢記錄輸出到檔案filename。
-o filename	將所有查詢重新導向輸出到檔案filename。
-p port	指定PostgreSQL伺服器的監聽連接埠。
-q --quiet	讓psql安靜地執行所處理的任務。預設時psql將輸出列印歡迎和許多其他資訊。
-t --tuples-only	關閉列印列名稱和結果行計數腳註等資訊。
-U username	以使用者username代替預設使用者與資料庫建立串連。

 2. 應用樣本：  
 

複製代碼 代碼如下:

    #先通過createdb命令，以myuser使用者的身份登入，建立帶恢複的資料newdb
    /> createdb -U myuser newdb
    #用pg_restore命令的-l選項匯出my_dump.dat備份檔案中匯出資料庫物件的明細列表。
    /> pg_restore -l my_dump.dat > db.list
    /> cat db.list
    2; 145344 TABLE species postgres
    4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    8; 145416 TABLE ss_old postgres
    10; 145433 TABLE map_resolutions postgres
    #將以上列表檔案中的內容修改為以下形式。
    #主要的修改是注釋掉編號為2、4和8的三個資料庫物件，同時編號10的對象放到該檔案的頭部，這樣在基於該列表
    #檔案匯入時，2、4和8等三個對象將不會被匯入，在恢複的過程中將先匯入編號為10的對象的資料，再匯入對象6的資料。
    /> cat new_db.list
    10; 145433 TABLE map_resolutions postgres
    ;2; 145344 TABLE species postgres
    ;4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    ;8; 145416 TABLE ss_old postgres   
    #恢複時指定的資料庫是newdb，匯入哪些資料庫物件和匯入順序將會按照new_db.list檔案中提示的規則匯入。
    /> pg_restore -d newdb -L new_db.list my_dump.dat

    3. 內建命令列表：
    psql內建命令的格式為反斜線後面緊跟一個命令動詞，之後是任意參數。參數與命令動詞以及其他參數之間可以用空白符隔開，如果參數裡麵包含空白符，該參數必須用單引號括起，如果參數內包含單引號，則需要用反斜線進行轉義，此外單引號內的參數還支援類似C語言printf函數所支援的轉義關鍵字，如\t、\n等。

命令	說明
\a	如果目前的表輸出格式是不對齊的，切換成對齊的。如果是對齊的，則切換成不對齊。
\cd [directory]	把當前工作目錄切換到directory。沒有參數則切換到目前使用者的主目錄。
\C [title]	為查詢結果添加表頭(title)，如果沒有參數則取消當前的表頭。
\c[dbname[username] ]	串連新的資料庫，同時斷開當前串連。如果dbname參數為-，表示仍然串連當前資料庫。如果忽略username，則表示繼續使用當前的使用者名稱。
\copy	其參數類似於SQL copy，功能則幾乎等同於SQL copy，一個重要的差別是該內建命令可以將表的內容匯出到本地，或者是從本地匯入到資料庫指定的表，而SQL copy則是將表中的資料匯出到伺服器的某個檔案，或者是從伺服器的檔案匯入到資料表。由此可見，SQL copy的效率要優於該內建命令。
\d [pattern]	顯示和pattern匹配的資料庫物件，如表、視圖、索引或者序列。顯示所有列，它們的類型，資料表空間(如果不是預設的)和任何特殊屬性。
\db [pattern]	列出所有可用的資料表空間。如果聲明了pattern， 那麼只顯示那些匹配模式的資料表空間。
\db+ [pattern]	和上一個命令相比，還會新增顯示每個資料表空間的許可權資訊。
\df [pattern]	列出所有可用函數，以及它們的參數和返回的資料類型。如果聲明了pattern，那麼只顯示匹配(Regex)的函數。
\df+ [pattern]	和上一個命令相比，還會新增顯示每個函數的附加資訊，包括語言和描述。
\distvS [pattern]	這不是一個單獨命令名稱：字母 i、s、t、v、S 分別代表索引(index)、序列(sequence)、表(table)、視圖(view)和系統資料表(system table)。你可以以任意順序聲明部分或者所有這些字母獲得這些對象的一個列表。
\dn [pattern]	列出所有可用模式。如果聲明了pattern，那麼只列出匹配模式的模式名。
\dn+ [pattern]	和上一個命令相比，還會新增顯示每個對象的許可權和注釋。
\dp [pattern]	產生一列可用的表和它們相關的許可權。如果聲明了pattern， 那麼只列出名字可以匹配模式的表。
\dT [pattern]	列出所有資料類型或只顯示那些匹配pattern的。
\du [pattern]	列出所有已配置使用者或者只列出那些匹配pattern的使用者。
\echotext [ ... ]	向標準輸出列印參數，用一個空格分隔並且最後跟著一個新行。如：\echo `date`
\g[{filename ||command}]	把當前的查詢結果緩衝區的內容發送給伺服器並且把查詢的輸出儲存到可選的filename或者把輸出定向到一個獨立的在執行 command的Unix shell。
\ifilename	從檔案filename中讀取並把其內容當作從鍵盤輸入的那樣執行查詢。
\l	列出伺服器上所有資料庫的名字和它們的所有者以及字元集編碼。
\o[{filename ||command}]	把後面的查詢結果儲存到檔案filename裡或者把後面的查詢結果定向到一個獨立的shell command。
\p	列印當前查詢緩衝區到標準輸出。
\q	退出psql程式。
\r	重設(清空)查詢緩衝區。
\s [filename]	將命令列曆史列印出或是存放到filename。如果省略filename，曆史將輸出到標準輸出。
\t	切換是否輸出資料行/欄位名的資訊頭和行記數腳註。
\w{filename ||command}	將當前查詢緩衝區輸出到檔案filename或者定向到Unix命令command。
\z [pattern]	產生一個帶有存取權限列表的資料庫中所有表，視圖和序列的列表。如果給出任何pattern，則被當成一個規則運算式，只顯示匹配的表，視圖和序列。
\! [command]	返回到一個獨立的Unix shell或者執行Unix命令command。參數不會被進一步解釋，shell將看到全部參數。

 4. 內建命令應用樣本：

    在psql中，大部分的內建命令都比較易於理解，因此這裡只是給出幾個我個人認為相對容易混淆的命令。
 

複製代碼 代碼如下:

    # \c: 其中橫線(-)表示仍然串連當前資料庫，myuser是新的使用者名稱。
    postgres=# \c - myuser
    Password for user myuser:
    postgres=> SELECT user;
     current_user
    --------------
     myuser
    (1 row)
    # 執行任意SQL語句。
    postgres=# SELECT * FROM testtable WHERE i = 2;
     i
    ---
     2
    (1 row)
    # \g 命令會將上一個SQL命令的結果輸出到指定檔案。
    postgres=# \g my_file_for_command_g
    postgres=# \! cat my_file_for_command_g
     i
    ---
     2
    (1 row)
    # \g 命令會將上一個SQL命令的結果從管道輸出到指定的Shell命令，如cat。
    postgres=# \g | cat
     i
    ---
     2
    (1 row)
    # \p 列印上一個SQL命令。
    postgres=# \p
    SELECT * FROM testtable WHERE i = 2;
    # \w 將上一個SQL命令輸出到指定的檔案。
    postgres=# \w my_file_for_option_w
    postgres=# \! cat my_file_for_option_w
    SELECT * FROM testtable WHERE i = 2;
    # \o 和\g相反，該命令會將後面psql命令的輸出結果輸出到指定的檔案，直到遇到下一個獨立的\o，
    # 此後的命令結果將不再輸出到該檔案。
    postgres=# \o my_file_for_option_o
    postgres=# SELECT * FROM testtable WHERE i = 1;
    # 終止後面的命令結果也輸出到my_file_for_option_o檔案中。
    postgres=# \o
    postgres=# \! cat my_file_for_option_o
     i
    ---
     1
    (1 row)