PG数据库之间的导入导出(pg_dumpall,pg_dump,psql,pg_restore)

参考文献:https://bbs.aliyun.com/read/247030.html?displayMode=1


本文将介绍如何对PG数据库进行导入、导出,主要利用的是PG自带的 pg_dump 、pg_dumpall、pg_restore、psql等命令,版本是9.4 (不同版本的pg_dump \ pg_restore选项可能会有些不同,请注意)

导出、导入的整体思路是:
- 导出全局对象,如用户、编码、权限等,产生文件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


导出过程

可以采用以下步驟完成:
  1. 导出公共对象,比如用户、权限、编码等
  2. 导出某个库上的对象,比如表、type等
  3. 导出某个库上的数据,即各个表的数据等

之后恢复按同样的步驟恢复就可以了。

第一步,导出所有公共对象,包括编码用户、权限等

pg_dumpall命令知识点:
pg_dumpall 可以导出所有数据库,类似 pg_dump,但用得非常少,原因可能是多方面的,
1 pg_dumpall 导出所有数据库到一个脚本文件,维护不方便;
2 pg_dumpall 依次导出所有库,总耗时比并行 pg_dump 各个库要多;
3 pg_dumpall 仅支持导出文件格式,还原时不能使用 pg_restore 有效使用并行等参数。

pg_dumpall相关参数
-g 导出role和tablespace
-r 仅导出role
-t 仅导出tablespace
-f或> 导出对应的文件路径和文件名 
eg.
$ pg_dumpall -g > pg92_globle.sql
$ pg_dumpall -g -f /home/data/ pg92_globle.sql

将所有的结构、对象、编码等必要信息导出
[postgres@cqs]$ pg_dumpall -h 127.0.0.1 -p 5432 -g -f /home/pg/gloable1117.dmp

第二步,导出库上的结构和对象

建议是将结构、对象与数据的导入、导出分开进行,会逻辑更清晰,也更好定位问题。
pg_dump
  -h 主机地址
  -p 端口号
  -U 用户名
  -s  选项,可以将库中所有的对象导出,而不导出数据
  -a  选项,只导出数据部分,不包括结构部分
  -C 选项,可以将建库的语句也输出到文件中;如果手动建库,则需要去除该选项
  -O 选项,如果目标库的用户与源库不同,那么导出的时候最好带上这个选项,去除“ALTER ...  OWNER TO”语句,以解决用户不存在或用户权限问题
   -x 选项,会去除GRANT/INVOKE语句
   -Fc选项,会采用自定义的格式,会占用较小的空间
   -Fd选项,目录格式。导出的时候,"-j NUM"会多线程的导出数据,提高性能,只在"-Fd"选项下有效
...

导出某个库上的结构:
[postgres@cqs ~]$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -s  mydb>/home/postgres/mydb1.dump

第三步,导出数据
只导出数据部分,不包括结构部分,是"-a"选项。使用的指令是 pg_dump

导出的数据支持四种格式: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文件的方式)
- 有数据压缩


恢复数据

恢复数据的时候,根据导出的过程,先恢复对象部分,再对数据进行恢复。
首先恢复全局的信息,包括用户、编码等:
[postgres@cqs backup]$ psql -f /home/postgres/backup/gloable1117.dmp

自定义格式和目录方式,在恢复的时候都是支持多线程的,这对于大数据量有较好的效果。本次不对性能做太多分析,只看下功能。
自定义格式:
[postgres@cqs backup]$ pg_restore -j4 -Fc -h 127.0.0.1 -d xxx -U postgres -p 5432 data.dump
目录方式:
[postgres@cqs backup]$ pg_restore -Fd -h  127.0.0.1 -d dxm -U postgres -p 5432 data.folder
可以看得出来, pg_restore的使用方式要简单很多。这里主要是因为将数据和对象分开来考虑了,所以这一步就只是数据。如果导出的文件包含数据和对象,通过pg_restore也是可以只恢复对象,或者数据。 另外,自定义格式和目录的方式,数据文件并不是可读的,对于数据的安全也是多了一点点保障。
另外,也可以先登录连接对应的数据库后,使用\i filename进行数据恢复
data=# \i /home/postgres/backup/data.sql

  • 1
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL是一种开源的关系型数据库,它提供了多种管理工具来操作数据库,其中包括psqlpg_dump命令。 psql是一种命令行工具,可以用来与PostgreSQL数据库进行交互。它不仅能够执行SQL语句,还可以支持交互式命令。在使用psql命令时,有时需要在命令中包含密码信息。可以使用以下的方式来在命令行中带密码执行SQL语句: 1. 在命令行中输入psql命令,启动psql工具。 2. 输入连接数据库的命令,例如:psql -U username -d dbname -h hostname -p port。 3. 输入密码,此时必须在命令行中输入密码。 4. 执行SQL语句,例如:SELECT * FROM tablename; pg_dump是PostgreSQL数据库备份工具,可以用来将数据库中的数据导出到一个文件中。使用pg_dump命令时,也需要在命令行中包含密码信息。可以使用以下的方式在命令行中带密码执行pg_dump命令: 1. 在命令行中输入pg_dump命令,启动pg_dump工具。 2. 输入数据库连接信息和密码,例如:pg_dump -U username -d dbname -h hostname -p port -W。 3. 执行备份操作,例如:pg_dump -U username -d dbname -h hostname -p port -W > backup.sql。 总的来说,在使用psqlpg_dump命令时,为了在命令中包含密码信息,需要使用“-W”参数将密码输入到命令行中。当然,这种方式存在一定的安全隐患,因为密码可以被其他人看到。因此,最好使用其他方式来进行密码管理,例如使用配置文件、环境变量或者其他安全的方式。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值