[概述]
备份是恢复的前提。不发生故障时,世界很太平,但发生故障时,如果不能顺利进行恢复,那将是一场噩梦!甚至可能对于企业是致命打击,这绝对不是危言耸听!
日常的备份有效性的检查就显得尤其重要,一个无效的备份集和没有备份是一样的,例如备份文件无法解压,或者存储备份的介质损坏等等。除了制定备份恢复策略,还应该制定一套定期、定时的恢复测试方案。我们在恢复测试中,应该考量恢复需要花费的时间,日常测试时,也应该记录和统计恢复花费的时间,如果恢复时间太长,还应该优化恢复方案,消除恢复瓶颈实现业务SLA要求等级。
PostgreSQL 只能向后恢复, 而不能前恢复。因此PostgreSQL恢复时,必须要搭配基础备份实施。
[增量备份]
启用WAL日志功能PostgreSQL增量备份是基于WAL日志实施的,它的开启与配置方法参考<PostgreSQL数据库系列之五:预写式日志WAL>。
启用功能核心参数
创建基础备份
从PostgreSQL 9.1版本开始有了pg_basebackup实用程序,使得创建基础备份更便捷,pg_basebackup用普通文件或创建tar包的方式进行基础备份,它在内部也是使用pg_start_backup和pg_stop_backup低级命令。
pg_basebackup备份脚本:
@echo offREM The script sets environment variables helpful for PostgreSQLSET PATH="C:\Program Files\PostgreSQL\11\bin";%PATH%SET PGDATA=D:\pgdata\11\dataSET PGDATABASE=postgresSET PGUSER=postgresSET PGPASSWORD=123456SET PGPORT=5432SET LOGDATE=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%SET LOGTIME=%time:~0,2%%time:~3,2%SET BAKPATH=D:\pgdata\11\backup\%LOGDATE%SET LOGFILE=D:\pgdata\11\backup\backuplog%LOGDATE%.txtif not exist %BAKPATH% (mkdir %BAKPATH% 2>> %LOGFILE% 1>&2echo Backup Start Time:%DATE:~0,4%%DATE:~5,2%%DATE:~8,2% %time:~0,2%%time:~3,2%%time:~6,2% >> %LOGFILE%pg_basebackup.exe -v -R -P --format=t --gzip --compress=4 -D %BAKPATH% 2>> %LOGFILE% 1>&2echo Backup End Time:%DATE:~0,4%%DATE:~5,2%%DATE:~8,2% %time:~0,2%%time:~3,2%%time:~6,2% >> %LOGFILE%) else (move %BAKPATH% %BAKPATH%_%LOGTIME% 2>> %LOGFILE% 1>&2move %LOGFILE% %BAKPATH%_%LOGTIME%\ 2>> %LOGFILE% 1>&2mkdir %BAKPATH% 2>> %LOGFILE% 1>&2echo Backup Start Time:%DATE:~0,4%%DATE:~5,2%%DATE:~8,2% %time:~0,2%%time:~3,2%%time:~6,2% >> %LOGFILE%pg_basebackup.exe -v -R -P --format=t --gzip --compress=4 -D %BAKPATH% 2>> %LOGFILE% 1>&2echo Backup End Time:%DATE:~0,4%%DATE:~5,2%%DATE:~8,2% %time:~0,2%%time:~3,2%%time:~6,2% >> %LOGFILE%)echo Delete Backup Files >> %LOGFILE%forfiles /p %RCPATH% /m * /d -%RCDATE% /c "cmd /c echo deleting @file ... && del /f @path" >> %LOGFILE%echo Delete Archive WAL Files >> %LOGFILE%forfiles /p %WALPATH% /m * /d -%RCDATE% /c "cmd /c echo deleting @file ... && del /f @path" >> %LOGFILE%
pg_basebackup核心参数解释:
效果展示:
文件说明:
[恢复]
恢复到最近的时间点—构建测试环境—
:# 创建测试表
create table tbl (
id SERIAL PRIMARY KEY,
ival INT NOT NULL DEFAULT 0,
description TEXT,
create_time TIMESTAMPTZ NOT NULL DEFAULT now()
);
:# 初始化测试数据
INSERT INTO tbl (ival) VALUES (1);
:# 查询表数据状态
SELECT id,ival,description,created_time FROM tbl;
:# 手工切换WAL日志
SELECT pg_switch_wal();
:# 初始化测试数据
INSERT INTO tbl (ival) VALUES (2);
:# 查询表数据状态
SELECT id,ival,description,created_time FROM tbl;
—构建测试环境—
关闭服务
迁移故障数据库的数据目录
※1 有空间的情况之下,我们尽可能不要删除源数据保障自己还有活路,不至于删库跑路!
※2 注意用户自定义表空间也需要进行迁移的。
创建数据目录并解压使用pg_basebackup创建的基础备份
※1 用户自定义表空间必须要自行将其目录结构恢复到指定的目录里。
创建recovery.conf文件并进行配置
restore_command = 'copy "D:\\pgdata\\11\\archive_wals\\%f" "%p"'recovery_target_timeline = 'latest'
重新启动服务
验证恢复数据
日志信息
-12-09 01:08:52.706 HKT [2408] 日志: 数据库系统中断;上一次的启动时间是在-12-08 23:51:11 HKT-12-09 01:08:53.478 HKT [2408] 日志: 开始归档恢复-12-09 01:08:53.655 HKT [2408] 日志: 从归档中恢复日志文件 "000000010000000000000019"-12-09 01:08:53.725 HKT [2408] 日志: redo 在 0/19000028 开始-12-09 01:08:53.741 HKT [2408] 日志: 在0/19000130上已到达一致性恢复状态-12-09 01:08:53.750 HKT [1036] 日志: 数据库系统准备接受只读请求的连接-12-09 01:08:53.900 HKT [2408] 日志: 从归档中恢复日志文件 "00000001000000000000001A"-12-09 01:08:54.042 HKT [2408] 日志: 从归档中恢复日志文件 "00000001000000000000001B"-12-09 01:08:54.108 HKT [2408] 日志: redo 在 0/1B0001F0 完成-12-09 01:08:54.108 HKT [2408] 日志: 上一次完成事务是在日志时间-12-09 00:24:23.682133+08完成的.-12-09 01:08:54.223 HKT [2408] 日志: 从归档中恢复日志文件 "00000001000000000000001B"-12-09 01:08:54.302 HKT [2408] 日志: 已选择的新时间线ID:5-12-09 01:08:54.371 HKT [2408] 日志: 归档恢复完毕-12-09 01:08:54.780 HKT [1036] 日志: 数据库系统准备接受连接
※ 每次恢复都会生成一条新的时间线,如果正常启动数据库并不是你所需要的数据,则你需要正确的判断数据所在的时间线。
恢复到指定时间点
recovery.conf配置参考
restore_command = 'copy "D:\\pgdata\\11\\archive_wals\\%f" "%p"'recovery_target_timeline = 2recovery_target_time = '-12-08 18:25:00.185701+08'
※1 此处是第二条时间线中的WAL日志中查找。
恢复到还原点
有时候,我们会希望将数据库恢复到某一个重要事件发生之前的状态,例如MRP动作之前、财务盘点动作之前。这种情况可以在重要事件发生时创建一个还原点,通过基础备份和归档恢复到时间发生之前的状态。
还原点创建方法:
SELECT pg_create_restore_point('restore_point_test');
recovery.conf配置参考
restore_command = 'copy "D:\pgdata\11\archive_wals\%f" "%p"'recovery_target_name = 'restore_point_test'
恢复到指定事务
recovery.conf配置参考
restore_command = 'copy "D:\\pgdata\\11\\archive_wals\\%f" "%p"'recovery_target_xid = 561
[SQL转储操作]
SQL转储可以将PG数据库中的某些表的数据迁移到其他关系型数据库中。
SQL转储备份
pg_dump -Fp -a --insert --column-inserts -t tbl -p 5432 mydb > dump.sql
参数说明:
pg_dumpall -r -p 5432
SQL转储恢复
:# 当转储的格式是纯文本形式时,使用psql运行转储出的SQL文本即可。
psql -p 5432 mydb < dump.sql
:# 当转储的格式指定为自定义格式时,需要使用pg_restore命令进行恢复。
pg_dump -Fc -p 5432 mydb > dump.datpg_restore -p 5432 -d mydb dump.dat
补充信息:时间线概述
时间线是这样的,无论何时,一个恢复完成后,会创建一个新的时间线来标识此次恢复之后产生的WAL日志。时间线的id号是WAL日志文件名字的一部分,因此不会覆盖其他时间线上的WAL日志文件。
每次创建一个新的时间线时,PostgreSQL会创建一个新的时间线历史文件,后缀为.history。历史文件会标识此时间线是什么时候从那个时间线分支而来的。
有了时间线历史文件,PostgreSQL就可以在含有多个时间线的归档文件中找到正确的WAL归档日志。虽然时间线看起来的确很高能,但是无论如何也不可能恢复到制作基础备份之前的时间。
新时间线创建场景
新的时间线会在什么情况下出现呢?
即时恢复(PITR)
设置好recovery.conf文件后,启动数据库,将会产生新的timeline,而且会生成一个新的history文件。
备库抢占角色()
搭建一个PG主备,然后停止主库,在备库机器执行:
pg_ctl promote –D $PGDATA
这时候备库将会升为主备,同时产生一个新的timeline,同样生成一个新的history文件。
history文件
每次创建一个新的时间线,PostgreSQL都会创建一个“时间线历史”文件,文件名类似.history,它里面的内容是由原时间线history文件的内容再追加一条当前时间线切换记录。