PG數據庫之間的導入導出

本文將介紹如何對PG數據庫進行導入、導出，主要利用的是PG自帶的pg_dump、pg_dumpall、pg_restore、psql等命令，版本是9.4（不同版本的pg_dump \ pg_restore選項可能會有些不同，請注意）。

 

 

導出、導入的整體思路是：

1. 導出全局對象，如用戶、編碼、權限等，產生文件global-objs.dmp
2. 導出每個數據庫中的對象、結構，如建庫語句、用戶、權限、編碼、表結構、自定義類型等，產生 [庫名]-objs.dmp文件，如dxm-objs.dmp
3. 導出每個數據庫中的數據，這裏分兩種來考慮，一個是某庫中所有的數據（所有的表），生成單個文件dxm.dmp；一個是針對某庫中每個表分別進行導出備份，每個表一個文件，以[庫名]-[表名].dmp命名，如dxm-all_types.dmp
4. 在目標實例上建立全局對象，即導入global-objs.dmp
5. 導入對象，即dxm-objs.dmp文件中的內容
6. 導入數據，根據導出的不同，分為一次導入dxm.dmp，和分別導入每個表的文件，如dxm-all_types.dmp

 

 

可以采用以下步驟完成：

1. 導出公共對象，比如用戶、權限、編碼等
2. 導出某個庫上的對象，比如表、type等
3. 導出某個庫上的數據，即各個表的數據等

之後恢複按同樣的步驟恢複就可以了。

 

 

 

將公共部分輸出到文件

1. [dxm@rdsdba ~]$ /pkg/pgsql/bin/pg_dumpall -h 192.168.xx.xx -g -p 5432 -f gloable.dmp

這裏會將所有的結構、對象、編碼等必要信息導出，用於在新庫上執行。

 

 

需要注意一點的就是，進行導出的用戶必須有相應的權限（如上麵例子中默認用的的所在的操作係統用戶，即dxm，也是創建這個實例時的用戶，擁有最高權限。），沒有權限會報以下的日誌：

1. [dxm@rdsdba ~]$ /pkg/pgsql/bin/pg_dumpall  -h 192.168.xx.xx -p 5432 -U pgtmp
2. ……
3. pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
4. pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2

 

 

 

這裏建議是將結構、對象與數據的導入、導出分開進行，會邏輯更清晰，也更好定位問題。

 

 

導出某個庫上的結構：

