本示例以Windows环境下的HighGo Database v4.1.1演示为例:
archive_mode = on
archive_directory = 'D:\\BACKUP\\ARCHIVELOG'
wal_level = archive
因此环境为单机环境的备份恢复,切勿将hot_standby设置为on,否则在下面的步骤中启动数据库可能出现报错。
2、启动数据库
从“控制面板-管理工具-服务”中选择hgdb-se4.1.1并启动
3、基于文件级别的在线热备份
a.模拟测试数据
CREATE TABLE tab1(a1 int);
INSERT INTO tab1 VALUES (1),(2),(3),(4);
b.备份
highgo=# SELECT now();
now
-------------------------------
2017-09-03 14:00:52.584023+08
(1 行记录)
highgo=# select pg_start_backup('bak_2017-09-03 14:00:52');
pg_start_backup
-----------------
0/B000028
(1 行记录)
c.打包data
将data目录复制到备份目录D:\backup下
d.结束并切换归档
highgo=# select pg_stop_backup();
NOTICE: 00000: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/C000050
(1 行记录)
highgo=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/D000000
(1 行记录)
这时会在data目录下产生一个backup_label的文件,记录了可以查看内容有checkpoint时间,基础备份的开始和结束时间,以及标签名称等。如下:
START WAL LOCATION: 0/B000028 (file 00000001000000000000000B)
CHECKPOINT LOCATION: 0/B000028
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2017-09-03 14:01:11 HKT
LABEL: bak_2017-09-03 14:00:52
说明:以上b/c/d三步中的备份步骤也可以使用pg_basebackup方式完成。
e.再次插入数据
highgo=# CREATE TABLE tab2(a1 int);
CREATE TABLE
highgo=# INSERT INTO tab2 VALUES (1),(2),(3);
INSERT 0 3
4、模拟毁坏并进行恢复
a.停止数据库服务
从“控制面板-管理工具-服务”中选择hgdb-se4.1.1并停止
b.模拟数据库毁坏
删除D:\HighGo\Database\4.1.1\data目录
c.恢复备份文件
将d:\backup\data目录拷贝到D:\HighGo\Database\4.1.1下
d.删除pg_xlog文件夹下的所有文件
在pg_xlog下创建目录archive_status
e.创建recovery.conf文件添加如下内容
archive_directory = 'D:\\backup\\archivelog'
recovery_target_time='2017-09-03 14:00:52'
recovery_target_time可以设置恢复到的时间点,如果不加此选项则会恢复到之前接收到的最后一个归档文件。
f.重启数据库查看恢复结果
从“控制面板-管理工具-服务”中选择hgdb-se4.1.1并启动
数据库后台日志输出恢复信息如下:
LOG: 00000: database system was interrupted while in recovery at log time 2017-09-03 14:01:11 HKT
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG: 00000: starting point-in-time recovery to 2017-09-03 14:00:52+08
LOG: 00000: restored log file "00000001000000000000000B" from archive
LOG: 00000: redo starts at 0/B000098
LOG: 00000: restored log file "00000001000000000000000C" from archive
LOG: 00000: consistent recovery state reached at 0/C000050
LOG: 00000: restored log file "00000001000000000000000D" from archive
LOG: 00000: recovery stopping before commit of transaction 621, time 2017-09-03 14:03:31.236301+08
LOG: 00000: redo done at 0/D0171B8
LOG: 00000: selected new timeline ID: 2
LOG: 00000: archive recovery complete
LOG: 00000: MultiXact member wraparound protections are now enabled
日志: 00000: 数据库系统准备接受连接
LOG: 00000: autovacuum launcher started
FATAL: 28000: role "WIN-MIRBE4NEBAU$" does not exist
启动后验证恢复的表:
C:\Users\Administrator>psql -d highgo
psql (4.1.1)
PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production
输入 "help" 来获取帮助信息.
highgo=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------------+------+--------+---------------
oracle_catalog | dual | 视图 | Administrator
public | tab1 | 数据表 | Administrator
(2 行记录)
在此仅恢复到第一次模拟数据(即3.a步)。
g.下面为在recovery.conf中未添加recovery_target_time的结果:
highgo=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------------+------+--------+---------------
oracle_catalog | dual | 视图 | Administrator
public | tab1 | 数据表 | Administrator
public | tab2 | 数据表 | Administrator
(3 行记录)
highgo=# SELECT * from tab2;
a1
----
1
2
3
(3 rows)
注:结束后,recovery.conf会改名变成recovery.done。
如果恢复过一次,并设置了恢复时间点,下次直接修改recovery_target_time,不会发生效果。
数据库安装目录为:D:\HighGo\Database\4.1.1
archive_mode = on
archive_directory = 'D:\\BACKUP\\ARCHIVELOG'
wal_level = archive
因此环境为单机环境的备份恢复,切勿将hot_standby设置为on,否则在下面的步骤中启动数据库可能出现报错。
2、启动数据库
从“控制面板-管理工具-服务”中选择hgdb-se4.1.1并启动
3、基于文件级别的在线热备份
a.模拟测试数据
CREATE TABLE tab1(a1 int);
INSERT INTO tab1 VALUES (1),(2),(3),(4);
b.备份
highgo=# SELECT now();
now
-------------------------------
2017-09-03 14:00:52.584023+08
(1 行记录)
highgo=# select pg_start_backup('bak_2017-09-03 14:00:52');
pg_start_backup
-----------------
0/B000028
(1 行记录)
c.打包data
将data目录复制到备份目录D:\backup下
d.结束并切换归档
highgo=# select pg_stop_backup();
NOTICE: 00000: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/C000050
(1 行记录)
highgo=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/D000000
(1 行记录)
这时会在data目录下产生一个backup_label的文件,记录了可以查看内容有checkpoint时间,基础备份的开始和结束时间,以及标签名称等。如下:
START WAL LOCATION: 0/B000028 (file 00000001000000000000000B)
CHECKPOINT LOCATION: 0/B000028
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2017-09-03 14:01:11 HKT
LABEL: bak_2017-09-03 14:00:52
说明:以上b/c/d三步中的备份步骤也可以使用pg_basebackup方式完成。
e.再次插入数据
highgo=# CREATE TABLE tab2(a1 int);
CREATE TABLE
highgo=# INSERT INTO tab2 VALUES (1),(2),(3);
INSERT 0 3
4、模拟毁坏并进行恢复
a.停止数据库服务
从“控制面板-管理工具-服务”中选择hgdb-se4.1.1并停止
b.模拟数据库毁坏
删除D:\HighGo\Database\4.1.1\data目录
c.恢复备份文件
将d:\backup\data目录拷贝到D:\HighGo\Database\4.1.1下
d.删除pg_xlog文件夹下的所有文件
在pg_xlog下创建目录archive_status
e.创建recovery.conf文件添加如下内容
archive_directory = 'D:\\backup\\archivelog'
recovery_target_time='2017-09-03 14:00:52'
recovery_target_time可以设置恢复到的时间点,如果不加此选项则会恢复到之前接收到的最后一个归档文件。
f.重启数据库查看恢复结果
从“控制面板-管理工具-服务”中选择hgdb-se4.1.1并启动
数据库后台日志输出恢复信息如下:
LOG: 00000: database system was interrupted while in recovery at log time 2017-09-03 14:01:11 HKT
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG: 00000: starting point-in-time recovery to 2017-09-03 14:00:52+08
LOG: 00000: restored log file "00000001000000000000000B" from archive
LOG: 00000: redo starts at 0/B000098
LOG: 00000: restored log file "00000001000000000000000C" from archive
LOG: 00000: consistent recovery state reached at 0/C000050
LOG: 00000: restored log file "00000001000000000000000D" from archive
LOG: 00000: recovery stopping before commit of transaction 621, time 2017-09-03 14:03:31.236301+08
LOG: 00000: redo done at 0/D0171B8
LOG: 00000: selected new timeline ID: 2
LOG: 00000: archive recovery complete
LOG: 00000: MultiXact member wraparound protections are now enabled
日志: 00000: 数据库系统准备接受连接
LOG: 00000: autovacuum launcher started
FATAL: 28000: role "WIN-MIRBE4NEBAU$" does not exist
启动后验证恢复的表:
C:\Users\Administrator>psql -d highgo
psql (4.1.1)
PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production
输入 "help" 来获取帮助信息.
highgo=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------------+------+--------+---------------
oracle_catalog | dual | 视图 | Administrator
public | tab1 | 数据表 | Administrator
(2 行记录)
在此仅恢复到第一次模拟数据(即3.a步)。
g.下面为在recovery.conf中未添加recovery_target_time的结果:
highgo=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------------+------+--------+---------------
oracle_catalog | dual | 视图 | Administrator
public | tab1 | 数据表 | Administrator
public | tab2 | 数据表 | Administrator
(3 行记录)
highgo=# SELECT * from tab2;
a1
----
1
2
3
(3 rows)
注:结束后,recovery.conf会改名变成recovery.done。
如果恢复过一次,并设置了恢复时间点,下次直接修改recovery_target_time,不会发生效果。