postgres的逻辑备份还原：pg_dump和pg_restore的使用

pg_dump


##1、
-h host 或 --host=host 
指定服务器主机名。也可以使用UNIX套接字路径，如果配置了$PGHOST则从此环境变量获取


##2、
-p port 或 --port=port
指定端口或者本地的UNIX套接字文件的扩展，对应环境变量￥PGPORT


##3、
-U username 或者 --username=username
指定用户名


##4、
-w 或者--no-password
不提示密码，如果无法登陆命令会失败，该选项用于后台脚本


##5、
-W 或者 --password
强制连接到数据库之前提示输入密码（如果服务器请求密码身份认证，pg_dump会自动提示密码输入，
不过如果不指定-W的时候，pg_dump会先去尝试连接服务器，使用-W避免了额外链接尝试）


##6、
--role=rolename
链接到数据库的时候发出一个 set ROLE rolename命令，相当于切换到另一个角色，当已验证的用户
缺少pg_dump需要的权限的时候，可以使用这个功能切换到一个有权限的角色


##7、
dbname
链接的数据库名，也是要备份的数据库名，对应$PGDATABASE


##8、
-a 或 --data-only
只输出数据，不输出数据定义的sql语句。纯文本格式的时候有效


##9、
-b 或 --blobs
转储中是否包含大对象，除非指定了选择性转储的选项 --schema，--table，--schema-only开关，
否则默认会转储大对象。用于选择性转储时控制是否转储大对象


##10、
-c 或 --clean
输出的脚本中是否生成清理该数据库对象语句（如drop table）。纯文本格式时候有意义


##11、
-C 或 --create
指定输出脚本是否输出一条create database 语句和链接到该数据库的语句，一般在备份的源数据库
和恢复的模板数据库的名称一致时使用这个参数
只针对纯文本有意义


##12、
-E encoding 或 --encoding=encoding
指定字符集编码创建转储，默认转储时依据数据库编码创建的，对应环境变量$PGCLIENTENCOGING


##13、
-f file 或 --file=file
指定数据的文件，否则输出到标准输出


##14、
-F format 或 --format=format
format=p|c|t
p:plain,纯文本模式，p是默认值
c：custom，用适合pg_restore使用的自定义格式输出并归档。比较灵活，允许手动查询，并且在恢复的
           时候重排归档项的顺序，默认使用压缩
t：tar，用适合pg_restore的tar格式输出并归档。同样允许手动选择和重排归档项的顺序，但是重排后
        一些限制。比如：表数据项的相关顺序在恢复时不能更改，同时tar格式不支持压缩，并且对独立
       表空间大小限制为8GB


##15、
-n schema 或 --schema=schema
转储匹配的schema的模式内容，包括模式本身以及其中包含的对象。如果没有不使用该选项，所有目标库的非
系统模式都会被转储。 可以使用个多-n指定多个schema。同样schema参数将按psql的\d命令被解释为匹配
模式，因此可以使用通配符匹配多个模式。 使用通配符的时候，最好使用引号进行界定，防止shell将通配符
进行扩展
（指定了-n，pg_dump将不会转储模式所依赖的其它数据库对象，因此无法保证转储出来的内容能够在别的数据库
恢复成功。非模式对象（比如大对象），不会再指定-n的时候被转储出来，可以使用-b来明确要求转储大对象）


##16、
-N schema 或 --exclude-schema=schema
意义跟-n相反，用法雷同
同时指定，只转储匹配-n而不匹配-N的模式


##17、
-o 或 --oids
是否为每个表都输出对象标示（OID）。当应用需要OID字段（比如用于外键约束）时，可以使用-o，否则不应该
使用


##18、
-O 或 --no-owner
针对纯文本转储。 表示不把对象的所有权设置为对应源数据库中的owner。
pg_dump默认发出alter owner 或 set session authorization语句来设置创建数据库对象的所有者。如果这些脚本
将来没有被超级用户运行，会导致恢复失败，-O为了让该脚本可以被任何用户使用


##19、
-s 或 --shcema-only
只输出对象定义模式，不输出数据。


##20、
-S username 或--superuser=username
指定关闭触发器时需要用到的超级用户名。它只在使用了--disable-triggers时才有用处。
一般最好不要使用-S，而是用超级用户启动生成的脚本


