postgresql學習筆記（五）備份與恢複

標籤：資料恢複   database   檔案夾   文字檔   登入密碼   

postgresql學習筆記（五）備份與恢複

1、備份工具：pg_dump和pg_dumpall

pg_dump可備份一個指定的database

pg_dumpall可一次性備份所有database的資料及系統全域資料

2、pg_dump和pg_dumpall工具不支援在命令列選項中設定登入密碼，因此為了便於執行自動任務，你需要在postgres作業系統帳號的home檔案夾下建立一個密碼檔案.pgpass來儲存密碼；或者也可以用PGPASSWORD環境變數來設定密碼。

3、如果你希望每天都進行備份，那麼使用pg_dump比pg_dumpall更合適，因為前者支援精確指定要備份的表，schema和database，而後者不支援。

4、pg_dump可以將資料備份為SQL文字檔格式，也支援備份為使用者定義壓縮格式或者是TAR包格式，在資料恢複時，對壓縮格式和TAR包格式的備份檔案可以實現並行恢複，該特性是從8.4版開始支援的。

5、pg_dumpall工具可以將當前postgresql服務執行個體中所有database的資料都匯出為SQL文本（pg_dumpall不支援匯出SQL文本以外的其他格式），也可以同時匯出表間定義和角色等全域對象。

6、pg_dump用法：

(1)備份某個database，備份結果以自定壓縮格式輸出 ：

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -f test.backup test

(2)備份某個database，備份結果以SQL文本方式輸出，輸出結果中需包括CREATE DATABASE語句；

pg_dump -h 127.0.0.1 -p 5432 -U postgres -C -F p -b -v -f test.sql test

(3)備份某個database中所有名稱以“pay”開頭的表，備份結果以自訂壓縮格式輸出：

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -t *.pay* -f pay.backup test

(4)備份某個database中hr和payroll這兩個schema中的所有資料，備份結果以自訂壓縮格式輸出：

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -n hr -n payroll -f hr.backup mydb

(5)備份某個database中除了public schema中的資料以外的所有資料，備份結果以自訂壓縮格式輸出：

pg_dump -h 127.0.0.1 -p 5432  -U postgres -F c -b -v -N public -f all_sch_except_pub.backup mydb

(6)將資料備份為SQL文字檔，且產生的INSERT語句是帶有欄位名列表的標準格式，該檔案可用於將資料匯入到低於目前的版本的PostgreSQL或者其他支援SQL的非PostgreSQL資料庫中。

pg_dump -h 127.0.0.1 -p 5432  -U postgres -F p --column-inserts -f select_tables.backup mydb

(7)目錄格式備份，解決了以其他備份格式時可能存在的單個檔案大小超出作業系統限制的問題

pg_dump -h 127.0.0.1 -p 5432  -U postgres -F d -f /somepath/a_directory mydb

(8)目錄格式並行備份

pg_dump -h 127.0.0.1 -p 5432  -U postgres -j 3 -Fd -f /somepath/a_directory mydb

7、pg_dumpall 建議每天都對角色和資料表空間定義等全域對象進行備份，但不建議每天都使用pg_dumpall來備份全庫資料，因為pg_dumpall僅支援匯出為SQL文字格式設定，而使用這種龐大的SQL文本備份來進行全庫層級

的資料恢複是極其耗時的，所以一般只建議用pg_dumpall來備份全域對象而非全庫資料，如果你一定要用pg_dumpall來備份全庫資料的話，一般一個月執行一次就夠了。

（1）僅備份角色和資料表空間定義

pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

 (2)僅需備份角色定義而無需備份資料表空間，那麼可以加上--roles-only選項

pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --roles-only

8、資料恢複方法

（1）使用psql來恢複pg_dump或者pg_dumpall工具產生的SQL文字格式設定的資料備份；

（2）使用pg_restore工具來恢複由pg_dump工具產生的自訂壓縮格式，TAR包格式或者目錄格式備份

9、使用psql恢複SQL文字格式設定的資料備份

 (1)恢複一個SQL備份檔案並忽略過程中可能發生的所有錯誤

psql -U postgres -f myglobals.sql

(2)恢複一個SQL備份檔案，如遇任何錯誤則立好停止恢複

psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql

(3)將SQL文本中的資料恢複到某個指定的database:

psql -U postgres -d mydb -f select_objects.sql

10、使用pg_restore進行恢複

（1）在使用pg_restore執行恢複動作之前，請建立目標資料庫

create database mydb;

 執行恢複：

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

 (2)如果備份和恢複時使用的database同名，則可以通過加--create選項省去單獨建庫的過程

pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup

 (3)恢複表結構而不恢複表資料

create database mydb2;pg_resotre --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

本文出自 “corasql” 部落格，請務必保留此出處http://corasql.blog.51cto.com/5908329/1920949

postgresql學習筆記（五）備份與恢複