PostgreSQL备份

 备份与恢复

• postgresql自带了两个备份工具：

?

1 2	pg_dump：可备份一个指定的database pg_dumpall：可一次性备份所有database数据以及系统全局数据

 使用pg_dump备份

?

1 2	pg_dump支持精确指定要备份的表、schema、和database 备份文件可以是SQL文件格式、自定义压缩格式或者tar包格式

•  pg_dump 常用选项

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

## 通用选项： -f, --file=FILENAME          输出文件名或目录名 -F, --format=c|d|t|p         输出文件格式（自定义、目录格式、tar包格式、纯文本） -j, --jobs=NUM               使用多个并行作业进行转储 -v, --verbose                详细信息模式   ## 详细模式： -b, --blobs                  在转储中包括大对象 -c, --clean                  在重新创建之前，先清除（删除）数据库对象 -C, --create                 在转储中包括命令,以便创建数据库（包括建库语句，无需在导入之前先建数据库） -n, --schema=SCHEMA          只转储指定名称的模式 -N, --exclude-schema=SCHEMA  不转储已命名的模式 -t, --table=TABLE            只转储指定名称的表 --column-inserts             以带有列名的INSERT命令形式转储数据   ## 控制输出内容选项： -d, --dbname=DBNAME          数据库名 -h, --host=HOSTNAME          数据库服务器的主机名或套接字目录 -p, --port=PORT              数据库服务器的端口号 -U, --username=NAME          以指定的数据库用户联接 -w, --no-password            永远不提示输入口令 -W, --password               强制口令提示 (自动) --role=ROLENAME              在转储之前执行SET ROLE命令

• 备份某个database，备份结果以自定义压缩格式输出：

?

1	pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

• 备份某个database，备份结果以SQL文本格式输出，输出结果要包含CREATE DATABASE 语句：

?

1	pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

• 备份某个database中所有名称以"pay"开头的表，备份结果以自定义压缩格式输出：

?

1	pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb

• 备份某个database中hr和payroll这两个schema中的所有数据，备份结果以自定义压缩格式输出：

?

1	pg_dump -h localhost -p 5432 -U someuser -F c -b -v -n hr -n payroll -f hr.backup mydb

• 备份某个database中除public schema中的数据以外的所有数据，备份结果以自定义压缩格式输出：

?

1	pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public -f all_sch_except_pub.backup mydb

• 将数据备份为SQL文本文件，且生成INSERT语句，该文件可以用于低版本的PostgreSQL或其它支持SQL的非PostgreSQL数据库

?

1	pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts -f select_tables.backup mydb

• 目录备份格式：备份时会先创建一个新目录，然后逐一将一个gzip格式的压缩文件和一个列出所有包含结构的文件填充到该目录。

?

1	pg_dump -h localhost -p 5432 -U someuser -F d -f /root/test/ mydb

• 支持使用备份选项 --jobs（-j），会启动多个线程并行执行当前备份操作

?

1	pg_dump -h localhost -p 5432 -U someuser -j 4 -F d -f /root/test/ mydb

 使用pg_dumpall进行全库备份

?

1 2 3 4

pg_dumpall不支持导出SQL文件以外的其他格式 pg_dumpall可以将当前所有database数据导出为SQL文本 可以同时导出表空间定义和角色等全局对象 建议每天对角色和表空间定义等全局对象进行备份，但不建议每天使用pg_dumpall来备份全库数据

•  pg_dumpall 常用选项

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

## 通用选项： -f, --file=FILENAME          输出文件名 -v, --verbose                详细信息模式   ## 控制输出内容选项： -a, --data-only              仅转储数据，而不转储schema -g, --globals-only           仅转储全局对象，而不转储数据库 -r, --reles-oly              仅转储角色，而不转储数据库和表空间 -s, --schema-only            仅转储shema，而不转储数据 --inserts                    以INSERT命令（而非COPY命令）的形式转储数据   ## 连接选项： -d, --dbname=DBNAME          数据库名 -h, --host=HOSTNAME          数据库服务器的主机名或套接字目录 -l, --database=DBNAME        代替默认数据库 -p, --port=PORT              数据库服务器的端口号 -U, --username=NAME          以指定的数据库用户联接 -w, --no-password            永远不提示输入口令 -W, --password               强制口令提示 (自动) --role=ROLENAME              在转储之前执行SET ROLE命令

• pg_dumpall备份数据库

?

1	pg_dumpall -U postgres -h localhost -p 5432 -v -f all.backup

• pg_dumpall仅备份角色和表空间定义

?

1	pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

• pg_dumpall仅需备份角色定义而无需备份表空间

?

1	pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only

 数据恢复

• PostgreSQL支持以下两种数据恢复方式：

?

1 2	使用psql来恢复pg_dump或pg_dumpall工具生产的sql文本格式的数据备份 使用pg_restore工具来恢复由pg_dump工具生产的自定义压缩格式、tar包格式或者目录格式备份

 使用pgsql恢复SQL文本格式的数据备份

• 恢复一个SQL备份文件并忽略过程中可能发生的所有错误

?

1	psql -U postgres -f myglobals.sql

• 恢复一个SQL备份文件，如遇错误则立即停止恢复

?

1	psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql

• 将SQL文本中的数据恢复到某个指定的database

?

1	psql -U postgres -d mydb -f select_objects.sql

 使用pg_restore进行恢复 

•   pg_restore 常用参数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

## 通用选项： -d, --dbname=DBNAME          数据库名 -f, --file=FILENAME          输入文件名 -F, --format=c|d|t           输入文件格式（可以自动识别） -v, --verbose                详细信息模式   ## 详细模式： -a, --data-only              仅恢复数据，而不恢复schema -C, --create                 创建目标数据库 -j, --jobs=NUM               使用多个并行作业进行恢复 -s, --schema-only            仅恢复shema，而不恢复数据 --section=SECTION            恢复命名部分（包括三个部分：pre-data,data以及post-data。\                              data部分包含表记录数据、大对象数据以及序列的值；\                              post-data部分包含索引、触发器、规则和约束的定义；\                              pre-data部分包含除此外其他所有的对象定义）   ## 控制输出内容选项： -h, --host=HOSTNAME          数据库服务器的主机名或套接字目录 -p, --port=PORT              数据库服务器的端口号 -U, --username=NAME          以指定的数据库用户联接 -w, --no-password            永远不提示输入口令 -W, --password               强制口令提示 (自动) --role=ROLENAME              在转储之前执行SET ROLE命令

• 先创建目标数据库

?

1	CREATE DATABASE mydb;

• 执行恢复

?

1	pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

• 如果备份和恢复使用相同的database，则可以通过添加--create选项省去单独创建库的过程

?

1 2	pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup --create选项，必须先连接数据库，才能创建数据库，所有指定--dbname=postgres

• 9.2版本或更新的pg_restore支持--section选项，实现仅恢复表结构而不恢复表数据，创建模板数据库时可以使用这个方法

?

1 2 3

CREATE DATABASE mydb2;   pg_restore --dbname=mydb2 --section=pre-data --job=4 mydb.backup