postgres邏輯備份恢複

學習環境：

centos6.5  64+postgres9.5.1

一簡要說明

工具pg_dump(pg_dumpall)，psql, pg_restore。

一個資料庫(或者部分對象)導出為腳本文本檔案，用psql恢複。

一個資料庫(或者部分對象)導出為歸檔檔案，用pg_restore恢複。

參考資料：

http://www.postgresql.org/docs/9.5/static/app-pgdump.html

http://www.runoob.com/manual/PostgreSQL/app-pgdump.html

二文法溫故

2.1 pg_dump --help

Usage:   pg_dump [OPTION]... [DBNAME] General options:   -f, --file=FILENAME         導出檔案路徑   -F, --format=c|d|t|p          導出檔案格式，預設p為文本（如果是定制模式，預設啟用壓縮）   -j, --jobs=NUM                執行并行個數   -v, --verbose                   詳細模式   -V, --version                   輸出版本資訊，然後退出   -Z, --compress=0-9         壓縮級别   --lock-wait-timeout=TIMEOUT   在等待表鎖逾時後操作失敗   -?, --help                    顯示幫助，然後退出 Options controlling the output content:   -a, --data-only              隻導出資料，不輸出模式（資料庫對象定義）   -b, --blobs                  導出時包括大對象   -c, --clean                  建立資料庫對象前先删除   -C, --create                 導出檔案中有建立目前備份資料庫的語句   -E, --encoding=ENCODING      指定字元集編碼建立轉儲   -n, --schema=SCHEMA          導出指定模式（如果沒有指定，那麼非系統模式都會被轉儲）   -N, --exclude-schema=SCHEMA  排除指定模式   -o, --oids                   為每個表都輸出對象辨別（OID）   -O, --no-owner            在明文格式中, 忽略恢複對象所屬者                                 -s, --schema-only         隻導出模式定義（資料庫對象定義），不包括資料   -S, --superuser=NAME  在明文格式中,聲明關閉觸發器時需要用到的超級使用者名   -t, --table=TABLE          指定導出的表（可能不同模式裡面有多個同名表，可以同時指定-n模式）   -T, --exclude-table=TABLE    排除指定的表   -x, --no-privileges          不導出權限   --binary-upgrade            隻能由更新工具使用   --column-inserts            以帶有列名的INSERT指令形式轉儲資料   --disable-dollar-quoting    關閉使用美元符包圍函數體。強制用 SQL 标準的字串文法的引号包圍。   --disable-triggers           在隻恢複資料的過程中禁用觸發器   --enable-row-security        enable row security (dump only content user has                                access to)   --exclude-table-data=TABLE   不導出指定表資料   --if-exists                  删除對象語句帶if-exists指令   --inserts                   以INSERT形式轉儲資料 ，而不是COPY  --no-security-labels          不轉儲安全标簽的配置設定   --no-synchronized-snapshots  在并行工作集中不使用同步快照   --no-tablespaces             不轉儲表空間配置設定資訊   --no-unlogged-table-data    不轉儲不記日志表的資料   --quote-all-identifiers      所有辨別符加引号，即使不是關鍵字   --section=SECTION       隻轉儲命名的章節(資料前, 資料, 及 資料後)   --serializable-deferrable  使用一個可串行化的事務，以保證使用的快照和稍後的資料庫狀态一緻；  --snapshot=SNAPSHOT  使用給定的snapshot   --use-set-session-authorization  使用 SET SESSION AUTHORIZATION指令代替ALTER OWNER 指令來設定所有權 Connection options:   -d, --dbname=DBNAME      資料庫   -h, --host=HOSTNAME       主機   -p, --port=PORT                  端口   -U, --username=NAME       連接配接使用者   -w, --no-password               永遠不提示輸入密碼   -W, --password                  強制密碼提示 (自動)   --role=ROLENAME            在轉儲前運作SET ROLE

2.2 pg_restore  --help