1. [dxm@rdsdba ~]$ [backcolor=#ffff00][b]/pkg/pgsql/bin/pg_dump -s -C -v -fdxm-objs.dmp -ddxm -h 192.168.xx.xx -Udxm -p 5432[/b][/backcolor]
2. Password: 
3. pg_dump: reading schemas
4. ……

這裏因為帶了"-v"選項，所以會打印較多的信息，從中也能看出讀出了哪些信息。選項有：

• “-s”選項，可以將庫中所有的對象導出，而不導出數據
• “-x”選項，會去除GRANT/INVOKE語句
• 更多選項，請參考"pg_dump --help"

 

具體看看導出了什麼內容：

1. [dxm@rdsdba ~]$ cat dxm-objs.dmp
2. ……
4. CREATE DATABASE "dxm" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8';
6. ALTER DATABASE "dxm" OWNER TO "dxm";
8. \connect "dxm"
9. ……
11. CREATE TABLE single_types (
12.     id integer,
13.     name text,
14.     descrps character varying(800),
15.     md5 bytea
16. );
17. ……

由上麵的內容可以看出來，隻包含了所有的對象，而沒有數據，並且包含了創建數據庫的語句。有了這些對象後，再進行數據導入即可。

 

 

 

 

導出的數據支持四種格式：SQL文件、自定義、目錄、壓縮格式。

• SQL文件

隻能通過psql來進行恢複，將SQL文件中的SQL語句進行重做，速度較慢，但較為簡單，出了問題修改SQL文件即可。但數據量大了之後，單個SQL文件就可以達到很大的規模，將很難處理。

• 自定義的格式，

會對數據進行一定的壓縮，且可以利pg_restore進行並發導入。但輸出的同樣是單個文件，對於數據量過大，同樣不太好處理。對於中等大小的實例比較合適，與SQL文件的大小簡單對比如下（all_types.dmp是SQL文件）：

• -rw-rw-r-- 1 dxm dxm 111736 Jun 2 18:43 all_types.cs
• -rw-rw-r-- 1 dxm dxm 1218139 Jun 2 18:04 all_types.dmp

• 目錄的方式

目錄的方式目前比較適合較大的實例，原因如下：

• 可以使用pg_dump的並發導出
• 可以使用pg_restore的並發導入
• 每個表一個文件，不至於單個文件過大（如SQL文件的方式）
• 有數據壓縮

 

 

 

“-Fc”選項，會采用自定義的格式，會占用較小的空間 ，空間大小如下所示：

1. [dxm@rdsdba ~]$ /pkg/pgsql/bin/pg_dump -a -v -f dxm.cs -Fc -d dxm -h 192.168.xx.xx -U dxm -p 5432
3. -rw-rw-r-- 1 dxm  dxm   51176 Jun  2 20:33 single_types.cs
4. -rw-rw-r-- 1  dxm  dxm  588859 Jun  2 19:50 single_types.dmp

"-Fd"選項，目錄格式。導出的時候，"-j NUM"會多線程的導出數據，提高性能，隻在"-Fd"選項下有效，例子如下：

1. [dxm@rdsdba ~]$ [backcolor=#ffff00][b]/pkg/pgsql/bin/pg_dump -a -v -j 6 -f dxm.folder -Fd -d dxm -h 192.168.xx.xxx -U dxm -p 5432[/b][/backcolor]
3. [dxm@rdsdba ~]$ ll dxm.folder/
4. total 164
5. -rw-rw-r-- 1 dxm  dxm  50533 Jun  2 20:40 2866.dat.gz
6. -rw-rw-r-- 1  dxm  dxm 110270 Jun  2 20:40 2867.dat.gz
7. -rw-rw-r-- 1  dxm  dxm    817 Jun  2 20:40 toc.dat[font=arial] [/font]

 

 

 

恢複數據的時候，根據導出的過程，先恢複對象部分，再對數據進行恢複。

 

 

首先恢複全局的信息，包括用戶、編碼等：

1. [dxm@rdsdba ~]$ /pkg/pgsql/bin/psql  -d postgres -U dxm -h 192.168.xx.xxx -p 5433 -f  gloable.dmp
2. SET
3. SET
4. SET
5. ……

 

 

其次，恢複某庫上的對象。

 

如果手動在目標實例上建了這個庫，則連到這個庫上即可；如果不是，則pg_dump的選項中應有“-C”，連到postgres即可。以下例子是帶有“-C”選項的，可以看到，導出的文件中包含了庫創建的語句：

1. [dxm@rdsdba ~]$[backcolor=#ffff00][b] /pkg/pgsql/bin/psql  -d postgres -U dxm -h 192.168.xx.xxx -p 5433 -f dxm-objs.dmp[/b][/backcolor]
2. SET
3. SET
4. SET
5. SET
6. SET
7. SET
8. CREATE DATABASE
9. ALTER DATABASE
10. ……

 

 

自定義格式和目錄方式，在恢複的時候都是支持多線程的，這對於大數據量有較好的效果。本次不對性能做太多分析，隻看下功能。

 

 

自定義格式：

1. [dxm@rdsdba ~]$ [b]/pkg/pgsql/bin/pg_restore -j4 -Fc -h 192.168.xx.xxx -d dxm -U dxm -p 5433 dxm.cs[/b]

目錄方式：

1. [dxm@rdsdba ~]$ [b]/pkg/pgsql/bin/pg_restore -Fd -h 192.168.xx.xxx -d dxm -U dxm -p 5433 dxm.folder/[/b]

可以看得出來， pg_restore的使用方式要簡單很多。這裏主要是因為將數據和對象分開來考慮了，所以這一步就隻是數據。如果導出的文件包含數據和對象，通過pg_restore也是可以隻恢複對象，或者數據。

另外，自定義格式和目錄的方式，數據文件並不是可讀的，對於數據的安全也是多了一點點保障。

 

 

• pg_dump/pg_restore功能是比較簡單有效的
• pg_dump對於對象類型的支持比較完整，包括複合數據類型、複雜類型等都有很好的支持
• PG還支持其他的方式（比如copy），有興趣的同學可以了解下

 

 

 

 

 

在進行導入、導出之前，需要在源庫上創建一些模擬數據。已經有數據和實例的，可以用已有的，可略過這一步。

 

 

1. create database "dxm" owner  "dxm";
3. 基本類型的表
4. create table if not exists single_types(
5. id integer, 
6. name text, 
7. descrps varchar(800), 
8. md5 bytea
9. );
11. ——創建複合類型
12. create type branch_desc as (owner text, name text);
14. ——擁有數組、複合類型和其他基本類型的表
15. create table if not exists all_types(
16. id integer primary key,  
17. name text not null, 
18. time timestamp not null, 
19. price decimal,
20. num numeric, 
21. valid boolean, 
22. profit_per_quarter decimal[],   
23. branch branch_desc,
24. md5 bytea
25. );

 

 

 

此處的數據自動生成，暫時不考慮邏輯性，即數據之間的關係（如關聯關係等）。因隻用於導入、導出，類型的多樣比邏輯關係更有作用。

1. ——插入10000條記錄
2. insert into single_types values(
3.     generate_series(0, 9999), 
4.     substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),       substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),
5.     E'\\xDEADBEAFAE346812734989'
6. );
8. ——插入10000條記錄
9. insert into all_types values(
10.     generate_series(0, 9999),
11.     substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),
12.     generate_series(now(), now() + '1 week', '1 day')::timestamp,
13.     (random() * 100.)::numeric(10,2),
14.     (random() * 100.)::numeric(10,0),
15.     true,
16.     '{100,100,100,100}',
17.     '("dxm", "hangzhou")',
18.     E'\\xCA9F87A98270197FA9FA'
19. );

 

   注：原創，從https://bbs.aliyun.com/read/247030.html?spm=5176.bbsl264.0.0.QBfiR2 搬過來。