ITPub博客

首页 > 数据库 > 数据库开发技术 > postgres的逻辑备份还原:pg_dump和pg_restore的使用

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

原创 数据库开发技术 作者:hotdog04 时间:2015-11-25 08:14:40 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-03-11

  • 博文量
    59
  • 访问量
    404538