postgresql学习笔记（五）备份与恢复

1、备份工具：pg_dump和pg_dumpall

pg_dump可备份一个指定的database

pg_dumpall可一次性备份所有database的数据及系统全局数据

2、pg_dump和pg_dumpall工具不支持在命令行选项中设定登录密码，因此为了便于执行自动任务，你需要在postgres操作系统帐号的home文件夹下创建一个密码文件.pgpass来存储密码；或者也可以用PGPASSWORD环境变量来设定密码。

3、如果你希望每天都进行备份，那么使用pg_dump比pg_dumpall更合适，因为前者支持精确指定要备份的表，schema和database，而后者不支持。

4、pg_dump可以将数据备份为SQL文本文件格式，也支持备份为用户定义压缩格式或者是TAR包格式，在数据恢复时，对压缩格式和TAR包格式的备份文件可以实现并行恢复，该特性是从8.4版开始支持的。

5、pg_dumpall工具可以将当前postgresql服务实例中所有database的数据都导出为SQL文本（pg_dumpall不支持导出SQL文本以外的其他格式），也可以同时导出表间定义和角色等全局对象。

6、pg_dump用法：

(1)备份某个database，备份结果以自定压缩格式输出 ：

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -f test.backup test

(2)备份某个database，备份结果以SQL文本方式输出，输出结果中需包括CREATE DATABASE语句；

pg_dump -h 127.0.0.1 -p 5432 -U postgres -C -F p -b -v -f test.sql test

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

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -t *.pay* -f pay.backup test

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

pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -n hr -n payroll -f hr.backup mydb

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

pg_dump -h 127.0.0.1 -p 5432  -U postgres -F c -b -v -N public -f all_sch_except_pub.backup mydb

(6)将数据备份为SQL文本文件，且生成的INSERT语句是带有字段名列表的标准格式，该文件可用于将数据导入到低于当前版本的PostgreSQL或者其他支持SQL的非PostgreSQL数据库中。

pg_dump -h 127.0.0.1 -p 5432  -U postgres -F p --column-inserts -f select_tables.backup mydb

(7)目录格式备份，解决了以其他备份格式时可能存在的单个文件大小超出操作系统限制的问题

pg_dump -h 127.0.0.1 -p 5432  -U postgres -F d -f /somepath/a_directory mydb

(8)目录格式并行备份

pg_dump -h 127.0.0.1 -p 5432  -U postgres -j 3 -Fd -f /somepath/a_directory mydb

7、pg_dumpall 建议每天都对角色和表空间定义等全局对象进行备份，但不建议每天都使用pg_dumpall来备份全库数据，因为pg_dumpall仅支持导出为SQL文本格式，而使用这种庞大的SQL文本备份来进行全库级别

的数据恢复是极其耗时的，所以一般只建议用pg_dumpall来备份全局对象而非全库数据，如果你一定要用pg_dumpall来备份全库数据的话，一般一个月执行一次就够了。

（1）仅备份角色和表空间定义

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

 (2)仅需备份角色定义而无需备份表空间，那么可以加上--roles-only选项

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

8、数据恢复方法

（1）使用psql来恢复pg_dump或者pg_dumpall工具生成的SQL文本格式的数据备份；

（2）使用pg_restore工具来恢复由pg_dump工具生成的自定义压缩格式，TAR包格式或者目录格式备份

9、使用psql恢复SQL文本格式的数据备份

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

psql -U postgres -f myglobals.sql

(2)恢复一个SQL备份文件，如遇任何错误则立好停止恢复

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

(3)将SQL文本中的数据恢复到某个指定的database:

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

10、使用pg_restore进行恢复

（1）在使用pg_restore执行恢复动作之前，请创建目标数据库

create database mydb;

 执行恢复：

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

 (2)如果备份和恢复时使用的database同名，则可以通过加--create选项省去单独建库的过程

pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup

 (3)恢复表结构而不恢复表数据

create database mydb2;
pg_resotre --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup