因为业务需求,需要将服务器上的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相似:
1 -F format 2 --format=format 3 Selects the format of the output. format can be one of the following: 4 p 5 plain 6 Output a plain-text sql script file (the default). 7 c 8 custom 9 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. 10 d 11 directory 12 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. 13 t 14 tar 15 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. 16 17 -C 18 --create 19 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. 20 This option is only meaningful for the plain-text format. For the archive formats,you can specify the option when you call pg_restore. 21 -E encoding 22 --encoding=encoding 23 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.) 24 25 -O 26 --no-owner 27 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. 28 This option is only meaningful for the plain-text format. For the archive formats,you can specify the option when you call pg_restore. 29
举例:
pg_dump -Fc dm -O > dm.dump
pg_dump默认dump文件到当前user的的home目录下
pg_dump内部使用的copy命令,速度还比较快,几个G的数据20多分钟就能dump完
然后将dm.dump文件用FileZilla Client拷贝到target服务器上
pg_restore -O -h IP -U username -d dm dm.dump