pg_dump

pg_dump 是一個邏輯備份工具。使用 pg_dump 可以在數(shù)據(jù)庫處于使用狀態(tài)下進行一致
性的備份,它不會阻塞其他用戶對數(shù)據(jù)庫的訪問 。

一致性備份是 pg_dump 開始運行時，給數(shù)據(jù)庫打了一個快照，且在 pg_dump 運行過程
中發(fā)生的更新將不會被備份。

pg_dump 只備份單個數(shù)據(jù)庫，不能備份數(shù)據(jù)庫公共的全局對象（例如角色和表空間）

創(chuàng)建一個database mydb ，在mydb下創(chuàng)建一個表t1

mydb=# create table t1(id serial,info text);
CREATE TABLE
mydb=# create index on t1(id);
CREATE INDEX
mydb=# insert into t1 values(generate_series(1,10000));
INSERT 0 10000
mydb=# \1
invalid command \1
Try \? for help.
mydb=# \q

pg_dump常用參數(shù)

-h host，指定數(shù)據(jù)庫主機名，或者 IP
-p port，指定端口號
-U user，指定連接使用的用戶名
-W，按提示輸入密碼
dbname，指定連接的數(shù)據(jù)庫名稱，實際上也是要備份的數(shù)據(jù)庫名稱。
-f,–file：輸出到指定文件中
-F,–format=c|d|t|p：
c 為自定義格式，也是二進制格式，壓縮存儲，只能使用 pg_restore 來還原, 可
以指定還原的表, 編輯 TOC 文件, 定制還原的順序, 表, 索引等。
d 為目錄
t 表示輸出為 tar 包
p 為純文本 SQL，大庫不推薦；
-j,–jobs=num：指定并行導(dǎo)出的并行度
-a,–data-only：只導(dǎo)出數(shù)據(jù)，不導(dǎo)出表結(jié)構(gòu)
-c,–clean：是否生成清理該數(shù)據(jù)庫對象的語句，比如 drop table
-C,–create：是否輸出一條創(chuàng)建數(shù)據(jù)庫語句
-n,–schema：只轉(zhuǎn)存匹配 schema 的模式內(nèi)容
-N,–exclude-scheam：不轉(zhuǎn)存匹配 schema 的模式內(nèi)容
-O,–no-owner，不設(shè)置導(dǎo)出對象的所有權(quán)
-s,–schema-only：只導(dǎo)致對象定義模式，不導(dǎo)出數(shù)據(jù)
-t,–table：只轉(zhuǎn)存匹配到的表，視圖，序列，可以使用多個-t 匹配多個表
-T,–exclude-table：不轉(zhuǎn)存匹配到的表。
–inserts：使用 insert 命令形式導(dǎo)出數(shù)據(jù)，這種方式比默認的 copy 方式慢很多，但是可
用于將數(shù)據(jù)導(dǎo)入到非 PostgreSQL 數(shù)據(jù)庫。
–column-inserts：導(dǎo)出的數(shù)據(jù)，有顯式列名

備份

(1)導(dǎo)出sql語句

一個insert插一行
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W --insert mydb >mydb.sql一次插入2行
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W --insert  --rows-per-insert=2 mydb  >mydb2.sql

（2）導(dǎo)出文件

-- 要轉(zhuǎn)儲一個數(shù)據(jù)庫到一個自定義格式歸檔文件
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W  -Fc mydb  >mydb.dump--使用 5 個并行任務(wù)轉(zhuǎn)儲一個數(shù)據(jù)庫到一個目錄格式的歸檔 
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W  -Fd -j 5 mydb  -f mydbdir 

（3）用法舉例

-- 備份單個表 
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -t t1 --inserts > testdb.sql -- 備份多個表 
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -t t1 -t t2 --inserts > 
testdb.sql-- 如果只想備份 schema 模式中所有以 t 開頭的表，但是不包括 t1 表 
pg_dump -t "public.t*" -T public.t1 testdb > testdb.sql -- 轉(zhuǎn)儲所有 testdb 的數(shù)據(jù)庫對象，但是不包含以 1 結(jié)尾的表 
pg_dump -T '*1' testdb > testdb.sql -- 轉(zhuǎn)儲 testdb 中 public 和 test 這兩個 schema 中的內(nèi)容 
pg_dump -Fc -n public -n test testdb -f testdb.dump -- 轉(zhuǎn)儲 testdb 中除了 public schema 中的數(shù)據(jù)以外的所有數(shù)據(jù) 
pg_dump -Fc -N public testdb -f testdb.dump--只備份數(shù)據(jù)
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb --inserts -a > testdb.sql--只備份表結(jié)構(gòu)
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -s > testdb.sql

恢復(fù)

--恢復(fù)一個文本文檔 
psql mydb <mydb.sql-- 要把一個歸檔文件重新載入到一個（新創(chuàng)建的）名為 newdb 的數(shù)據(jù)庫： 
pg_restore -d newdb mydb.dump -- 把一個歸檔文件重新裝載到同一個數(shù)據(jù)庫（該歸檔正是從這個數(shù)據(jù)庫中轉(zhuǎn)儲得來）中，
丟掉那個數(shù)據(jù)庫中的當前內(nèi)容 
pg_restore -d newdb --clean mydb.dump-- 備份后直接進行恢復(fù)，文件不落地 
pg_dump testdb| psql newdb -- 并行備份恢復(fù) 
pg_dump -Fd -j4 testdb -f dumpdir 
pg_restore -d newdb -j4 dumpdir

利用toc文件選擇性恢復(fù)

-- 根據(jù)二進制備份文件生成 toc 文件 
方式一：pg_restore -l mydb.dump > mydb.toc 
方式二：pg_restore -l -f mydb.toc mydb.dump 
以上兩種方式相同效果
-- 修改 toc 文件，用‘;’號注釋掉不用還原的內(nèi)容: 
-- 以 toc 文件列表做恢復(fù) 
pg_restore -Fc  -d mydb mydb.dump -L mydb.toc
-- 檢查發(fā)現(xiàn) t1 表沒有被導(dǎo)入。--只恢復(fù)t1表
pg_dump -Fc -d mydb  -f mydb.dmp
pg_restore -l mydb.dmp |grep t1 >mydbt1.dmp
pg_restore -d mydb  mydb.dmp -L mydbt1.dmp

壓縮備份

-- 導(dǎo)出并且壓縮 
pg_dump testdb -f testdb.sql | gzip testdb.sql 
-- 解壓并且導(dǎo)入，壓縮文件不變： 
gunzip -c testdb.sql.gz | psql testdb -- 分割備份 
pg_dump testdb | split -b 1m 
-- 恢復(fù) 
cat filename* | psql dbname

大表備份

可以使用 -j 選項來指定執(zhí)行 pg_dump 和 pg_restore 時要使用的線程數(shù)。 
可以使用目錄格式 (-Fd)，它會提供壓縮轉(zhuǎn)儲（使用 gzip）。使用 -Fd 選項可以提供超過
5 倍的壓縮。對于較大的數(shù)據(jù)庫（例如超過 1 TB），壓縮轉(zhuǎn)儲可以減少磁盤 IOPS。示例：pg_dump -Fd testdb -j 5 -f dump_dir pg_restore -d newdb -j 5 dump_dir