postgres的邏輯備份還原：pg_dump和pg_restore的使用

pg_dump


##1、
-h host 或 --host=host 
指定伺服器主機名。也可以使用UNIX套接字路徑，如果配置了$PGHOST則從此環境變數獲取


##2、
-p port 或 --port=port
指定埠或者本地的UNIX套接字檔案的擴充套件，對應環境變數￥PGPORT


##3、
-U username 或者 --username=username
指定使用者名稱


##4、
-w 或者--no-password
不提示密碼，如果無法登陸命令會失敗，該選項用於後臺指令碼


##5、
-W 或者 --password
強制連線到資料庫之前提示輸入密碼（如果伺服器請求密碼身份認證，pg_dump會自動提示密碼輸入，
不過如果不指定-W的時候，pg_dump會先去嘗試連線伺服器，使用-W避免了額外連結嘗試）


##6、
--role=rolename
連結到資料庫的時候發出一個 set ROLE rolename命令，相當於切換到另一個角色，當已驗證的使用者
缺少pg_dump需要的許可權的時候，可以使用這個功能切換到一個有許可權的角色


##7、
dbname
連結的資料庫名，也是要備份的資料庫名，對應$PGDATABASE


##8、
-a 或 --data-only
只輸出資料，不輸出資料定義的sql語句。純文字格式的時候有效


##9、
-b 或 --blobs
轉儲中是否包含大物件，除非指定了選擇性轉儲的選項 --schema，--table，--schema-only開關，
否則預設會轉儲大物件。用於選擇性轉儲時控制是否轉儲大物件


##10、
-c 或 --clean
輸出的指令碼中是否生成清理該資料庫物件語句（如drop table）。純文字格式時候有意義


##11、
-C 或 --create
指定輸出指令碼是否輸出一條create database 語句和連結到該資料庫的語句，一般在備份的源資料庫
和恢復的模板資料庫的名稱一致時使用這個引數
只針對純文字有意義


##12、
-E encoding 或 --encoding=encoding
指定字符集編碼建立轉儲，預設轉儲時依據資料庫編碼建立的，對應環境變數$PGCLIENTENCOGING


##13、
-f file 或 --file=file
指定資料的檔案，否則輸出到標準輸出


##14、
-F format 或 --format=format
format=p|c|t
p:plain,純文字模式，p是預設值
c：custom，用適合pg_restore使用的自定義格式輸出並歸檔。比較靈活，允許手動查詢，並且在恢復的
           時候重排歸檔項的順序，預設使用壓縮
t：tar，用適合pg_restore的tar格式輸出並歸檔。同樣允許手動選擇和重排歸檔項的順序，但是重排後
        一些限制。比如：表資料項的相關順序在恢復時不能更改，同時tar格式不支援壓縮，並且對獨立
       表空間大小限制為8GB


##15、
-n schema 或 --schema=schema
轉儲匹配的schema的模式內容，包括模式本身以及其中包含的物件。如果沒有不使用該選項，所有目標庫的非
系統模式都會被轉儲。 可以使用個多-n指定多個schema。同樣schema引數將按psql的\d命令被解釋為匹配
模式，因此可以使用萬用字元匹配多個模式。 使用萬用字元的時候，最好使用引號進行界定，防止shell將萬用字元
進行擴充套件
（指定了-n，pg_dump將不會轉儲模式所依賴的其它資料庫物件，因此無法保證轉儲出來的內容能夠在別的資料庫
恢復成功。非模式物件（比如大物件），不會再指定-n的時候被轉儲出來，可以使用-b來明確要求轉儲大物件）


##16、
-N schema 或 --exclude-schema=schema
意義跟-n相反，用法雷同
同時指定，只轉儲匹配-n而不匹配-N的模式


##17、
-o 或 --oids
是否為每個表都輸出物件標示（OID）。當應用需要OID欄位（比如用於外來鍵約束）時，可以使用-o，否則不應該
使用


##18、
-O 或 --no-owner
針對純文字轉儲。 表示不把物件的所有權設定為對應源資料庫中的owner。
pg_dump預設發出alter owner 或 set session authorization語句來設定建立資料庫物件的所有者。如果這些指令碼
將來沒有被超級使用者執行，會導致恢復失敗，-O為了讓該指令碼可以被任何使用者使用


##19、
-s 或 --shcema-only
只輸出物件定義模式，不輸出資料。


##20、
-S username 或--superuser=username
指定關閉觸發器時需要用到的超級使用者名稱。它只在使用了--disable-triggers時才有用處。
一般最好不要使用-S，而是用超級使用者啟動生成的指令碼


