PG資料庫之間的導入導出

本文将介紹如何對pg資料庫進行導入、導出，主要利用的是pg自帶的pg_dump、pg_dumpall、pg_restore、psql等指令，版本是9.4<b>（不同版本的pg_dump \ pg_restore選項可能會有些不同，請注意）</b>。

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

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

<b>資料的導出這裡主要采用pg_dump工具，可以導出為sql檔案、目錄方式（“-fd”）和自定義格式（“-fc”）等。其中sql檔案比較适合較小的執行個體資料量較小的情況，目錄方式因為可以在導入、導出都用并發的方式，是以可以用于較大執行個體；自定義格式可以在導入的時候用上并發。</b>

<b>導出過程</b>

可以采用以下步驟完成：

1. 導出公共對象，比如使用者、權限、編碼等

2. 導出某個庫上的對象，比如表、type等

3. 導出某個庫上的資料，即各個表的資料等

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

<b><b>第一步，導出所有公共對象，包括編碼使用者、權限等</b></b>

将公共部分輸出到檔案

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

這裡會将所有的結構、對象、編碼等必要資訊導出，用于在新庫上執行。

需要注意一點的就是，進行導出的使用者必須有相應的權限（如上面例子中預設用的的所在的作業系統使用者，即dxm，也是建立這個執行個體時的使用者，擁有最高權限。），沒有權限會報以下的日志：

[dxm@rdsdba ~]$ /pkg/pgsql/bin/pg_dumpall  -h 192.168.xx.xx -p 5432 -u pgtmp

……

pg_dumpall: query failed: error:  permission denied for relation pg_authid

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

<b><b>第二步，導出庫上的結構和對象</b></b>

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

導出某個庫上的結構：

[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]

password: 

pg_dump: reading schemas

這裡因為帶了"-v"選項，是以會列印較多的資訊，從中也能看出讀出了哪些資訊。選項有：

<b>“-s”選項，可以将庫中所有的對象導出，而不導出資料</b>

<b>“-c”選項，可以将建庫的語句也輸出到檔案中；如果手動建庫，則需要去除該選項</b>

<b>“-o”選項，如果目标庫的使用者與源庫不同，那麼導出的時候最好帶上這個選項，去除“alter ...  owner to”語句，以解決使用者不存在或使用者權限問題</b>

<b>“-x”選項，會去除grant/invoke語句</b>

<b>更多選項，請參考"pg_dump --help"</b>

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

[dxm@rdsdba ~]$ cat dxm-objs.dmp

create database "dxm" with template = template0 encoding = 'utf8' lc_collate = 'zh_cn.utf-8' lc_ctype = 'zh_cn.utf-8';

alter database "dxm" owner to "dxm";

\connect "dxm"

create table single_types (

    id integer,

    name text,

    descrps character varying(800),

    md5 bytea

);

由上面的內容可以看出來，隻包含了所有的對象，而沒有資料，并且包含了建立資料庫的語句。有了這些對象後，再進行資料導入即可。

<b><b>第三步，導出資料</b></b>

導出的資料支援四種格式：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檔案的方式）

有資料壓縮

<b>

</b>

<b>隻導出資料部分，不包括結構部分，是"-a"選項。</b>

<b><b>目錄方式和自定義格式</b></b>

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

[dxm@rdsdba ~]$ /pkg/pgsql/bin/pg_dump -a -v -f dxm.cs -fc -d dxm -h 192.168.xx.xx -u dxm -p 5432

-rw-rw-r-- 1 dxm  dxm   51176 jun  2 20:33 single_types.cs

-rw-rw-r-- 1  dxm  dxm  588859 jun  2 19:50 single_types.dmp

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

[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]

[dxm@rdsdba ~]$ ll dxm.folder/

total 164

-rw-rw-r-- 1 dxm  dxm  50533 jun  2 20:40 2866.dat.gz

-rw-rw-r-- 1  dxm  dxm 110270 jun  2 20:40 2867.dat.gz

-rw-rw-r-- 1  dxm  dxm    817 jun  2 20:40 toc.dat[font=arial] [/font]

<b>恢複資料</b>

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

<b><b>恢複對象</b></b>

首先恢複全局的資訊，包括使用者、編碼等：

[dxm@rdsdba ~]$ /pkg/pgsql/bin/psql  -d postgres -u dxm -h 192.168.xx.xxx -p 5433 -f  gloable.dmp

set

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

<b>如果手動在目标執行個體上建了這個庫，則連到這個庫上即可；如果不是，則pg_dump的選項中應有“-c”，連到postgres即可。以下例子是帶有“-c”選項的，</b><b>可以看到，導出的檔案中包含了庫建立的語句：</b>

[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]

create database

alter database

<b> 恢複資料</b>

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

自定義格式：

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

目錄方式：

[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也是可以隻恢複對象，或者資料。

另外，自定義格式和目錄的方式，資料檔案并不是可讀的，對于資料的安全也是多了一點點保障。

<b>總結</b>

- pg_dump/pg_restore功能是比較簡單有效的

- pg_dump對于對象類型的支援比較完整，包括複合資料類型、複雜類型等都有很好的支援

- pg還支援其他的方式（比如copy），有興趣的同學可以了解下

<b>附</b>

<b>源庫結構、資料（已有資料的，可略過）</b>

在進行導入、導出之前，需要在源庫上建立一些模拟資料。已經有資料和執行個體的，可以用已有的，可略過這一步。

<b>建立原始的庫和表：</b>

create database "dxm" owner  "dxm";

基本類型的表

create table if not exists single_types(

id integer, 

name text, 

descrps varchar(800), 

md5 bytea

——建立複合類型

create type branch_desc as (owner text, name text);

——擁有數組、複合類型和其他基本類型的表

create table if not exists all_types(

id integer primary key,  

name text not null, 

time timestamp not null, 

price decimal,

num numeric, 

valid boolean, 

profit_per_quarter decimal[],   

branch branch_desc,

<b>插入資料</b>

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

——插入10000條記錄

insert into single_types values(

    generate_series(0, 9999), 

    substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),       substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),

    e'\\xdeadbeafae346812734989'

insert into all_types values(

    generate_series(0, 9999),

    substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),

    generate_series(now(), now() + '1 week', '1 day')::timestamp,

    (random() * 100.)::numeric(10,2),

    (random() * 100.)::numeric(10,0),

    true,

    '{100,100,100,100}',

    '("dxm", "hangzhou")',

    e'\\xca9f87a98270197fa9fa'

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