[[email protected] ~]$ pg_restore  --help pg_restore restores a PostgreSQL database from an archive created by pg_dump. Usage:   pg_restore [OPTION]... [FILE] General options:   -d, --dbname=NAME         連接配接的資料庫   -f, --file=FILENAME          輸出檔案名   -F, --format=c|d|t             備份檔案格式（會自動識别）   -l, --list                    列印歸檔檔案的 TOC 概述   -v, --verbose             詳細模式   -V, --version             輸出版本，然後退出   -?, --help                 顯示幫助，然後退出 Options controlling the restore:   -a, --data-only           隻恢複資料，不恢複模式（資料庫對象定義）   -c, --clean                 恢複資料庫對象之前，先删除   -C, --create                恢複資料庫的時候，自定義先建立某個資料庫   -e, --exit-on-error         遇到錯誤退出，預設為繼續   -I, --index=NAME         恢複索引   -j, --jobs=NUM              指定并行作業數   -L, --use-list=FILENAME      指定檔案名恢複   -n, --schema=NAME            恢複指定的使用者模式   -O, --no-owner               不恢複對象的所屬   -P, --function=NAME(args)    恢複指定的函數   -s, --schema-only            隻恢複模式(資料庫對象定義)，不包含資料   -S, --superuser=NAME     使用指定的超級使用者來禁用觸發器   -t, --table=NAME             恢複指定的表   -T, --trigger=NAME           恢複指定的觸發器   -x, --no-privileges              不恢複權限   -1, --single-transaction     作為單個事物   --disable-triggers             在隻恢複資料的過程中禁用觸發器   --enable-row-security        啟用行安全   --if-exists                  删除對象的語句中帶if-exists   --no-data-for-failed-tables 對那些無法建立的表不進行資料恢複   --no-security-labels         不恢複安全标簽資訊   --no-tablespaces             不恢複表空間的配置設定資訊   --section=SECTION         恢複命名的章節(資料前、資料及資料後)   --use-set-session-authorization 使用 SET SESSION AUTHORIZATION指令代替ALTER OWNER 指令來設定所有權。 Connection options:   -h, --host=HOSTNAME        主機   -p, --port=PORT                   端口   -U, --username=NAME        連接配接使用者   -w, --no-password          永遠不提示輸入密碼   -W, --password             強制密碼提示 (自動)   --role=ROLENAME            在轉儲前運作SET ROLE 選項 -I, -n, -P, -t, -T, 以及 --section 可以組合使用和指定 多次用于選擇多個對象。

2.3 psql  --help

[[email protected] ~]$ psql  --help psql is the PostgreSQL interactive terminal. Usage:   psql [OPTION]... [DBNAME [USERNAME]] General options:   -c, --command=COMMAND    聲明 psql 将執行一條查詢字元串，然後退出。   -d, --dbname=DBNAME         指定資料庫名   -f, --file=FILENAME      filename作為指令的語句源而不是互動式讀入查詢，執行檔案路徑與名稱。   -l, --list                        列出可用資料庫名   -v, --set=, --variable=NAME=VALUE設定psql 相關變量值 (e.g., -v ON_ERROR_STOP=1)   -V, --version                      輸出版本資訊   -X, --no-psqlrc                   不讀取啟動檔案（系統的 psqlrc 或者~/.psqlrc 都不讀取）。   -1 ("one"), --single-transaction   以單個事物運作(if non-interactive)   -?, --help[=options]        show this help, then exit       --help=commands         list backslash commands, then exit       --help=variables        list special variables, then exit Input and output options:   -a, --echo-all            标準輸出列印列印從腳本的所有輸入   -b, --echo-errors        列印失敗的指令   -e, --echo-queries       把所有發送給伺服器的查詢同時也回顯到标準輸出。   -E, --echo-hidden        回顯由 \d 和其它反斜杠指令生成的實際查詢。  -L, --log-file=FILENAME    将會話日志發送到檔案   -n, --no-readline         取消指令行編輯   -o, --output=FILENAME     查詢輸出重定向到檔案filename，等效于\o指令，(or |pipe)   -q, --quiet               安靜地執行處理任務。(無其他資訊, 隻有結果)   -s, --single-step        單步模式運作。每個查詢在發往伺服器之前都要提示使用者。   -S, --single-line         單行運作模式，這時每個指令都将由換行符結束，像分号那樣。 Output format options:   -A, --no-align            切換為非對齊輸出模式，預設輸出模式是對齊的。   -F, --field-separator=STRING 使用 separator 作為未對齊輸出的域分隔符，(default: "|")   -H, --html                 HTML 格式輸出。等效于 \pset format html 或 \H 指令。   -P, --pset=VAR[=ARG]       設定列印選項。 (see \pset command)   -R, --record-separator=STRING 設定記錄行分隔符，預設為每一行(default: newline)   -t, --tuples-only          隻列印行，等效于 \t 指令。   -T, --table-attr=TEXT     設定HTML屬性，聲明放在HTMLtable标簽裡的選項。(e.g., width, border)   -x, --expanded             打開擴充表格式模式。等效于 \x 指令。   -z, --field-separator-zero  将區域分割設定為0   -0, --record-separator-zero 将記錄分割設定為0  Connection options:   -h, --host=HOSTNAME        主機名 (default: "local socket")   -p, --port=PORT                   端口 (default: "5432")   -U, --username=USERNAME    使用者 (default: "postgres")   -w, --no-password           從不提示密碼   -W, --password              強制提示密碼 (should happen automatically)

