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 搬过来。