PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色權限、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第36講：PostgreSQL邏輯備份

第36講：12月02日(周六)19:30-20:30，往期公開課的文檔及視頻，聯系CUUG

內容1：邏輯備份概述

內容2：pg_dump使用

內容3：pg_dumpall使用

內容4：copy使用

PG導出導入工具

PG導出導入概述

· 可以使用這些實用程序執行以下操作：

歸檔歷史數據

保存表定義以防止用戶出錯

在計算機和數據庫之間或PostgreSQL服務器的不同版本之間移動數據

在數據庫之間傳輸數據

調用導出和導入程序的方法

命令行接口

其它管理工具

導出模式

· pg_dump只能備份單個數據庫，不會導出角色和表空間相關的信息，而且恢復的時候需要創建空數據庫。

· pg_dumpall可以備份所有數據庫，并且備份角色、表空間。

-F c 備份為二進制格式, 壓縮存儲. 并且可被 pg_restore 用于精細還原。

-F p 備份為文本, 大庫不推薦。

調用pg_dump

語法：

pg_dump [選項]... [數據庫名字]

示例：

pg_dump testdb > testdb.sql

pg_dump -F c -f testdb.dmp -C -E UTF8 -h 127.0.0.1 -U postgres testdb

pg_dump -F p -f testdb.sql -C -E UTF8 -h 127.0.0.1 -U postgres testdb

邏輯恢復

· 恢復順序

1.創建新表

2.導入數據

3.建立索引

4.觸發器已導入

5.在新表上啟用完整性約束

6.創建函數

· 恢復數據

文本格式備份恢復方式：

1、創建新數據庫

CREATE DATABASE new_db1;

2、恢復數據

psql new_db1 < testdb.sql

二進制格式備份恢復方式：

1、創建新數據庫

CREATE DATABASE new_db1;

2、恢復數據

pg_restore -d new_db1 testdb.dmp

二進制備份實現精細恢復

· 利用toc文件實現精細恢復

1、根據二進制備份文件生成 toc 文件

pg_restore -l -f testdb.toc testdb.dmp

2、修改 toc 文件，用‘;’號注釋掉不用還原的內容

203; 1259 40996 TABLE public dept postgres

204; 1259 41001 TABLE public emp postgres

;202; 1259 16391 TABLE public t1 postgres

2840; 0 40996 TABLE DATA public dept postgres

2841; 0 41001 TABLE DATA public emp postgres

;2839; 0 16391 TABLE DATA public t1 postgres

3、以toc文件做恢復

pg_restore -F c -L testdb.toc -d testdb testdb.dmp

其它導出導入技巧

· 利用管道符實現各種導出導入

導出并且壓縮

pg_dump testdb |gzip testdb.sql

解壓并且導入，壓縮文件不變，，導入到testdb數據庫：

gunzip -c testdb.sql.gz |psql testdb

導出后直接進行恢復，文件不落地，把testdb數據庫的表導入到testdb1：

pg_dump testdb| psql testdb1

并行導出與導入

pg_dump -Fd -j4 -f testdb.p.dump testdb

pg_restore -d testdb1 -j4 testdb.p.dump

其它備份恢復技巧

· 備份指定/排除的表

如果只想導出指定的表數據，可以使用-t參數，表名需要用兩個雙引號概括起來。

pg_dump -t ""departments"" -t ""employees"" testdb > testdb.sql

導出排除指定的表的數據庫

pg_dump -d testdb -T ""emp"" > testdb_ex_emp.sql

使用pg_dump實現主機間數據庫間遷移數據

· 文件不落地，實現數據庫遷移

1、配置兩個數據庫之間的信任關系(必須)

host all all 192.168.18.0/24 trust

2、在數據庫之間進行某個數據庫遷移

pg_dump -h pg1 -p 1922 -U postgres testdb|psql -h pg2 -p 5432 -U postgres testdb

3、遷移所有的數據庫到目標數據庫

pg_dumpall -h pg1 -p 1922 -U postgres |psql -h pg2 -p 5432 -U postgres

注意：

1、PG數據庫之間遷移無需配置dblink，只要指定主機名和端口即可。

2、由于用的是管道符，所以必須配置兩個數據庫遠程登錄信任關系。

3、遷移時兩個數據庫必須要有相同的表空間，需要提前創建。

copy概述

· 實現表與文件之間的交互

copy 命令用于數據庫中表與操作系統文件(和標準輸出、標準輸入)之間的相互拷貝，可以由表至文件，也可以由文件至表。

使用copy實現數據庫表和文本文件之間數據的遷移，非常方便，比Oracle的sqlloader易用性好。

copy語法

· 語法

文件到表

COPY table_name [ ( column ...] ) ]

FROM { 'filename' | command' | STDIN }

[ [ WITH ] ( option [, ] ) ]

表到文件

COPY { table_name column_name [, ...] ) ] | ( query ) }

TO { 'filename' | PROGRAM 'command' | STDOUT }

[ [ WITH ] ( option [, ...] ) ]

應用案例

· 文件到表

文件到表時，支持的文本內容格式有兩種，一種是tab制表符作為分隔符(不是空格);一種是逗號做為分隔符。

tab鍵作為分隔符：

1 a

2 b

3 c

逗號作為分隔符：

1,a,"I am students"

2,b,"I am teacher"

3,c,"I am boss"

· 文件到表

文件到表時，支持的文本內容格式有兩種，一種是tab制表符作為分隔符(不是空格);一種是逗號做為分隔符。

tab鍵作為分隔符：

\copy tab_name from /home/postgres/tab.txt;

逗號做為分隔符：

\copy tab_name from /home/postgres/tab.csv with csv;

· 表到文件

表到文件時，支持的文本內容格式有兩種，一種是tab制表符作為分隔符(不是空格);一種是逗號做為分隔符。

tab鍵作為分隔符：

\copy tab_name to /home/postgres/tab.txt;

逗號做為分隔符：

\copy tab_name to /home/postgres/tab.csv with csv;

CUUG PostgreSQL技術大講堂系列公開課，往期視頻及文檔，請聯系CUUG客服。