##21、
-t table 或 --table=table
指定导出的表，视图，序列。可以使用多个-t匹配多个表，使用了-t之后，-n和-N就失效了，被-t选中的表无视-n/N
被导出，而除了该表之外的所有对象都不会被转储
（-t只导出该表。而依赖不会被导出，可能导致不能正确恢复）


##22、
-v 或 --verbose
执行过程打印详细信息，包括对象评注，转储文件的启停时间和进度信息（输出到标准错误输出）


##23、
-V 或 --version
版本信息


##24、
-x 或者 --no-privileges 或 --no-acl
禁止转储访问权限（grant/revoke）


##25、
-Z 0..9 或 --compress=0..9
压缩级别，0表示不压缩。对于自动以归档格式，指定压缩的单个表数据段，默认使用中等压缩水平。 对于纯文本，设置非
0值，会导致全部输出文件被压缩。 模式是不压缩。 tar格式不支持压缩


##26、
--binary-upgrade


##27、
--inserts
把默认的copy转储方式改成insert，会导致恢复起来比较慢，可以用来把数据加载到非pg库。它为每一行生成一个insert命令
。当恢复遇到错误时，仅丢一条数据，要求表的列顺序完全一致


##28、
--column-inserts 或 --attribute-inserts
同--inserts，不过它可以支持列顺序的不同的导入导出


##29、disable-dollar-quoting
关闭使用美元符界定函数体。强制函数体内容使用SQL标准的字符串语法的引号包围


##30、
--disable-triggers
针对纯文本有意义，和创建只有数据的转储有关。指定在做恢复的时候，临时关闭目标表上的触发器命令。如果在表上
有参照完整性检查或者触发器，并且恢复数据的时候不想重载他们，指定该选项
    发出该选项的必须是超级用户，执行转储的脚本时，应该使用-S执行一个超级用户的命令


##31、
--lock-wait-timeout=timeout
不要永远等待在开始转储时获取共享表锁。如果不指定，超过timeout时间，转储就会失败


##32、
--no-tablespaces
只针对纯文本格式，表示不输出命令来选择表空间。在该选项内，转储期间若表空间是默认的，将会创建所有的对象


##33、
--use-set-session-authorization
输出符合SQL标准的set session authorization命令而不是alter owner命令。这样转储更加符合标准，但是如果转储文件中对象
的历史问题，可能导致不能正常恢复。并且必须有超级用户权限再能使用该参数。 alter owner需要的权限更小。




##############################################################################


pg_restore:


##1、
基本项跟pg_dump相同：
-h  host  或 --host=host
-p  port 或 --port=port
-U username 或 --username=username
-w 或 --no-password
-W 或 --password
--role=rolename
不同点：
-d dbname 或 --dbname=dbname 指定数据库
pg_dump默认最后一个没有任何修饰的参数为要链接的目标库
pg_restore默认最后一个不带任何修饰的参数是转储文件名


##2、
filename
要恢复的文件位置，没有声明则使用标准输入


##3、
-a 或 --data-only
只恢复数据，而不恢复表模式


##4、
-c 或 --clean
创建数据库对象前先清理（删除）他们


##5、
-C  或 --create
恢复前先创建，如果使用-C和 -d在一起的数据库名只是用于发出最初的create database命令，
所有的数据都恢复到名字出现在归档中的数据库。


##6、
-d dbname 或 --dbname=dbname
指定数据库


##7、
-e 或 --exit-on-error
如果再向数据库发生sql命令时遇到错误，则退出。默认是继续执行，在结束时显示错误数。


##8、
-f filename 或 --file=filename
指定生成的脚本的输出文件，或者出现-l选项时用于列表的文件，默认是标准输出


##9、
-F format 或 --format=format
指定备份文件的格式。pg_restore可以自动判断，可选t|c


##10、
-I index 或 --index=index
只恢复命名的索引


##11、
-j number-of-jobs 或 --jobs=number-of-jobs
在运行加载数据，创建索引或者约束的时候，可以使用多个并发来完成，可以缩短恢复时间
只有自定义格式才支持该选项，不能跟--single-transaction选项一起使用


##12、
-l 或 --list
列出归档内容，该操作的输出可以用作输入的-L选项。注意如果过滤选项（-n -t）与-l一起
他们将显示列出的项


