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

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