postgresql從入門到精通教程 - 第36講：postgresql邏輯備份

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客服。