PostgreSQL数据库之间的导入导出
PostgreSQL数据库之间的导入导出
本文将介绍如何对PG数据库进行导入、导出,主要利用的是PG自带的pg_dump、pg_dumpall、pg_restore、psql等命令。
导出、导入的整体思路是:
- 导出全局对象,如用户、编码、权限等,产生文件global-objs.dmp
- 导出每个数据库中的对象、结构,如建库语句、用户、权限、编码、表结构、自定义类型等,产生 [库名]-objs.dmp文件,如dxm-objs.dmp
- 导出每个数据库中的数据,这里分两种来考虑,一个是某库中所有的数据(所有的表),生成单个文件dxm.dmp;一个是针对某库中每个表分别进行导出备份,每个表一个文件,以[库名]-[表名].dmp命名,如dxm-all_types.dmp
- 在目标实例上建立全局对象,即导入global-objs.dmp
- 导入对象,即dxm-objs.dmp文件中的內容
- 导入数据,根据导出的不同,分为一次导入dxm.dmp,和分别导入每个表的文件,如dxm-all_types.dmp
数据的导出这里主要采用pg_dump工具,可以导出为SQL文件、目录方式(“-Fd”)和自定义格式(“-Fc”)等。其中SQL文件比较适合较小的实例数据量较小的情况,目录方式因为可以在导入、导出都用并发的方式,因此可以用于较大实例;自定义格式可以在导入的时候用上并发。
导出过程
如果只是较小的实例,那么使用下面简单的例子就可以了。
简单的例子 —— 小实例的导出、导入
利用pg_dumpall可以一次性导出PG实例中所有的数据和对象,但只适合用于数据量较小的实例,如:[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dumpall -h 192.168.xx.xxx -p 5432 -f global.dmp
- [dxm@rdsdba ~]$ head -n 15 global.dmp
- ……
- COPY single_types (id, name, descrps, md5) FROM stdin;
- 0 abcdefghi abcdefghijklmnopqrstuvw \xdeadbeafae346812734989
- 1 abcdefg a \xdeadbeafae346812734989
- 2 abcdefghijklm abcdefghijklmnop \xdeadbeafae346812734989
- ……
导出完成后对导出的文件在目标库进行执行即可,如:[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/psql -d postgres -Udxm -h 192.168.xx.xxx -p 5433 -f all.dmp
- SET
- SET
- ……
- ALTER ROLE
- REVOKE
- ……
查看数据之后,可以看到所有的数据都已经过来了。
注:pg_dumpall可以导出所有结构和数据,但这里不推荐完全依靠pg_dumpall完成数据的导出。pg_dumpall只产生一个SQL文件,不能用上pg_restore的并行导入*,数据量大的时候会时间较长。
如果小的实例,或者不想太费心考虑大量数据的问题,这里应该足够了。如果有更多的数据,多到一个SQL文件很难处理,就需要考虑下面的用法了。
复杂数据的导出
针对数据量较大,对象较多的数据库,可以采用以下步驟完成:
- 导出公共对象,比如用户、权限、编码等
- 导出某个库上的对象,比如表、type等
- 导出某个库上的数据,即各个表的数据等
之后恢复按同样的步驟恢复就可以了。
第一步,导出所有公共对象,包括编码用户、权限等
直接输出的形式是这样的:[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dumpall -h 192.168.xx.xx -g -p 5432
- ……
- CREATE ROLE "dxm";
- ALTER ROLE "dxm" WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD ‘md5ddd5eedda7057f4dbc0795c4d801e0d4’;
- ……
可以输出到文件[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dumpall -h 192.168.xx.xx -g -p 5432 -f gloable.dmp
这里会将所有的结构、对象、编码等必要信息导出,用于在新库上执行。需要注意一点的就是,进行导出的用户必须有相应的权限(如上面例子中默认用的的所在的操作系统用户,即dxm,也是创建这个实例时的用户,拥有最高权限。),没有权限会报以下的日志:[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/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
第二步,导出库上的结构和对象
这里建议是将结构、对象与数据的导入、导出分开进行,会逻辑更清晰,也更好定位问题[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dump -s -C -v -fdxm-objs.dmp -ddxm -h 192.168.xx.xx -Udxm -p 5432
- Password:
- pg_dump: reading schemas
- ……
这里因为带了"-v"选项,所以会打印较多的信息,从中也能看出读出了哪些信息。具体看看导出了什么內容:[url=]复制代码[/url]
- [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
- );
- ……
由上面的內容可以看出来,只包含了所有的对象,而没有数据,并且包含了创建数据库的语句。有了这些对象后,再进行数据导入即可。
其中,“-s”选项可以将库中所有的对象导出,“-C”选项可以将建库的语句也输出到文件中。
第三步,导出数据
导出的数据支持四种格式: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文件的方式)
- 有数据压缩
- 压缩格式,在目录的方式中,已经用到了压缩,此处不作涉及了。
同时,导出数据分为两种方式:一种是将库的所有数据导出,二是将数据按表一个个的导出。按表导出,只是参数不同而已,其他方式类似,此处就不考虑了。
以下只导出数据部分,因此都是"-a"选项。
目录方式和自定义格式
“-Fc”选项,会采用自定义的格式,会占用较小的空间,且可以利用pg_restore ,空间大小如下所示:
[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/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
"-j NUM",导出的时候,“-j”选项会多线程的导出数据,提高性能,只在"-Fd"选项下有效,例子如下:
[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dump -a -v -j 6 -f dxm.folder -Fd -d dxm -h 192.168.xx.xxx -U dxm -p 5432
- [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
将库导出到SQL文件的方式
[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dump -a -v -f dxm.dmp -d dxm -h 192.168.xx.xx -U dxm -p 5432
- Password:
- pg_dump: reading schemas
- pg_dump: reading user-defined tables
- pg_dump: reading extensions
- ……
- [dxm@rdsdba ~]$ cat dxm.dmp
- ……
- COPY all_types (id, name, "time", price, num, valid, profit_per_quarter, branch, md5) FROM stdin;
- 0 ab 2015-06-02 16:36:37.476248 75.27 68 t {100,100,100,100} (dxm," hangzhou") \xca9f87a98270197fa9fa
- 1 abcdefghijklmnopqr 2015-06-03 16:36:37.476248 28.75 47 t {100,100,100,100} (dxm," hangzhou") \xca9f87a98270197fa9fa< li>……
从內容可以看出来,文件中就是一系列的SQL,数据部分其实就是COPY命令的內容。也可以输出成INSERT命令的方式,带上参数“–inserts”即可。
针对表的导出的一个例子:
[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_dump -a -v -f single_types.dmp -t single_types -d dxm -h 192.168.xx.xxx -U dxm -p 5432
- Password:
- pg_dump: reading schemas
- pg_dump: reading user-defined tables
- pg_dump: reading extensions
- pg_dump: reading user-defined functions
- pg_dump: reading user-defined types
- ……
以上是整个库导出的情况,针对表进行导出是类似的,只是参数的不同而已。实际上,完全可以通过查pg_class得出当前库中有哪些表,然后通过脚本的方式完成整个库的表的导出,从中也可以根据表的大小进行并发的控制等。这个就很复杂了,此处不作展开。
恢复数据
恢复数据的时候,根据导出的过程,先恢复对象部分,再对数据进行恢复。
恢复对象
首先恢复全局的信息,包括用户、编码等:
[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/psql -d postgres -U dxm -h 192.168.xx.xxx -p 5433 -f gloable.dmp
- SET
- SET
- SET
- ……
其次,恢复某库上的对象,最开始没有这个库,所以连到postgres即可:
[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/psql -d postgres -U dxm -h 192.168.xx.xxx -p 5433 -f dxm-objs.dmp
- SET
- SET
- SET
- SET
- SET
- SET
- CREATE DATABASE
- ALTER DATABASE
- ……
可以看到,导出的文件中包含了库创建的语句。
自定义格式和目录方式
自定义格式和目录方式,在恢复的时候都是支持多线程的,这对于大数据量有较好的效果。本次不对性能做太多分析,只看下功能。
自定义格式:[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_restore -j4 -h 192.168.xx.xxx -d dxm -U dxm -p 5433 dxm.cs
目录方式:[url=]复制代码[/url]
- [dxm@rdsdba ~]$ /u01/pgsql/bin/pg_restore -j4 -h 192.168.xx.xxx -d dxm -U dxm -p 5433 dxm.folder/
可以看得出来, pg_restore的使用方式要简单很多。这里主要是因为将数据和对象分开来考虑了,所以这一步就只是数据。如果导出的文件包含数据和对象,通过pg_restore也是可以只恢复对象,或者数据。
另外,自定义格式和目录的方式,数据文件并不是可读的,对于数据的安全也是多了一点点保障。
总结
- pg_dump/pg_restore功能是比较简单有效的
- pg_dump对于对象类型的支持比较完整,包括复合数据类型、复杂类型等都有很好的支持
- PG还支持其他的方式(比如copy),有兴趣的同学可以了解下
附
源库结构、数据(已有数据的,可略过)
在进行导入、导出之前,需要在源库上创建一些模拟数据。已经有数据和实例的,可以用已有的,可略过这一步。
创建原始的库和表:
[url=]复制代码[/url]
- 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,
- md5 bytea
- );
插入数据
此处的数据自动生成,暂时不考虑逻辑性,即数据之间的关系(如关联关系等)。因只用于导入、导出,类型的多样比逻辑关系更有作用。
[url=]复制代码[/url]
- ——插入10000条记录
- insert into single_types values(
- generate_series(0, 9999),
- substr(‘abcdefghijklmnopqrstuvwxyz’,1,(random() * 26) ::integer), substr(‘abcdefghijklmnopqrstuvwxyz’,1,(random() * 26) ::integer),
- E’\xDEADBEAFAE346812734989′
- );
- ——插入10000条记录
- 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’
- );