##13、
-L list-file 或 --use-list=list-file
仅恢复那些在list-file中列出的归档元素，按照他们出现的顺序恢复
可以先运行 pg_restore -l命令然后编辑结果作为-L的输入文件


##14、
-n namespace 或 --schema=schema
只恢复指定的模式的数据或者定义，跟-t一起使用只恢复单表


#15、
-O 或--on-owner
不输出设置和最初数据库对象权限匹配的命令。默认情况下，pg_restore发出alter owner
或者set session authoriazaion语句，来设置创建出来的模式元素的所有者权限。如果
最初的数据库连接不是超级用户，那么这些语句会失败。 使用-O，任何用户都可以，并且
这个用户将拥有创建出来的所有对象


##16、
--no_tablespaces
表示不输出命令来选择表空间，若使用了该项，恢复数据时，所有对象被创建到默认表空间中，
而不是原来的表空间


##17、
-P function-name（argtype[,...]） 或 --function=function-name（argtype[,...]）
恢复指定的命名函数，注意拼写，应该跟备份的内容列表完全一致


##18、
-s 或 --schema-only
只恢复表结构（数据定义），不恢复数据，序列的当前值也不会得到恢复


##19、
-S username 或 --superuser=username
设置关闭触发器时声明的超级用户名，只有跟--disable-triggers配合才有作用


##20、
-t table 或 --table=table
指定恢复的表


##21、
-T trigger 或 --trigger=trigger
指定恢复的触发器


##22、
-v 或者 -verbose
详细输出模式


##23、
-V 或 --version
输出版本号


##24、
-x 或 --no-privileges 或 --no-acl
禁止恢复访问权限（grant/revoke）


##25、
--disable-triggers
仅仅在恢复数据的时候有用，指定在加载数据时临时关闭触发器（完整性检查约束或者触发器）


##26、
--use-set-session-authorization
输出SQL标准的set session authorization 命令，而不是alter owner命令
可以是转储和标准兼容的更好，但是可能导致转储不能正确恢复


##27、
--no-data-for-failed-tables
默认情况下，创建表的命令失败，表中的数据仍然会被恢复，使用该选项错误的表的数据将跳过恢复操作，
该选项仅仅在直接向一个数据库恢复数据时有效，sql脚本输入无效


##28、
-l 或 --single-transaction
指定作为一个单独事务来存储（使用begin/commit封装命令）确保要么所有的都成功，要么都失败
它蕴含了 --exit-on-error


################################################################
最佳实践：


1、数据库导出导入


1）导出数据库文本文件，不带create db语句。可以导入任何库
pg_dump -h 127.0.0.1 -p 7002  test_dump > 2.sql
pg_dump -h 127.0.0.1 -p 7002 test_dump -f 2.sql
恢复到hehe中
psql -h 127.0.0.1 -p 7002 -d hehe < 2.sql 


2）导出数据库文本文件，带create db语句。
pg_dump -h 127.0.0.1 -p 7002 -C test_dump > 3.sql
恢复到同样名字的库中：
psql -h 127.0.0.1 -p 7002  < 3.sql 


3）导出自定义格式：
pg_dump -h 127.0.0.1 -p 7002 -Fc  test_dump > 5.sql
导入hehe库
pg_restore  -h 127.0.0.1 -p 7002 --no-data-for-failed-tables  -d hehe  5.sql 




2、表的导入导出：
1）文本导出导入
pg_dump -h 127.0.0.1 -p 7002  -t t   test > 8.sql
恢复：
psql -h 127.0.0.1 -p 7002   monitor <8.sql 
2）自定义格式：
pg_dump -h 127.0.0.1 -p 7002 -Fc  -t t   test > 1.sql
导入到test：
pg_restore  -h 127.0.0.1 -p 7002 --no-data-for-failed-tables  -d test  1.sql 
导入到monitor：
pg_restore  -h 127.0.0.1 -p 7002 --no-data-for-failed-tables  -d monitor  1.sql 
3)多表通配符导出：
pg_dump -h 127.0.0.1 -p 7002 -Fc  -t 't*'   test > 1.sql
pg_restore  -h 127.0.0.1 -p 7002 -d test2 1.sql 


3 存储过程，函数，触发器的导入导出
1)导出表以外的数据库对象
pg_dump -h 127.0.0.1 -p 7002 -T '*'  test2 > 4.sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20625855/viewspace-1845992/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20625855/viewspace-1845992/