因为业务需求,需要将服务器上的postgre多个数据库的数据整个库得迁移到另一个postgre数据库上。

一般表较少时,会使用postgre 的copy to 和 copy from 命令就能完成表的迁移,但这种方式需要target_database 上提前先创建好对应的表,并且每一个表都需要一次copy to 和copy from操作,当表比较多的时候,非常繁琐。
因此我查询了网上的方法,发现了pg_dump这个方法,但是网上的描述比较杂乱,因此我查询了postgresql的官方手册https://www.postgresql.org/docs/9.2/app-pgdump.html
使用pg_dump和pg_restore可以非常快速进行整个database的数据迁移或者备份。
以下是pg_dump的部分选项,pg_restore相似:
 -F format
--format=format
Selects the format of the output. format can be one of the following:
p
plain
Output a plain-text SQL script file (the default).
c
custom
Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.
d
directory
Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default.
t
tar
Output a tar-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be changed during restore. -C
--create
Begin the output with a command to create the database itself and reconnect to the created database. (With a script of this form, it doesn't matter which database in the destination installation you connect to before running the script.) If --clean is also specified, the script drops and recreates the target database before reconnecting to it.
This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.
-E encoding
--encoding=encoding
Create the dump in the specified character set encoding. By default, the dump is created in the database encoding. (Another way to get the same result is to set the PGCLIENTENCODING environment variable to the desired dump encoding.) -O
--no-owner
Do not output commands to set ownership of objects to match the original database. By default, pg_dump issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created database objects. These statements will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script). To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O.
This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.
 
举例:
pg_dump  -Fc dm  -O  > dm.dump
需要先su到有操作postgresql权限的用户,否则则需加上指定-h IP -U username
将对名为dm的database 以自定义的的方式并且忽略掉原数据库的owner进行dump。
pg_dump默认dump文件到当前user的的home目录下
pg_dump内部使用的copy命令,速度还比较快,几个G的数据20多分钟就能dump完
 
然后将dm.dump文件用FileZilla Client拷贝到target服务器上
因为在目标服务器上没有有操作postgresql权限的用户,所以需加上指定-h IP -U username
pg_restore -O -h IP -U username -d dm dm.dump
这样就能将数据库迁移到目标服务器上了,这里目标服务器已经有了dm数据库,若没有需要加上-C选项创建数据库。
-O的作用就是能将restore到目标服务上数据库的表的owner更改成目标服务上数据库的owner。
 

最新文章

  1. git创建仓库
  2. HMM 自学教程(五)前向算法
  3. 部署在IIS上的网站如何调试
  4. C++ Ouput Exactly 2 Digits After Decimal Point 小数点后保留三位数字
  5. 打补丁patch 命令使用
  6. cookie与localstorage和sessionstorage的区别比较
  7. hdu 4622 Reincarnation trie树+树状数组/dp
  8. mac 下周期调度命令或脚本
  9. windows系统-web渗透工具-AWVS
  10. LeetCode——Remove Element
  11. 固定DIV样式
  12. TestNg它@Factory详细解释------如何更改参数值测试
  13. 在centos上安装jenkins
  14. data parameter is nil 异常处理
  15. 剖析ElasticSearch核心概念,NRT,索引,分片,副本等
  16. css哪些属性可以继承
  17. js函数式编程——蹦床函数
  18. java http get、post请求
  19. Pycharm 字体大小快捷方式设置
  20. Lintcode: First Position of Target (Binary Search)

热门文章

  1. Spring Boot Dubbo 应用启停源码分析
  2. leetcode.字符串.12整数转罗马数字-Java
  3. leetcode.字符串.409最长回文串-Java
  4. 第十一篇 session和cookie自动登录机制
  5. Airbub 弃用React Native
  6. 支付宝支付接口-运行支付宝demo
  7. js求三个数的最大值运算
  8. .nett Core之路由配置
  9. (转)sql的group by应用
  10. 采用多个数据源是Spring的配置