postgres逻辑备份恢复

学习环境：

centos6.5  64+postgres9.5.1

一简要说明

工具pg_dump(pg_dumpall)，psql, pg_restore。

一个数据库(或者部分对象)导出为脚本文本文件，用psql恢复。

一个数据库(或者部分对象)导出为归档文件，用pg_restore恢复。

参考资料：

http://www.postgresql.org/docs/9.5/static/app-pgdump.html

http://www.runoob.com/manual/PostgreSQL/app-pgdump.html

二语法温故

2.1 pg_dump --help

Usage:

  pg_dump [OPTION]... [DBNAME]

General options:

  -f, --file=FILENAME         导出文件路径

  -F, --format=c|d|t|p          导出文件格式，默认p为文本（如果是定制模式，默认启用压缩）

  -j, --jobs=NUM                执行并行个数

  -v, --verbose                   详细模式

  -V, --version                   输出版本信息，然后退出

  -Z, --compress=0-9         压缩级别

  --lock-wait-timeout=TIMEOUT   在等待表锁超时后操作失败

  -?, --help                    显示帮助，然后退出

Options controlling the output content:

  -a, --data-only              只导出数据，不输出模式（数据库对象定义）

  -b, --blobs                  导出时包括大对象

  -c, --clean                  创建数据库对象前先删除

  -C, --create                 导出文件中有创建当前备份数据库的语句

  -E, --encoding=ENCODING      指定字符集编码创建转储

  -n, --schema=SCHEMA          导出指定模式（如果没有指定，那么非系统模式都会被转储）

  -N, --exclude-schema=SCHEMA  排除指定模式

  -o, --oids                   为每个表都输出对象标识（OID）

  -O, --no-owner            在明文格式中, 忽略恢复对象所属者                              

  -s, --schema-only         只导出模式定义（数据库对象定义），不包括数据

  -S, --superuser=NAME  在明文格式中,声明关闭触发器时需要用到的超级用户名

  -t, --table=TABLE          指定导出的表（可能不同模式里面有多个同名表，可以同时指定-n模式）

  -T, --exclude-table=TABLE    排除指定的表

  -x, --no-privileges          不导出权限

  --binary-upgrade            只能由升级工具使用

  --column-inserts            以带有列名的INSERT命令形式转储数据

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

  --disable-triggers           在只恢复数据的过程中禁用触发器

  --enable-row-security        enable row security (dump only content user has

                               access to)

  --exclude-table-data=TABLE   不导出指定表数据

  --if-exists                  删除对象语句带if-exists命令

  --inserts                   以INSERT形式转储数据 ，而不是COPY

 --no-security-labels          不转储安全标签的分配

  --no-synchronized-snapshots  在并行工作集中不使用同步快照

  --no-tablespaces             不转储表空间分配信息

  --no-unlogged-table-data    不转储不记日志表的数据

  --quote-all-identifiers      所有标识符加引号，即使不是关键字

  --section=SECTION       只转储命名的章节(数据前, 数据, 及 数据后)

  --serializable-deferrable  使用一个可串行化的事务，以保证使用的快照和稍后的数据库状态一致；

 --snapshot=SNAPSHOT  使用给定的snapshot

  --use-set-session-authorization  使用 SET SESSION AUTHORIZATION命令代替ALTER OWNER 命令来设置所有权

Connection options:

  -d, --dbname=DBNAME      数据库

  -h, --host=HOSTNAME       主机

  -p, --port=PORT                  端口

  -U, --username=NAME       连接用户

  -w, --no-password               永远不提示输入口令

  -W, --password                  强制口令提示 (自动)

  --role=ROLENAME            在转储前运行SET ROLE

2.2 pg_restore  --help

[postgres@pg1 ~]$ pg_restore  --help

pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:

  pg_restore [OPTION]... [FILE]

General options:

  -d, --dbname=NAME         连接的数据库

  -f, --file=FILENAME          输出文件名

  -F, --format=c|d|t             备份文件格式（会自动识别）

  -l, --list                    打印归档文件的 TOC 概述

  -v, --verbose             详细模式

  -V, --version             输出版本，然后退出

  -?, --help                 显示帮助，然后退出

