PostgreSQL 之 基于时间点的恢复（续）
基于时间点的恢复的三种方式中（即基于命名的还原点；基于目标时间还原点；基于XID事务还原点），日常恢复场景以基于目标时间还原点较为常见，本节主要通过实验验证基于时间还原点的恢复(postgresql V9.5.7)。
过程主要有以下四个部分：
基础备份
备份完成后执行数据变更
删除集群数据库
利用基础备份+归档日志执行指定时间还原点的恢复
注意：前提是需要将日志归档设置好
配置归档命令参考
vi $PGDATA/postgresql.conf
archive_mode = on
archive_command = ‘DATE=`date +%Y%m%d`; DIR=”/ssd/pg957/arch/$DATE”; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f’
wal_level = hot_standby
注解：
%p 表示xlog文件名$PGDATA的相对路径,
如pg_xlog/00000001000000190000007D
%f 表示xlog文件名,
如00000001000000190000007D
一、数据库基础备份
备份方式选择另外一种方式来完成，过程如下：
首先在数据库中以超级用户执行：
testdb=# select pg_start_backup(now()::text);
pg_start_backup
—————–
0/6000028
(1 row)
注：这个命令执行的目的是在$PGDATA中记录一个标签文件,
包含标签名now()::text,
以及执行这条指令的启动时间和WAL位置。
以下是查看备份标签内容
[postgres@hgdb01 data]$ pwd
/pgdata/data
[postgres@hgdb01 data]$ cat backup_label
START WAL LOCATION: 0/2C000060 (file 00000006000000000000002C)
CHECKPOINT LOCATION: 0/2C000098
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-02-01 16:08:10 CST
LABEL: 2018-02-01 16:08:09.686638+08
2.以用户postgres执行数据库文件备份命令：
rsync -acvz -L –exclude “pg_xlog” –exclude “pg_log” $PGDATA /dbbak/20180201_backup
注：以上备份命令排除了pg_xlog pg_log两个目录
附：sync命令是一个远程数据同步工具，可通过LAN/WAN快速同步多台主机间的文件。
rsync使用所谓的“rsync算法”来使本地和远程两个主机之间的文件达到同步，这个算法只传送两个文件的不同部分，而不是每次都整份传送，因此速度相当快。
查看备份内容
[postgres@hgdb01 20180201_backup]$ pwd
/dbbak/20180201_backup
[postgres@hgdb01 20180201_backup]$ ls
data
[postgres@hgdb01 20180201_backup]$ ls data
backup_label global pg_hba.conf pg_notify pg_stat pg_twophase postmaster.opts tablespace_map.old
backup_label.old pg_clog pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postmaster.pid
base pg_commit_ts pg_logical pg_serial pg_subtrans postgresql.auto.conf recovery.done
base.tar pg_dynshmem pg_multixact pg_snapshots pg_tblspc postgresql.conf tablespace_ma
文件备份完成后执行以下命令结束基础备份
testdb=# select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
—————-
0/60000C0
(1 row)
然后要备份这段时间产生的归档文件（如果归档路径不涉及删除操作，可以不用备份） :
备份开始是的XLOG文件在backup_label中已经指出了00000006000000000000002C,
结束时的XLOG文件也可以从pg_stop_backup()的输出中得到
:
testdb=# select pg_xlogfile_name(‘ 0/60000C0’);
pg_xlogfile_name
————————–
000000010000000000000006
(1 row)
本例中需要的文件只有1个就是000000010000000000000006
可以到归档路径下去查看是否有这个日志文件。
二、执行数据变更
事务时间线
commit_xact1; time1; rollback_xact2;
time2; commit_xact3; commit_xact4;
按照之前的描述, 使用recovery_target_time恢复,
如果recovery_target_time=time1, 不管recovery_target_inclusive=true|false, 恢复后应该都只能看到commit_xact1;的数据变更. 因为rollback_xact2回滚了,
不过使用ctid也能看出回滚的影响,
因为rollback_xact2;的xlog是需要处理的.
如果recovery_target_time=time2,
不管recovery_target_inclusive=true|false,
恢复后应该都只能看到commit_xact3;的数据变更.
但是绝对看不到commit_xact4的变更,
即使ctid也不会有影响,
因为commit_xact4;的xlog不会被处理.
commit_xact1; — SESSION A :
testdb=#create table pitr_test(id int,content text);
testdb=# truncate pitr_test ;
TRUNCATE TABLE
testdb=# insert into pitr_test values (1,’test’);
INSERT 0 1
time1; — SESSION A :
testdb=# select now();
now
——————————-
2018-02-01 18:01:04.201105+08
(1 row)
testdb=# begin;
BEGIN
testdb=# insert into pitr_test values (2,’rollback’);
INSERT 0 1
testdb=# insert into pitr_test values (3,’rollback’);
INSERT 0 1
testdb=# rollback;
ROLLBACK
time2; — SESSION A :
testdb=# select now();
now
——————————-
2018-02-01 18:02:21.319612+08
(1 row)
testdb=# insert into pitr_test values (4,’new’);
INSERT 0 1
testdb=# insert into pitr_test values (5,’new’);
INSERT 0 1
以下执行手动刷脏页数据，日志归档
testdb=# checkpoint;
CHECKPOINT
testdb=# select pg_xlogfile_name(pg_switch_xlog());
pg_xlogfile_name
————————–
000000010000000000000007
(1 row)
testdb=# checkpoint;
CHECKPOINT
testdb=# select pg_xlogfile_name(pg_switch_xlog());
pg_xlogfile_name
————————–
000000010000000000000008
(1 row)
此处可以去归档日志路径查看是否包含以上日志内容。
三、删除集群数据库
关闭数据库
[postgres@hgdb01 ~]$ pg_ctl stop
waiting for server to shut down…. done
server stopped
删除数据库目录以及表空间目录(为简化例子,
本例无新建的表空间.)
[postgres@hgdb01 ~]$ cd $PGDATA
[postgres@hgdb01 data]$ rm -rf *
四、使用基础备份+归档日志进行指定时间点的还原
还原备份以及表空间软链接
cp -r /dbbak/20180201_backup/* $PGDATA
创建pg_log目录,
存放日志 :
[postgres@hgdb01 ~]$ cd $PGDATA
[postgres@hgdb01 ~]$ mkdir -p pg_log pg_xlog
配置$PGDATA/recovery.conf
[postgres@hgdb01 ~]$ cd $PGDATA
[postgres@hgdb01 ~]$ cp $PGHOME/share/recovery.conf.sample ./recovery.conf
vi recovery.conf
============================================================
restore_command = ‘cp /ssd/pg957/arch/20180201/%f %p’
#recovery_target_time = ‘2018-02-01 18:01:04.201105+08‘
recovery_target_time = ‘2018-02-01 18:01:05‘
recovery_target_inclusive = false
recovery_target_timeline = ‘latest’
启动数据库
[postgres@hgdb01 ~]$ pg_ctl start
server starting
[postgres@hgdb01 ~]$ LOG: 00000: loaded library “pg_stat_statements”
L
LOCATION: load_libraries, miscinit.c:1296
查看还原点是否与预计匹配 :
[postgres@hgdb01 data]$ psql
psql (9.5.7)
Type “help” for help.
testdb=#
testdb=#
testdb=# select ctid,* from pitr_test ;
ctid | id | content
——-+—-+———
(0,1) | 1 | test
(1 row)
testdb=# insert into pitr_test values(10,’new’);
INSERT 0 1
testdb=#
testdb=#
testdb=# select ctid,* from pitr_test ;
ctid | id | content
——-+—-+———
(0,1) | 1 | test
(0,4) | 10 | new
(2 rows)
新插入的数据ctid=4,
说明rollback_xact2;的xlog信息被恢复了,
但是回滚了.
如果没有执行info=new的ctid应该=2。
本文主要参考德哥的PostgreSQL 最佳实践 – 在线增量备份与任意时间点恢复 做实验验证，原文链接：https://yq.aliyun.com/articles/59359
By 波罗
转载自：https://blog.csdn.net/pg_hgdb/article/details/79258452