##21、
-t table 或 --table=table
指定匯出的表，檢視，序列。可以使用多個-t匹配多個表，使用了-t之後，-n和-N就失效了，被-t選中的表無視-n/N
被匯出，而除了該表之外的所有物件都不會被轉儲
（-t只匯出該表。而依賴不會被匯出，可能導致不能正確恢復）


##22、
-v 或 --verbose
執行過程列印詳細資訊，包括物件評註，轉儲檔案的啟停時間和進度資訊（輸出到標準錯誤輸出）


##23、
-V 或 --version
版本資訊


##24、
-x 或者 --no-privileges 或 --no-acl
禁止轉儲訪問許可權（grant/revoke）


##25、
-Z 0..9 或 --compress=0..9
壓縮級別，0表示不壓縮。對於自動以歸檔格式，指定壓縮的單個表資料段，預設使用中等壓縮水平。 對於純文字，設定非
0值，會導致全部輸出檔案被壓縮。 模式是不壓縮。 tar格式不支援壓縮


##26、
--binary-upgrade


##27、
--inserts
把預設的copy轉儲方式改成insert，會導致恢復起來比較慢，可以用來把資料載入到非pg庫。它為每一行生成一個insert命令
。當恢復遇到錯誤時，僅丟一條資料，要求表的列順序完全一致


##28、
--column-inserts 或 --attribute-inserts
同--inserts，不過它可以支援列順序的不同的匯入匯出


##29、disable-dollar-quoting
關閉使用美元符界定函式體。強制函式體內容使用SQL標準的字串語法的引號包圍


##30、
--disable-triggers
針對純文字有意義，和建立只有資料的轉儲有關。指定在做恢復的時候，臨時關閉目標表上的觸發器命令。如果在表上
有參照完整性檢查或者觸發器，並且恢復資料的時候不想過載他們，指定該選項
    發出該選項的必須是超級使用者，執行轉儲的指令碼時，應該使用-S執行一個超級使用者的命令


##31、
--lock-wait-timeout=timeout
不要永遠等待在開始轉儲時獲取共享表鎖。如果不指定，超過timeout時間，轉儲就會失敗


##32、
--no-tablespaces
只針對純文字格式，表示不輸出命令來選擇表空間。在該選項內，轉儲期間若表空間是預設的，將會建立所有的物件


##33、
--use-set-session-authorization
輸出符合SQL標準的set session authorization命令而不是alter owner命令。這樣轉儲更加符合標準，但是如果轉儲檔案中物件
的歷史問題，可能導致不能正常恢復。並且必須有超級使用者許可權再能使用該引數。 alter owner需要的許可權更小。




##############################################################################


pg_restore:


##1、
基本項跟pg_dump相同：
-h  host  或 --host=host
-p  port 或 --port=port
-U username 或 --username=username
-w 或 --no-password
-W 或 --password
--role=rolename
不同點：
-d dbname 或 --dbname=dbname 指定資料庫
pg_dump預設最後一個沒有任何修飾的引數為要連結的目標庫
pg_restore預設最後一個不帶任何修飾的引數是轉儲檔名


##2、
filename
要恢復的檔案位置，沒有宣告則使用標準輸入


##3、
-a 或 --data-only
只恢復資料，而不恢復表模式


##4、
-c 或 --clean
建立資料庫物件前先清理（刪除）他們


##5、
-C  或 --create
恢復前先建立，如果使用-C和 -d在一起的資料庫名只是用於發出最初的create database命令，
所有的資料都恢復到名字出現在歸檔中的資料庫。


##6、
-d dbname 或 --dbname=dbname
指定資料庫


##7、
-e 或 --exit-on-error
如果再向資料庫發生sql命令時遇到錯誤，則退出。預設是繼續執行，在結束時顯示錯誤數。


##8、
-f filename 或 --file=filename
指定生成的指令碼的輸出檔案，或者出現-l選項時用於列表的檔案，預設是標準輸出


##9、
-F format 或 --format=format
指定備份檔案的格式。pg_restore可以自動判斷，可選t|c


##10、
-I index 或 --index=index
只恢復命名的索引


##11、
-j number-of-jobs 或 --jobs=number-of-jobs
在執行載入資料，建立索引或者約束的時候，可以使用多個併發來完成，可以縮短恢復時間
只有自定義格式才支援該選項，不能跟--single-transaction選項一起使用


##12、
-l 或 --list
列出歸檔內容，該操作的輸出可以用作輸入的-L選項。注意如果過濾選項（-n -t）與-l一起
他們將顯示列出的項