三示例

3.1備份與恢複整庫

（1）邏輯備份

//文本備份

pg_dump  -d test -h localhost -p 5432  -U dba -F p -f /pgdb/backup/test.out

//定制備份，允許對歸檔元素進行選取和重新排列， 預設壓縮；

pg_dump  -d test -h localhost -p 5432  -U dba -F c -f /pgdb/backup/test.bak

//tar方式

pg_dump  -d test -h localhost -p 5432  -U dba -F t -f /pgdb/backup/test.tar

//并行

pg_dump  -d test -h localhost -p 5432  -U dba -F d –j 4 -f /pgdb/backup/test.d

（2）邏輯恢複

create   database test;

pg_restore  -h localhost  -p  5432 -U dba  -d  test/pgdb/backup/test.d

//隻恢複模式

pg_restore  -h localhost  -p  5432 -U dba -s – j 4 -d  test /pgdb/backup/test.d

//隻恢複資料

pg_restore  -h localhost  -p  5432 -U dba -a – j 4 -d  test /pgdb/backup/test.d

3.2備份與恢複一個表

pg_dump -d test  -h localhost –p 5432  -U dba -F d -t emp4 -j 4 -f /pgdb/backup/emp4.d

pg_dump -d  test -h localhost -p 5432  -U  dba  -F c  -t emp4  -f /pgdb/backup/emp4.d

pg_restore  -h localhost  -p  5432 -U dba -s  -d test /pgdb/backup/emp4.d

pg_restore  -h localhost  -p  5432 -U dba -a  -d test /pgdb/backup/emp4.d

3.3備份與恢複模式

//導出指定模式 pg_dump  -d test -h localhost -p 5432  -U dba -F c -n  test -f /pgdb/backup/test.bak  //恢複時沒有這個模式，那麼會自動建立 pg_restore  -h  localhost  -p  5432 -U dba  -d test /pgdb/backup/test.bak //如果有模式，也有表,導入會報錯模式存在，表存在，但是資料導入成功，且追加方式 pg_restore -h localhost  -p  5432 -U dba  -d test  /pgdb/backup/test.bak //如果有模式，但是沒有表, 導入會報錯模式已經存在，但是資料導入成功 pg_restore  -h  localhost  -p  5432 -U dba  -d test  /pgdb/backup/test.bak //導入時先删除表，然後建立表，資料導入 pg_restore  -h  localhost  -p  5432 -U dba  -d test  -c /pgdb/backup/test.bak

3.4其他

dump 所有public schema是以emp開始的表，但不包括emp1這個表

pg_dump -t 'public.emp*' -T 'public.emp1 test > db.sql

Dump 所有的 schemas 名字以east或 west 開頭并以gsm結束的，但不包括名字中包含test的schema：

pg_dump -n 'east*gsm' -n 'west*gsm' -N'*test*' test > db.sql

注：pg_dump可以對針對單表或者多表進行備份

如：pg_dump  databasename–t tablename1 –t tablename2 > table.sql

3.5 pg_dumpall

 pg_dumpall 可以導出所有資料庫，類似 pg_dump，但用得較少。

缺點：

(1)pg_dumpall 導出所有資料庫到一個腳本檔案，維護不友善;

(2)pg_dumpall 依次導出所有庫，總耗時比并行pg_dump各個庫要多;

(3)pg_dumpall 僅支援導出檔案格式，還原時不能使用pg_restore 有效使用并行等參數。

優點：

pg_dumpall 在取全局對像時非常友善，例如取資料庫上的表空間(tablespace)，使用者(roles)等，這在資料庫結構遷移時非常有用。