Options controlling the restore:

  -a, --data-only           只恢复数据，不恢复模式（数据库对象定义）

  -c, --clean                 恢复数据库对象之前，先删除

  -C, --create                恢复数据库的时候，自定义先创建某个数据库

  -e, --exit-on-error         遇到错误退出，默认为继续

  -I, --index=NAME         恢复索引

  -j, --jobs=NUM              指定并行作业数

  -L, --use-list=FILENAME      指定文件名恢复

  -n, --schema=NAME            恢复指定的用户模式

  -O, --no-owner               不恢复对象的所属

  -P, --function=NAME(args)    恢复指定的函数

  -s, --schema-only            只恢复模式(数据库对象定义)，不包含数据

  -S, --superuser=NAME     使用指定的超级用户来禁用触发器

  -t, --table=NAME             恢复指定的表

  -T, --trigger=NAME           恢复指定的触发器

  -x, --no-privileges              不恢复权限

  -1, --single-transaction     作为单个事物

  --disable-triggers             在只恢复数据的过程中禁用触发器

  --enable-row-security        启用行安全

  --if-exists                  删除对象的语句中带if-exists

  --no-data-for-failed-tables 对那些无法创建的表不进行数据恢复

  --no-security-labels         不恢复安全标签信息

  --no-tablespaces             不恢复表空间的分配信息
  --section=SECTION         恢复命名的章节(数据前、数据及数据后)

  --use-set-session-authorization 使用 SET SESSION AUTHORIZATION命令代替ALTER OWNER 命令来设置所有权。

Connection options:

  -h, --host=HOSTNAME        主机

  -p, --port=PORT                   端口

  -U, --username=NAME        连接用户

  -w, --no-password          永远不提示输入口令

  -W, --password             强制口令提示 (自动)

  --role=ROLENAME            在转储前运行SET ROLE

选项 -I, -n, -P, -t, -T, 以及 --section 可以组合使用和指定

多次用于选择多个对象。

2.3 psql  --help

[postgres@pg1 ~]$ psql  --help

psql is the PostgreSQL interactive terminal.

Usage:

  psql [OPTION]... [DBNAME [USERNAME]]

General options:

  -c, --command=COMMAND    声明 psql 将执行一条查询字符串，然后退出。

  -d, --dbname=DBNAME         指定数据库名

  -f, --file=FILENAME      filename作为命令的语句源而不是交互式读入查询，执行文件路径与名称。

  -l, --list                        列出可用数据库名

  -v, --set=, --variable=NAME=VALUE设置psql 相关变量值 (e.g., -v ON_ERROR_STOP=1)

  -V, --version                      输出版本信息

  -X, --no-psqlrc                   不读取启动文件（系统的 psqlrc 或者~/.psqlrc 都不读取）。

  -1 ("one"), --single-transaction   以单个事物运行(if non-interactive)

  -?, --help[=options]        show this help, then exit

      --help=commands         list backslash commands, then exit

      --help=variables        list special variables, then exit

Input and output options:

  -a, --echo-all            标准输出打印打印从脚本的所有输入

  -b, --echo-errors        打印失败的命令

  -e, --echo-queries       把所有发送给服务器的查询同时也回显到标准输出。

  -E, --echo-hidden        回显由 \d 和其它反斜杠命令生成的实际查询。

 -L, --log-file=FILENAME    将会话日志发送到文件

  -n, --no-readline         取消命令行编辑

  -o, --output=FILENAME     查询输出重定向到文件filename，等效于\o命令，(or |pipe)

  -q, --quiet               安静地执行处理任务。(无其他信息, 只有结果)

  -s, --single-step        单步模式运行。每个查询在发往服务器之前都要提示用户。

  -S, --single-line         单行运行模式，这时每个命令都将由换行符结束，像分号那样。