##13、
-L list-file 或 --use-list=list-file
僅恢復那些在list-file中列出的歸檔元素，按照他們出現的順序恢復
可以先執行 pg_restore -l命令然後編輯結果作為-L的輸入檔案


##14、
-n namespace 或 --schema=schema
只恢復指定的模式的資料或者定義，跟-t一起使用只恢復單表


#15、
-O 或--on-owner
不輸出設定和最初資料庫物件許可權匹配的命令。預設情況下，pg_restore發出alter owner
或者set session authoriazaion語句，來設定建立出來的模式元素的所有者許可權。如果
最初的資料庫連線不是超級使用者，那麼這些語句會失敗。 使用-O，任何使用者都可以，並且
這個使用者將擁有建立出來的所有物件


##16、
--no_tablespaces
表示不輸出命令來選擇表空間，若使用了該項，恢復資料時，所有物件被建立到預設表空間中，
而不是原來的表空間


##17、
-P function-name（argtype[,...]） 或 --function=function-name（argtype[,...]）
恢復指定的命名函式，注意拼寫，應該跟備份的內容列表完全一致


##18、
-s 或 --schema-only
只恢復表結構（資料定義），不恢復資料，序列的當前值也不會得到恢復


##19、
-S username 或 --superuser=username
設定關閉觸發器時宣告的超級使用者名稱，只有跟--disable-triggers配合才有作用


##20、
-t table 或 --table=table
指定恢復的表


##21、
-T trigger 或 --trigger=trigger
指定恢復的觸發器


##22、
-v 或者 -verbose
詳細輸出模式


##23、
-V 或 --version
輸出版本號


##24、
-x 或 --no-privileges 或 --no-acl
禁止恢復訪問許可權（grant/revoke）


##25、
--disable-triggers
僅僅在恢復資料的時候有用，指定在載入資料時臨時關閉觸發器（完整性檢查約束或者觸發器）


##26、
--use-set-session-authorization
輸出SQL標準的set session authorization 命令，而不是alter owner命令
可以是轉儲和標準相容的更好，但是可能導致轉儲不能正確恢復


##27、
--no-data-for-failed-tables
預設情況下，建立表的命令失敗，表中的資料仍然會被恢復，使用該選項錯誤的表的資料將跳過恢復操作，
該選項僅僅在直接向一個資料庫恢復資料時有效，sql指令碼輸入無效


##28、
-l 或 --single-transaction
指定作為一個單獨事務來儲存（使用begin/commit封裝命令）確保要麼所有的都成功，要麼都失敗
它蘊含了 --exit-on-error


################################################################
最佳實踐：


1、資料庫匯出匯入


1）匯出資料庫文字檔案，不帶create db語句。可以匯入任何庫
pg_dump -h 127.0.0.1 -p 7002  test_dump > 2.sql
pg_dump -h 127.0.0.1 -p 7002 test_dump -f 2.sql
恢復到hehe中
psql -h 127.0.0.1 -p 7002 -d hehe < 2.sql 


2）匯出資料庫文字檔案，帶create db語句。
pg_dump -h 127.0.0.1 -p 7002 -C test_dump > 3.sql
恢復到同樣名字的庫中：
psql -h 127.0.0.1 -p 7002  < 3.sql 


3）匯出自定義格式：
pg_dump -h 127.0.0.1 -p 7002 -Fc  test_dump > 5.sql
匯入hehe庫
pg_restore  -h 127.0.0.1 -p 7002 --no-data-for-failed-tables  -d hehe  5.sql 




2、表的匯入匯出：
1）文字匯出匯入
pg_dump -h 127.0.0.1 -p 7002  -t t   test > 8.sql
恢復：
psql -h 127.0.0.1 -p 7002   monitor <8.sql 
2）自定義格式：
pg_dump -h 127.0.0.1 -p 7002 -Fc  -t t   test > 1.sql
匯入到test：
pg_restore  -h 127.0.0.1 -p 7002 --no-data-for-failed-tables  -d test  1.sql 
匯入到monitor：
pg_restore  -h 127.0.0.1 -p 7002 --no-data-for-failed-tables  -d monitor  1.sql 
3)多表萬用字元匯出：
pg_dump -h 127.0.0.1 -p 7002 -Fc  -t 't*'   test > 1.sql
pg_restore  -h 127.0.0.1 -p 7002 -d test2 1.sql 


3 儲存過程，函式，觸發器的匯入匯出
1)匯出表以外的資料庫物件
pg_dump -h 127.0.0.1 -p 7002 -T '*'  test2 > 4.sql

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20625855/viewspace-1845992/，如需轉載，請註明出處，否則將追究法律責任。