PG数据库之间的导入导出

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 本文将介绍如何对PG数据库进行导入、导出,主要利用的是PG自带的pg_dump、pg_dumpall、pg_restore、psql等命令,版本是9.4(不同版本的pg_dump \ pg_restore选项可能会有些不同,请注意)。
本文将介绍如何对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

数据的导出这里主要采用pg_dump工具,可以导出为SQL文件、目录方式(“-Fd”)和自定义格式(“-Fc”)等。其中SQL文件比较适合较小的实例数据量较小的情况,目录方式因为可以在导入、导出都用并发的方式,因此可以用于较大实例;自定义格式可以在导入的时候用上并发。
 
导出过程
 
可以采用以下步驟完成:

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”选项,可以将库中所有的对象导出,而不导出数据
  • “-C”选项,可以将建库的语句也输出到文件中;如果手动建库,则需要去除该选项
  • “-O”选项,如果目标库的用户与源库不同,那么导出的时候最好带上这个选项,去除“ALTER ...  OWNER TO”语句,以解决用户不存在或用户权限问题
  • “-x”选项,会去除GRANT/INVOKE语句
  • 更多选项,请参考"pg_dump --help"
 
具体看看导出了什么內容:
  1. [dxm@rdsdba ~]$ cat dxm-objs.dmp
  2. ……
  3. CREATE DATABASE "dxm" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8';
  4. ALTER DATABASE "dxm" OWNER TO "dxm";
  5. \connect "dxm"
  6. ……
  7. CREATE TABLE single_types (
  8.     id integer,
  9.     name text,
  10.     descrps character varying(800),
  11.     md5 bytea
  12. );
  13. ……
由上面的內容可以看出来,只包含了所有的对象,而没有数据,并且包含了创建数据库的语句。有了这些对象后,再进行数据导入即可。
 
 
第三步,导出数据
 
 
导出的数据支持四种格式: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”选项,会采用自定义的格式,会占用较小的空间 ,空间大小如下所示:
  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
  2. -rw-rw-r-- 1 dxm  dxm   51176 Jun  2 20:33 single_types.cs
  3. -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]
  2. [dxm@rdsdba ~]$ ll dxm.folder/
  3. total 164
  4. -rw-rw-r-- 1 dxm  dxm  50533 Jun  2 20:40 2866.dat.gz
  5. -rw-rw-r-- 1  dxm  dxm 110270 Jun  2 20:40 2867.dat.gz
  6. -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";
  2. 基本类型的表
  3. create table if not exists single_types(
  4. id integer, 
  5. name text, 
  6. descrps varchar(800), 
  7. md5 bytea
  8. );
  9. ——创建复合类型
  10. create type branch_desc as (owner text, name text);
  11. ——拥有数组、复合类型和其他基本类型的表
  12. create table if not exists all_types(
  13. id integer primary key,  
  14. name text not null, 
  15. time timestamp not null, 
  16. price decimal,
  17. num numeric, 
  18. valid boolean, 
  19. profit_per_quarter decimal[],   
  20. branch branch_desc,
  21. md5 bytea
  22. );
 
插入数据
 
 
此处的数据自动生成,暂时不考虑逻辑性,即数据之间的关系(如关联关系等)。因只用于导入、导出,类型的多样比逻辑关系更有作用。
  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. );
  7. ——插入10000条记录
  8. insert into all_types values(
  9.     generate_series(0, 9999),
  10.     substr('abcdefghijklmnopqrstuvwxyz',1,(random() * 26) ::integer),
  11.     generate_series(now(), now() + '1 week', '1 day')::timestamp,
  12.     (random() * 100.)::numeric(10,2),
  13.     (random() * 100.)::numeric(10,0),
  14.     true,
  15.     '{100,100,100,100}',
  16.     '("dxm", "hangzhou")',
  17.     E'\\xCA9F87A98270197FA9FA'
  18. );
 
   注:原创,从http://bbs.aliyun.com/read/247030.html?spm=5176.bbsl264.0.0.QBfiR2 搬过来。
 
 
 
 
 
 
 

相关实践学习
【文生图】一键部署Stable Diffusion基于函数计算
本实验教你如何在函数计算FC上从零开始部署Stable Diffusion来进行AI绘画创作,开启AIGC盲盒。函数计算提供一定的免费额度供用户使用。本实验答疑钉钉群:29290019867
建立 Serverless 思维
本课程包括: Serverless 应用引擎的概念, 为开发者带来的实际价值, 以及让您了解常见的 Serverless 架构模式
目录
相关文章
|
6月前
|
关系型数据库 数据库连接 数据库
Python执行PG数据库查询语句:以Markdown格式打印查询结果
使用Python的`psycopg2`和`pandas`库与PostgreSQL交互,执行查询并以Markdown格式打印结果。首先确保安装所需库:`pip install psycopg2 pandas`。接着建立数据库连接,执行查询,将查询结果转换为DataFrame,再用`tabulate`库将DataFrame格式化为Markdown。代码示例包括连接函数、查询函数、转换和打印函数。最后限制列宽以适应输出。
|
7月前
|
DataWorks Oracle 关系型数据库
DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本,并遇到了与RAW字段相关的语法错误,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
108 0
|
5月前
|
关系型数据库 Java 数据库
实时计算 Flink版操作报错合集之flinksql采PG数据库时报错,该如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
7月前
|
SQL 监控 关系型数据库
PG数据库释放闲置连接
PG数据库释放闲置连接
275 0
|
7月前
|
关系型数据库 数据库 流计算
Flink CDC在处理Incremental Snapshot PG数据库时
Flink CDC在处理Incremental Snapshot PG数据库时
320 1
|
7月前
|
SQL 关系型数据库 数据库
postgresql|数据库|pg数据库的文件系统详解---最全面的解析
postgresql|数据库|pg数据库的文件系统详解---最全面的解析
767 0
|
7月前
|
Oracle 关系型数据库 MySQL
PG系、Oracle、MySQL数据库在特定场景下结果差异分析
本文主要介绍以PolarDB O引擎、ADB PG为代表的PG系数据库在某种特定事务场景下,其事务结果与Oracle、MySQL不同的现象,并分析该现象出现的原因。
221 0
PG系、Oracle、MySQL数据库在特定场景下结果差异分析
|
7天前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
27 3
|
7天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
31 3
|
7天前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
36 2