Output format options:

  -A, --no-align            切换为非对齐输出模式，缺省输出模式是对齐的。

  -F, --field-separator=STRING 使用 separator 作为未对齐输出的域分隔符，(default: "|")

  -H, --html                 HTML 格式输出。等效于 \pset format html 或 \H 命令。

  -P, --pset=VAR[=ARG]       设置打印选项。 (see \pset command)

  -R, --record-separator=STRING 设置记录行分隔符，默认为每一行(default: newline)

  -t, --tuples-only          只打印行，等效于 \t 命令。

  -T, --table-attr=TEXT     设置HTML属性，声明放在HTMLtable标签里的选项。(e.g., width, border)

  -x, --expanded             打开扩展表格式模式。等效于 \x 命令。

  -z, --field-separator-zero  将区域分割设置为0

  -0, --record-separator-zero 将记录分割设置为0

 Connection options:

  -h, --host=HOSTNAME        主机名 (default: "local socket")

  -p, --port=PORT                   端口 (default: "5432")

  -U, --username=USERNAME    用户 (default: "postgres")

  -w, --no-password           从不提示密码

  -W, --password              强制提示密码 (should happen automatically)

三示例

3.1备份与恢复整库

（1）逻辑备份

//文本备份

pg_dump  -d test -h localhost -p 5432  -U dba -F p -f /pgdb/backup/test.out

//定制备份，允许对归档元素进行选取和重新排列， 缺省压缩；

pg_dump  -d test -h localhost -p 5432  -U dba -F c -f /pgdb/backup/test.bak

//tar方式

pg_dump  -d test -h localhost -p 5432  -U dba -F t -f /pgdb/backup/test.tar

//并行

pg_dump  -d test -h localhost -p 5432  -U dba -F d –j 4 -f /pgdb/backup/test.d

（2）逻辑恢复

create   database test;

pg_restore  -h localhost  -p  5432 -U dba  -d  test/pgdb/backup/test.d

//只恢复模式

pg_restore  -h localhost  -p  5432 -U dba -s – j 4 -d  test /pgdb/backup/test.d

//只恢复数据

pg_restore  -h localhost  -p  5432 -U dba -a – j 4 -d  test /pgdb/backup/test.d

3.2备份与恢复一个表

pg_dump -d test  -h localhost –p 5432  -U dba -F d -t emp4 -j 4 -f /pgdb/backup/emp4.d

pg_dump -d  test -h localhost -p 5432  -U  dba  -F c  -t emp4  -f /pgdb/backup/emp4.d

pg_restore  -h localhost  -p  5432 -U dba -s  -d test /pgdb/backup/emp4.d

pg_restore  -h localhost  -p  5432 -U dba -a  -d test /pgdb/backup/emp4.d

3.3备份与恢复模式

//导出指定模式

pg_dump  -d test -h localhost -p 5432  -U dba -F c -n  test -f /pgdb/backup/test.bak 

//恢复时没有这个模式，那么会自动创建

pg_restore  -h  localhost  -p  5432 -U dba  -d test /pgdb/backup/test.bak

//如果有模式，也有表,导入会报错模式存在，表存在，但是数据导入成功，且追加方式

pg_restore -h localhost  -p  5432 -U dba  -d test  /pgdb/backup/test.bak

//如果有模式，但是没有表, 导入会报错模式已经存在，但是数据导入成功

pg_restore  -h  localhost  -p  5432 -U dba  -d test  /pgdb/backup/test.bak

//导入时先删除表，然后创建表，数据导入

pg_restore  -h  localhost  -p  5432 -U dba  -d test  -c /pgdb/backup/test.bak

3.4其他

dump 所有public schema是以emp开始的表，但不包括emp1这个表

pg_dump -t 'public.emp*' -T 'public.emp1 test > db.sql

Dump 所有的 schemas 名字以east或 west 开头并以gsm结束的，但不包括名字中包含test的schema：

pg_dump -n 'east*gsm' -n 'west*gsm' -N'*test*' test > db.sql

注：pg_dump可以对针对单表或者多表进行备份
如：pg_dump  databasename–t tablename1 –t tablename2 > table.sql

3.5 pg_dumpall

 pg_dumpall 可以导出所有数据库，类似 pg_dump，但用得较少。

缺点：

(1)pg_dumpall 导出所有数据库到一个脚本文件，维护不方便;

(2)pg_dumpall 依次导出所有库，总耗时比并行pg_dump各个库要多;

(3)pg_dumpall 仅支持导出文件格式，还原时不能使用pg_restore 有效使用并行等参数。

优点：

pg_dumpall 在取全局对像时非常方便，例如取数据库上的表空间(tablespace)，用户(roles)等，这在数据库结构迁移时非常有用。